Description:
If you provide an invalid password hash length to CREATE USER, the user will vanish from memory version of grant tables after you flush privileges / reset the server. Leading to inconsistent outputs when executing commands such as SHOW GRANTS.
How to repeat:
1. Get a hash password:
mysql [localhost] {msandbox} ((none)) > SELECT PASSWORD('Passw0rd!');
+-------------------------------------------+
| PASSWORD('Passw0rd!') |
+-------------------------------------------+
| *03F7361A0E18DA99361B7A82EA575944F53E206B |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)
2. Create a user using only part of password (I removed final 6B):
mysql [localhost] {msandbox} ((none)) > create user marcelo@'%' IDENTIFIED WITH 'mysql_native_password' AS '*03F7361A0E18DA99361B7A82EA575944F53E20';
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} ((none)) > show grants for marcelo;
+-------------------------------------+
| Grants for marcelo@% |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'marcelo'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)
3 . Flush privileges / restart server. Same show grants will fail:
mysql [localhost] {msandbox} ((none)) > flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql [localhost] {msandbox} ((none)) > show grants for marcelo;
ERROR 1141 (42000): There is no such grant defined for user 'marcelo' on host '%'
Suggested fix:
Use same validation on CREATE USER as it's used on FLUSH PRIVILEGES / server start when deciding which user is valid or not to be copied to memory version of grants.
Description: If you provide an invalid password hash length to CREATE USER, the user will vanish from memory version of grant tables after you flush privileges / reset the server. Leading to inconsistent outputs when executing commands such as SHOW GRANTS. How to repeat: 1. Get a hash password: mysql [localhost] {msandbox} ((none)) > SELECT PASSWORD('Passw0rd!'); +-------------------------------------------+ | PASSWORD('Passw0rd!') | +-------------------------------------------+ | *03F7361A0E18DA99361B7A82EA575944F53E206B | +-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) 2. Create a user using only part of password (I removed final 6B): mysql [localhost] {msandbox} ((none)) > create user marcelo@'%' IDENTIFIED WITH 'mysql_native_password' AS '*03F7361A0E18DA99361B7A82EA575944F53E20'; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} ((none)) > show grants for marcelo; +-------------------------------------+ | Grants for marcelo@% | +-------------------------------------+ | GRANT USAGE ON *.* TO 'marcelo'@'%' | +-------------------------------------+ 1 row in set (0.00 sec) 3 . Flush privileges / restart server. Same show grants will fail: mysql [localhost] {msandbox} ((none)) > flush privileges; Query OK, 0 rows affected (0.00 sec) mysql [localhost] {msandbox} ((none)) > show grants for marcelo; ERROR 1141 (42000): There is no such grant defined for user 'marcelo' on host '%' Suggested fix: Use same validation on CREATE USER as it's used on FLUSH PRIVILEGES / server start when deciding which user is valid or not to be copied to memory version of grants.