Description:
REVOKE does not work properly in case when all privileges are revoked.
GRANT privilege remains in tables_priv while it should be removed too.
(Unless this behaviour is required by standards in which case Bug is in SHOW GRANTS)
How to repeat:
mysql> GRANT SELECT ON test.test TO 'test1_dba'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS for 'test1_dba'@'localhost';
+----------------------------------------------------------------------------+
| Grants for test1_dba@localhost |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1_dba'@'localhost' |
| GRANT SELECT ON `test`.`test` TO 'test1_dba'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> REVOKE all ON `test`.`test` FROM 'test1_dba'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GRANTS for 'test1_dba'@'localhost';
+----------------------------------------------------------------------+
| Grants for test1_dba@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1_dba'@'localhost' |
| GRANT ON `test`.`test` TO 'test1_dba'@'localhost' WITH GRANT OPTION |
+----------------------------------------------------------------------+
mysql> select * from tables_priv;
+-----------+------+-----------+------------+----------------+----------------+------------+-------------+
| Host | Db | User | Table_name | Grantor | Timestamp | Table_priv | Column_priv |
+-----------+------+-----------+------------+----------------+----------------+------------+-------------+
| localhost | test | test1_dba | test | root@localhost | 20030719213243 | Grant | |
+-----------+------+-----------+------------+----------------+----------------+------------+-------------+