Show grants still keep result after user has been dropped

[xinwu5]

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

mysql> create user test5@'100.%' identified by '';
Query OK, 1 row affected (0.01 sec)

mysql> grant select on test.* to test5@'100.%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user from mysql.user where user ='test5';
+-------+
| user  |
+-------+
| test5 |
+-------+
1 row in set (0.01 sec)

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

**---- Everything is good until now.**

mysql> drop user test5@'100.%';
Query OK, 0 rows affected (0.01 sec)

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

mysql> select user from mysql.user where user ='test5';
Empty set (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.14 sec)

mysql> select user from mysql.user where user ='test5';
Empty set (0.01 sec)

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

2. What did you expect to see?

After the user has been dropped, show grants for test5@'100.%'; should return empty.

3. What did you see instead?

mysql> show grants for test5@'100.%';
+-------------------------------------------+
| Grants for test5@100.%                    |
+-------------------------------------------+
| GRANT Select ON test.* TO 'test5'@'100.%' |
+-------------------------------------------+

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v2.0.4-15-g14552af
Git Commit Hash: 14552afb4228c87f746c884c1548abc3eea4035e
Git Branch: release-2.0
UTC Build Time: 2018-07-04 11:56:13
GoVersion: go version go1.10.2 linux/amd64
TiKV Min Version: 2.0.0-rc.4.1
1 row in set (0.00 sec)

[shenli]

@xinwu5 Thanks for your report!
Could you try flush privileges? Please refer to https://github.com/pingcap/docs/blob/master/sql/privilege.md#time-of-effect

[xinwu5]

In my original post, flush privileges is one of the action I have done.
As the doc says the time period is 5m, the following output is after 20m with the flush privileges.

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

[zz-jason]

@tiancaiamao PTAL

[tiancaiamao]

I believe @zhexuany have handled this before.
See #6624
It's not in the release-2.0 branch, should we cherry pick @shenli

[shenli]

@zhexuany Please cherry-pick it to the release-2.0 branch.

[shenli]

@xinwu5 This bug is fixed in master and has been cherry-picked to the 2.0 branch. You can try the latest master or wait for the next 2.0.x release (about 2 weeks).

[wwar]

Confirming that this bug can be closed / TiDB behavior is identical to MySQL:

CREATE USER u1;
GRANT SELECT ON test.* TO u1;
SELECT * FROM mysql.user WHERE user = 'u1';
SHOW GRANTS FOR u1;
DROP USER u1;
SHOW GRANTS FOR u1;
SELECT * FROM mysql.user WHERE user = 'u1';
FLUSH PRIVILEGES;
SELECT * FROM mysql.user WHERE user = 'u1';
SHOW GRANTS FOR u1;


mysql [localhost:8019] {root} ((none)) > CREATE USER u1;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8019] {root} ((none)) > GRANT SELECT ON test.* TO u1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8019] {root} ((none)) > SELECT * FROM mysql.user WHERE user = 'u1';
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| Host | User | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher             | x509_issuer              | x509_subject               | max_questions | max_updates | max_connections | max_user_connections | plugin                | authentication_string | password_expired | password_last_changed | password_lifetime | account_locked | Create_role_priv | Drop_role_priv | Password_reuse_history | Password_reuse_time | Password_require_current | User_attributes |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
| %    | u1   | N           | N           | N           | N           | N           | N         | N           | N             | N            | N         | N          | N               | N          | N          | N            | N          | N                     | N                | N            | N               | N                | N                | N              | N                   | N                  | N                | N          | N            | N                      |          | 0x                     | 0x                       | 0x                         |             0 |           0 |               0 |                    0 | caching_sha2_password |                       | N                | 2020-04-10 10:16:01   |              NULL | N              | N                | N              |                   NULL |                NULL | NULL                     | NULL            |
+------+------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------------------+--------------------------+----------------------------+---------------+-------------+-----------------+----------------------+-----------------------+-----------------------+------------------+-----------------------+-------------------+----------------+------------------+----------------+------------------------+---------------------+--------------------------+-----------------+
1 row in set (0.00 sec)

