Description:
As per the documentation, when any grant table modified using account management statements they should load grant tables into memory.
" If you modify the grant tables indirectly using an account-management statement, the server notices these changes and loads the grant tables into memory again immediately. Account-management statements are described in Section 15.7.1, “Account Management Statements”. Examples include GRANT, REVOKE, SET PASSWORD, and RENAME USER. "
https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html
However, when we modify authentication_string column (password) in mysql.user table using DML and execute GRANT command the changes are not applied for new connections.
How to repeat:
Repro:
create user named test_user and change password to '' string using ALTER USER operation. Now, change the password to samp using DML command that update mysql.user grant table.
[root@testbox ~]# mysql80 -u root -p****** -S /tmp/mysql_sandbox8039.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> CREATE USER IF NOT EXISTS test_user;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> ALTER USER test_user IDENTIFIED WITH mysql_native_password BY '';
Query OK, 0 rows affected (0.01 sec)
mysql> UPDATE mysql.user SET authentication_string='*EBB6BDA6EF4AFA57BE809B84E08C7A5FEF07F9BE' WHERE user='test_user';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> create database sbtest;
Query OK, 1 row affected (0.01 sec)
mysql> \q
Bye
[root@testbox ~]#
Now, try to connect using test_user with empty string as password and we connect asexpected. When we specify 'samp' as password - connection fails. This is expected because the privileges are not flushed after explicit modificationt to grant tables using DML command.
[root@testbox ~]# mysql80 -u test_user -p -S /tmp/mysql_sandbox8039.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \q
Bye
[root@testbox ~]#
[root@testbox ~]# mysql80 -u test_user -p'samp' -S /tmp/mysql_sandbox8039.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
[root@testbox ~]#
Now, lets execute GRANT coomand to grant some privileges to test_user both at global and database level. We can see changes to privileges reflect immediately.
[root@testbox ~]# mysql80 -u root -p****** -S /tmp/mysql_sandbox8039.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql>
mysql> GRANT all ON sbtest.* TO test_user;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for test_user;
+-------------------------------------------------------+
| Grants for test_user@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `test_user`@`%` |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `test_user`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> GRANT select ON *.* TO test_user;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for test_user;
+-------------------------------------------------------+
| Grants for test_user@% |
+-------------------------------------------------------+
| GRANT SELECT ON *.* TO `test_user`@`%` |
| GRANT ALL PRIVILEGES ON `sbtest`.* TO `test_user`@`%` |
+-------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> \q
Bye
[root@testbox ~]#
When we try to connect using test_user with 'samp' password, we are unable to even after GRANT command execution.
[root@testbox ~]# mysql80 -u test_user -p'samp' -S /tmp/mysql_sandbox8039.sock
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'test_user'@'localhost' (using password: YES)
[root@testbox ~]#
[root@testbox ~]# mysql80 -u test_user -p -S /tmp/mysql_sandbox8039.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.39 MySQL Community Server - GPL
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> \q
Bye
[root@testbox ~]#
[root@testbox ~]#
Suggested fix:
Update the documentation to provide clarificationa round the same.
