MySQL 8.0: Making User Management DDLs Atomic

With MySQL 8.0, we are bringing in an important change in the way user management DDLs are executed.

Background

User management DDLs cover following user management actions:

  • Creating user/role (CREATE USER [IF NOT EXISTS] / CREATE ROLE [IF NOT EXISTS])
  • Altering user account properties (ALTER USER [IF EXISTS])
  • Dropping user/role from database (DROP USER [IF EXISTS] / DROP ROLE [IF EXISTS])
  • Granting/Revoking privileges to/from a user/role (GRANT/REVOKE)

Each of these administrative action can handle multiple entities as a part of single command. E.g. It is possible to create multiple users using:

Above will create 3 user accounts, set their authentication properties using individual passwords and expire their passwords. If none of the above mentioned users are not present, server will gladly create them.

However, if any of the above mentioned user accounts exist, one will notice following behavior.

In MySQL 5.7, the server will throw an error stating that it could not create baz@mno.com, but still goes on and creates foo@abc.com and  bar@xyz.com accounts. This shows that each user in the DDL is handled independently and collective result is displayed to user. Things get even more complicated when such a DDL is logged in binary log. In cases where DDL is executed partially, an incident is written to binary log describing the failure. On the slave node, it is expected that same error will be encountered.

Further, the way user management tables and in-memory caches are updated in case of transaction rollback, there is a small window where soon-to-be-invalidated information is available for authentication/authorization.

Atomicity in User Management DDLs

With MySQL 8.0, we are changing existing behavior such that a multi-user DDL would either execute fully for all users or no changes are applied. Further, we are making sure that in-memory caches are protected for the entire duration – including rollbacks and transient state changes are not visible to other transactions.

Revisiting the previous example involving 3 users, if one of the users in the list is already present, CREATE USER would throw related error as it is doing in MySQL 5.7 and prior.

In addition, changes done for other users will be rolled back such that no new users are created.

Due to this, binary logging becomes simpler because only a completely successful statement makes it way to binary log. So, conditional handling based on error incident is not required for user management DDLs.

Similar will be the behavior of other user management DDLs. Changes to user properties and/or privileges through these DDLs will always be atomic in nature.

Behavior of fault tolerant variants: IF EXISTS/IF NOT EXISTS

With MySQL 5.7, we added support for fault tolerant variants for CREATE/ALTER/DROP USER statements. MySQL 8.0 adds similar support for roles as well. Their behavior would be similar to their non-fault-tolerant counterparts. However, when it comes to binary logging, these variants have different behavior.

  • In case of CREATE USER IF NOT EXISTS, if statement is executed successfully (Remember, warning for existing user does not count towards failure!), all specified users will be present in rewritten query in binary log. If some of the users already exist and if specific authentication plugin is not supplied for them, server will use default authentication plugin for rewriting password.
  • In case of ALTER USER IF EXISTS, if statement is executed successfully, all specified users will be present in rewritten query in binary log. If some of the users do not exist and if specific authentication plugin is not supplied for them, server will use default authentication plugin for rewriting password.

Taking CREATE USER IF NOT EXISTS example:

Binary log will have entry similar to following:

In addition, server log will contain additional information about such a logging as well:

Conclusion

With MySQL 8.0, we are making user management DDLs more robust by introducing atomicity. It also simplifies logging of such DDLs and makes their execution on other nodes smoother.

As always, a big thank you for using MySQL!

2 thoughts on “MySQL 8.0: Making User Management DDLs Atomic

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.