Bug #116100 changes to few columns in grant tables are not loaded into memory using GRANT

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.

Hello Chelluru,

Thank you for the report and feedback.

regards,
Umesh