REVOKE ALL fails on current user

[morgo]

Bug Report

This was discovered by running mysql-test against TiDB. A user should have permission to revoke privileges from themselves as long as they have CREATE USER granted.

This fails in TiDB because ALL is expanded into the all privileges mask + Grant priv.

It also fails in the case that the privilege check is met (u4).

1. Minimal reproduce step (Required)

CREATE USER u1;
GRANT ALL ON *.* TO u1;
CREATE USER u2;
GRANT SELECT ON *.* to u2;
CREATE USER u3;
GRANT SELECT, CREATE USER ON *.* to u3;
CREATE USER u4;
GRANT ALL ON *.* TO u4 WITH GRANT OPTION;

$ mysql -u u1 # should work

SHOW GRANTS;
REVOKE ALL ON *.* FROM CURRENT_USER();

$ mysql -u u2 # should fail

SHOW GRANTS;
REVOKE ALL ON *.* FROM CURRENT_USER();

$ mysql -u u3 # should work

SHOW GRANTS;
REVOKE ALL ON *.* FROM CURRENT_USER();

$ mysql -u u4 # should work

SHOW GRANTS;
REVOKE ALL ON *.* FROM CURRENT_USER();

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

mysql [localhost:8022] {u1} ((none)) > REVOKE ALL ON *.* FROM CURRENT_USER();
Query OK, 0 rows affected (0.00 sec)
..
mysql [localhost:8022] {u2} ((none)) > REVOKE ALL ON *.* FROM u2;
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation
..
mysql [localhost:8022] {u3} ((none)) > REVOKE ALL ON *.* FROM CURRENT_USER();
Query OK, 0 rows affected (0.00 sec)
..
mysql [localhost:8022] {u4} ((none)) > REVOKE ALL ON *.* FROM CURRENT_USER();
Query OK, 0 rows affected (0.00 sec)

3. What did you see instead (Required)

mysql> revoke all on *.* from current_user();
ERROR 8121 (HY000): privilege check fail

mysql> revoke all on *.* from current_user();
ERROR 8121 (HY000): privilege check fail

mysql> revoke all on *.* from current_user();
ERROR 8121 (HY000): privilege check fail

mysql> revoke all on *.* from current_user();
ERROR 1105 (HY000): Unknown user: @

4. What is your TiDB version? (Required)

mysql> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-2561-g9c75cfa4e
Edition: Community
Git Commit Hash: 9c75cfa4e2bbb854ab90562ecec0409f84a989b0
Git Branch: master
UTC Build Time: 2021-04-02 18:50:29
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[tiancaiamao]

I find it's caused by the 'Grant Option' privilege.

According to the MySQL document https://dev.mysql.com/doc/refman/8.0/en/revoke.html, the privilege check rule for 'revoke some privilege':

To use the first REVOKE syntax, you must have the GRANT OPTION privilege, and you must have the privileges that you are revoking.

However, the rule for 'revoke some privilege' and 'revoke all privilege' are different. 'revoke all privileges' check 'CREATE USER' and so on.

To use this REVOKE syntax, you must have the global CREATE USER privilege, or the UPDATE privilege for the mysql system schema.

That is the root cause.

[tiancaiamao]

genius@DESKTOP-IEU1E7O:/mnt/c/Users/genius$ mysql -u u1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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> SHOW GRANTS;
+-----------------------------------------+
| Grants for u1@%                         |
+-----------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'u1'@'%' |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE ALL ON *.* FROM CURRENT_USER();
ERROR 1045 (28000): Access denied for user 'u1'@'%' (using password: NO)
mysql> ^DBye

genius@DESKTOP-IEU1E7O:/mnt/c/Users/genius$ mysql -u u2
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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> SHOW GRANTS;
+---------------------------------+
| Grants for u2@%                 |
+---------------------------------+
| GRANT SELECT ON *.* TO 'u2'@'%' |
+---------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE ALL ON *.* FROM CURRENT_USER();
ERROR 1045 (28000): Access denied for user 'u2'@'%' (using password: NO)
mysql> ^DBye

genius@DESKTOP-IEU1E7O:/mnt/c/Users/genius$ mysql -u u3
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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> SHOW GRANTS;
+----------------------------------------------+
| Grants for u3@%                              |
+----------------------------------------------+
| GRANT SELECT, CREATE USER ON *.* TO 'u3'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE ALL ON *.* FROM CURRENT_USER();
ERROR 1045 (28000): Access denied for user 'u3'@'%' (using password: NO)
mysql> ^DBye

genius@DESKTOP-IEU1E7O:/mnt/c/Users/genius$ mysql -u u4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 5.7.33-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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> SHOW GRANTS;
+-----------------------------------------------------------+
| Grants for u4@%                                           |
+-----------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'u4'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

mysql> REVOKE ALL ON *.* FROM CURRENT_USER();
Query OK, 0 rows affected (0.00 sec)

mysql> select version();
+-------------------------+
| version()               |
+-------------------------+
| 5.7.33-0ubuntu0.18.04.1 |
+-------------------------+
1 row in set (0.00 sec)

I test with MySQL, and the result does not align to your "2. What did you expect to see? (Required)" description @morgo

Do you mean the behavior in MySQL 8.0?

[morgo]

Do you mean the behavior in MySQL 8.0?

Yes, I am comparing to MySQL 8.0. I discovered this issue when looking at MTR tests.

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

The user name and host is empty for 'revoke .. from CURRENT_USER()', so the revoke statement fails.

2. Symptom (optional)

Revoke ... from CURRENT_USER(), with this kind of error message:

ERROR 1105 (HY000): Unknown user: @

3. All Trigger Conditions (optional)

revoke from current_user()

4. Workaround (optional)

N/A

5. Affected versions

[v3.0.1:v3.0.999], [v4.0.1:v4.0.999], [v5.0.0:v5.0.999]

6. Fixed versions

master