MySQL Bugs: #114761: revoke statements take effect delayed

Bug #114761	revoke statements take effect delayed
Submitted:	24 Apr 9:42	Modified:	24 Apr 13:44
Reporter:	Wenliang Xu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	MySQL8.2.0	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
In a MySQL connection, if the 'use my_db;' command has already been executed, revoking permissions will not take immediate effect.

How to repeat:
1. Start the MySQL client and connect to the database with admin user (SESSION1).
For example:
mysql>  GRANT delete,update,insert  ON my_test.* TO test@"%" ;
Query OK, 0 rows affected (0.00 sec)

2.Start the MySQL client and connect to the database with test user(SESSION2)
For example:
mysql> use my_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

3. Execute revoke COMMAND (SESSION1).
For example:
mysql> revoke delete,update,insert  ON my_test.* from test@"%";
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for test@'%';
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

3. Execute insert COMMAND ,then,execute use my_db  and insert COMMAND again.(SESSION1).
For example:

mysql>
mysql> insert into sbtest2 values(3,'zhangsan','male',3);
Query OK, 1 row affected (0.00 sec)

mysql> use my_test;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'my_test'
mysql>
mysql> insert into sbtest2 values(3,'zhangsan','male',4);
Query OK, 1 row affected (0.03 sec)

Hi Mr. Xu,

Thank you for your bug report.

However, we cannot repeat what you are reporting with 8.3.0, which has replaced 8.2.0.

When the user 'test'@'%' attempts the revoke command, the error is issued, as it should be:

ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'my_test'

that is because user 'test'@'%' does not have grant privileges.

Then we revoked the grants with user that has grant privilege.

But, when we try anything with user 'test'@'%' in the schema my_test, we get the expected error:

use my_test;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'my_test'

This is expected behaviour as per our Reference Manual.

Not a bug.

Sorry,I description wrongly.
1. Start the MySQL client and connect to the database with admin user (SESSION1).
For example:
mysql>  GRANT delete,update,insert  ON my_test.* TO test@"%" ;
Query OK, 0 rows affected (0.00 sec)

2.Start the MySQL client and connect to the database with test user(SESSION2)
For example:
mysql> use my_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

3. Execute revoke COMMAND with admin user(SESSION1).
For example:
mysql> revoke delete,update,insert  ON my_test.* from test@"%";
Query OK, 0 rows affected (0.01 sec)

mysql> show grants for test@'%';
+----------------------------------+
| Grants for test@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `test`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

4. Execute insert COMMAND ,then,execute use my_db  and insert COMMAND again with test user.(SESSION2).
For example:

mysql>
mysql> insert into sbtest2 values(3,'zhangsan','male',3);
Query OK, 1 row affected (0.00 sec)

mysql> use my_test;
ERROR 1044 (42000): Access denied for user 'test'@'%' to database 'my_test'
mysql>
mysql> insert into sbtest2 values(3,'zhangsan','male',4);
Query OK, 1 row affected (0.03 sec)

Hi Mr. Xu,

If you have read our Reference Manual, REVOKE grants from some user  will affect only new logging in of the same user.

Hence, read our Reference Manual.

According to the description in the Reference Manual, the revoke command will take effect immediately, and the link address is:https://dev.mysql.com/doc/refman/8.0/en/flush.html, and The original information is as follows:
Reloading the grant tables is necessary to enable updates to MySQL privileges and users only if you make such changes directly to the grant tables; it is not needed for account management statements such as GRANT or REVOKE, which take effect immediately. See Section 8.2.13, “When Privilege Changes Take Effect”, for more information.

Hi,

What you wrote is quite true, but it is not valid for the connections that were active before REVOKE was issued. It will be immediately active after user quits the connection and then connects again.

Simply, flushing is not required for the change in the privileges. It is just that the active connections cache the privileges that were valid in the moment of the connection establishment.

That is also described in our  Reference Manual .

in fact, even if the connections that were active before REVOKE was issued. It will be immediately active if we don‘t execute "use my_test" command or some others command like "show databases;",not need to  quits the current connection and then connects again.