opened
xinwu5
username_0
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)
```
Show grants still keep result after user has been dropped
issue
created
shenli
username_1
@username_0 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
comment
created
xinwu5
username_0
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)

```
comment
created
zz-jason
username_2
@username_3 PTAL
comment
created
tiancaiamao
username_3
I believe @zhexuany  have handled this before.   
See https://github.com/pingcap/tidb/pull/6624
It's not in the release-2.0 branch, should we cherry pick @username_1
comment
created
shenli
username_1
@zhexuany Please cherry-pick it to the release-2.0 branch.
comment
created
wwar
username_4
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)


```
comment
closed
zz-jason
username_2
issue
created
zz-jason
username_2
thank you @username_4, I'm going to close this issue now.
comment