User information exists after the privileges are revoked in mysql.tables_priv

[mikecaat]

Bug Report

Thanks for watching the issue. I found a MySQL compatibility broken case,
which is the mysql.tables have rows that user don't have any privileges to the table.

1. Minimal reproduce step (Required)

CREATE user test;
GRANT SELECT ON `mysql`.`db` TO test;
REVOKE SELECT ON `mysql`.`db` FROM test;
SELECT * FROM `mysql`.`tables_priv`;

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

mysql.tables_priv doesn't have the row related to test user.

3. What did you see instead (Required)

I assumed that what Table_priv is NULL means the test user don't have any privileges to the table.
But, I think it's better to remove the row.

MySQL [(none)]> SELECT * FROM `mysql`.`tables_priv`;
+------+-------+------+------------+---------+---------------------+------------+-------------+
| Host | DB    | User | Table_name | Grantor | Timestamp           | Table_priv | Column_priv |
+------+-------+------+------------+---------+---------------------+------------+-------------+
| %    | mysql | test | db         |         | 2022-10-12 15:56:22 |            |             |
+------+-------+------+------------+---------+---------------------+------------+-------------+
1 row in set (0.03 sec)

When I test with MySQL 5.7, it removes the line when the user privilege is revoked.

4. What is your TiDB version? (Required)

MySQL [(none)]> SELECT tidb_version();
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tidb_version()                                                                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Release Version: v6.1.1
Edition: Community
Git Commit Hash: 5263a0abda61f102122735049fd0dfadc7b7f8b2
Git Branch: heads/refs/tags/v6.1.1
UTC Build Time: 2022-08-25 10:42:41
GoVersion: go1.18.5
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

[CbcWestwolf]

/assign

[wxbty]

hi @CbcWestwolf ! can this task be assigned to me? if you don't have time

[CbcWestwolf]

can this task be assigned to me?

Sure!

[CbcWestwolf]

/assign wxbty

[dveeden]

Looks similar to #38363