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:
|
1 |
CREATE USER foo@abc.com IDENTIFIED BY ‘S0m3PasSw0rD~’, bar@xyz.com IDENTIFIED WITH ‘sha256_password’ by ‘OTh3R‘P@sSW0rD’, baz@mno.com IDENTIFIED BY ‘@uTh3nTiCationStR!nG’ PASSWORD EXPIRE; |
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
mysql> CREATE USER foo@abc.com IDENTIFIED BY 'S0m3PasSw0rD#', bar@xyz.com IDENTIFIED WITH sha256_password BY 'OTh3RP@sSW0rD', baz@mno.com IDENTIFIED BY '@uTh3nTiCationStR!nG' PASSWORD EXPIRE; Query OK, 0 rows affected (0.01 sec) mysql> SELECT USER, HOST FROM mysql.user WHERE USER IN ('foo', 'bar', 'baz'); +------+---------+ | USER | HOST | +------+---------+ | foo | abc.com | | baz | mno.com | | bar | xyz.com | +------+---------+ 3 rows in set (0.00 sec) |
However, if any of the above mentioned user accounts exist, one will notice following behavior.
|
1
2
3
4
5
6
7
8
9
10
11
12
|
mysql> CREATE USER foo@abc.com IDENTIFIED BY 'S0m3PasSw0rD#', bar@xyz.com IDENTIFIED WITH sha256_password BY 'OTh3RP@sSW0rD', baz@mno.com IDENTIFIED BY '@uTh3nTiCationStR!nG' PASSWORD EXPIRE; ERROR 1396 (HY000): Operation CREATE USER failed for 'baz'@'mno.com' mysql> SELECT USER, HOST FROM mysql.user WHERE USER IN ('foo', 'bar', 'baz'); +------+---------+ | USER | HOST | +------+---------+ | foo | abc.com | | baz | mno.com | | bar | xyz.com | +------+---------+ 3 rows in set (0.00 sec) |
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.
|
1
2
3
|
mysql> CREATE USER foo@abc.com IDENTIFIED BY 'S0m3PasSw0rD#', bar@xyz.com IDENTIFIED WITH sha256_password BY 'OTh3RP@sSW0rD', baz@mno.com IDENTIFIED BY '@uTh3nTiCationStR!nG' PASSWORD EXPIRE; ERROR 1396 (HY000): Operation CREATE USER failed for 'baz'@'mno.com' |
In addition, changes done for other users will be rolled back such that no new users are created.
|
1
2
3
4
5
6
7
|
mysql> SELECT USER, HOST FROM mysql.user WHERE USER IN ('foo', 'bar', 'baz'); +------+---------+ | USER | HOST | +------+---------+ | baz | mno.com | +------+---------+ 1 row in set (0.00 sec) |
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:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
mysql> show variables like 'default_authentication_plugin'; +-------------------------------+-----------------------+ | Variable_name | Value | +-------------------------------+-----------------------+ | default_authentication_plugin | mysql_native_password | +-------------------------------+-----------------------+ 1 row in set (0.04 sec) mysql> CREATE USER IF NOT EXISTS baz@mno.com IDENTIFIED BY '@uTh3nTiCationStR!nG'; Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> show warnings; +-------+------+--------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------+ | Note | 3163 | User 'baz'@'mno.com' already exists. | +-------+------+--------------------------------------+ 1 row in set (0.00 sec) |
Binary log will have entry similar to following:
|
1 |
| 0.000001 | 483 | Query | 1 | 682 | CREATE USER IF NOT EXISTS 'baz'@'mno.com' IDENTIFIED WITH 'mysql_native_password' AS '*6100A98C8FA4AFF52FD2A38E83E748BFE1DDE6F8' | |
In addition, server log will contain additional information about such a logging as well:
|
1 |
2016-09-07T11:06:05.198059Z 4 [Warning] Following users were specified in CREATE USER IF NOT EXISTS but they already exist. Corresponding entry in binary log used default authentication plugin 'mysql_native_password' to rewrite authentication information(if any) for them: 'baz'@'mno.com' |
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!