mysql [localhost:8019] {root} ((none)) > SHOW GRANTS FOR u1;
+--------------------------------------+
| Grants for u1@%                      |
+--------------------------------------+
| GRANT USAGE ON *.* TO `u1`@`%`       |
| GRANT SELECT ON `test`.* TO `u1`@`%` |
+--------------------------------------+
2 rows in set (0.00 sec)

mysql [localhost:8019] {root} ((none)) > DROP USER u1;
Query OK, 0 rows affected (0.01 sec)

mysql [localhost:8019] {root} ((none)) > SHOW GRANTS FOR u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
mysql [localhost:8019] {root} ((none)) > SELECT * FROM mysql.user WHERE user = 'u1';
Empty set (0.00 sec)

mysql [localhost:8019] {root} ((none)) > FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost:8019] {root} ((none)) > SELECT * FROM mysql.user WHERE user = 'u1';
Empty set (0.00 sec)

mysql [localhost:8019] {root} ((none)) > SHOW GRANTS FOR u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'

..

tidb> CREATE USER u1;
Query OK, 0 rows affected (0.02 sec)

tidb> GRANT SELECT ON test.* TO u1;
Query OK, 0 rows affected (0.01 sec)

tidb> SELECT * FROM mysql.user WHERE user = 'u1';
+------+------+-----------------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+-----------------+------------+--------------+------------+-----------------------+------------------+--------------+------------------+----------------+---------------------+--------------------+------------+------------------+------------+--------------+------------------+----------------+----------------+---------------+-------------+-----------+
| Host | User | authentication_string | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Process_priv | Grant_priv | References_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Index_priv | Create_user_priv | Event_priv | Trigger_priv | Create_role_priv | Drop_role_priv | Account_locked | Shutdown_priv | Reload_priv | FILE_priv |
+------+------+-----------------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+-----------------+------------+--------------+------------+-----------------------+------------------+--------------+------------------+----------------+---------------------+--------------------+------------+------------------+------------+--------------+------------------+----------------+----------------+---------------+-------------+-----------+
| %    | u1   |                       | N           | N           | N           | N           | N           | N         | N            | N          | N               | N          | N            | N          | N                     | N                | N            | N                | N              | N                   | N                  | N          | N                | N          | N            | N                | N              | N              | N             | N           | N         |
+------+------+-----------------------+-------------+-------------+-------------+-------------+-------------+-----------+--------------+------------+-----------------+------------+--------------+------------+-----------------------+------------------+--------------+------------------+----------------+---------------------+--------------------+------------+------------------+------------+--------------+------------------+----------------+----------------+---------------+-------------+-----------+
1 row in set (0.00 sec)

tidb> SHOW GRANTS FOR u1;
+------------------------------------+
| Grants for u1@%                    |
+------------------------------------+
| GRANT USAGE ON *.* TO 'u1'@'%'     |
| GRANT Select ON test.* TO 'u1'@'%' |
+------------------------------------+
2 rows in set (0.00 sec)

tidb> DROP USER u1;
Query OK, 0 rows affected (0.02 sec)

tidb> SHOW GRANTS FOR u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
tidb> SELECT * FROM mysql.user WHERE user = 'u1';
Empty set (0.00 sec)

tidb> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

tidb> SELECT * FROM mysql.user WHERE user = 'u1';
Empty set (0.00 sec)

tidb> SHOW GRANTS FOR u1;
ERROR 1141 (42000): There is no such grant defined for user 'u1' on host '%'
tidb> SELECT tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-183-g57db6cec7
Git Commit Hash: 57db6cec7ffad78e74b6ac6c67a2bfe9a6718d17
Git Branch: master
UTC Build Time: 2020-04-04 08:06:18
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)

[zz-jason]

thank you @wwar, I'm going to close this issue now.