events.action: opened
events.author: xinwu5
events.masked_author: username_0
events.text: 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)
  ```
events.title: Show grants still keep result after user has been dropped
events.type: issue
events.action: created
events.author: shenli
events.masked_author: username_1
events.text: @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
events.type: comment
events.action: created
events.author: xinwu5
events.masked_author: username_0
events.text: 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)
  
  ```
events.type: comment
events.action: created
events.author: zz-jason
events.masked_author: username_2
events.text: @username_3 PTAL
events.type: comment
events.action: created
events.author: tiancaiamao
events.masked_author: username_3
events.text: 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
events.type: comment
events.action: created
events.author: shenli
events.masked_author: username_1
events.text: @zhexuany Please cherry-pick it to the release-2.0 branch.
events.type: comment
events.action: created
events.author: wwar
events.masked_author: username_4
events.text: 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)
  
  
  ```
events.type: comment
events.action: closed
events.author: zz-jason
events.masked_author: username_2
events.type: issue
events.action: created
events.author: zz-jason
events.masked_author: username_2
events.text: thank you @username_4, I'm going to close this issue now.
events.type: comment