can't grant ALTER privilege after revoking it from root

[lance6716]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

mysql> revoke alter on *.* from 'root'@'%';
Query OK, 0 rows affected (0.02 sec)

mysql> show grants;
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for User                                                                                                                                                                                                                                                                                                                                                      |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,PROCESS,REFERENCES,SHOW DATABASES,SUPER,EXECUTE,INDEX,CREATE USER,CREATE TABLESPACE,TRIGGER,CREATE VIEW,SHOW VIEW,CREATE ROLE,DROP ROLE,CREATE TEMPORARY TABLES,LOCK TABLES,CREATE ROUTINE,ALTER ROUTINE,EVENT,SHUTDOWN,RELOAD,FILE,CONFIG,REPLICATION CLIENT,REPLICATION SLAVE ON *.* TO 'root'@'%' WITH GRANT OPTION |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> grant alter on *.* to 'root'@'%';
ERROR 8121 (HY000): privilege check for 'Alter' fail

2. What did you expect to see? (Required)

mysql

MySQL [(none)]> revoke alter on *.* from 'root'@'%';
Query OK, 0 rows affected (0.003 sec)

MySQL [(none)]> show grants;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                                                                                                                                                                                                                                                                 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE ON *.* TO 'root'@'%' WITH GRANT OPTION |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

MySQL [(none)]> grant alter on *.* to 'root'@'%';
Query OK, 0 rows affected (0.003 sec)

3. What did you see instead (Required)

see step 1

4. What is your TiDB version? (Required)

Release Version: v6.5.0-alpha
Edition: Community
Git Commit Hash: cf49466a1e03a3929f6587b33df8e3d8fe4a4c6a
Git Branch: heads/refs/tags/v6.5.0-alpha
UTC Build Time: 2022-11-23 14:25:44
GoVersion: go1.19.3
Race Enabled: false
TiKV Min Version: 6.2.0-alpha
Check Table Before Drop: false
Store: tikv

[CbcWestwolf]

/assign

[CbcWestwolf]

This incompatibility with MySQL is because grant statement requires the grantor has the corresponding privilege.

Since the alter privilege has been revoked from the grantor, the execution of grant statement fails on TiDB. But it seems that MySQL has a session-level privilege cache for those revoked privileges, so the execution of grant statement succeeds on MySQL. If we exits the session after show grants and execute grant using another session, the execution also fails on MySQL.

We can improve this compatibility by adding a similar session privileges cache.

[CbcWestwolf]

After taking 6.2.13 When Privilege Changes Take Effect as a reference, I think this inconsistency with MySQL can be regarded as a reasonable incompatibility.

• The modifications of privilege in MySQL have different reloading strategies as mentioned in the link.
• The modifications of privilege in TiDB always trigger reloading immediately.

I don't think we have a strong motivation to keep the behavior completely the same as MySQL's. Instead, we can explain this incompatibility in our document. @xhebox What's your opinion?

[CbcWestwolf]

Explained in pingcap/docs-cn#13368