Bug #878 REVOKE (or SHOW GRANTS) bug with WITH GRANT OPTION
Submitted: 19 Jul 2003 12:01 Modified: 22 Aug 2003 6:38
Reporter: Alexander Keremidarski Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version: OS:
Assigned to: MySQL Verification Team CPU Architecture:Any

[19 Jul 2003 12:01] Alexander Keremidarski
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      |             |
+-----------+------+-----------+------------+----------------+----------------+------------+-------------+
[24 Jul 2003 8:54] MySQL Verification Team
Fixed
[22 Aug 2003 6:38] Michael Widenius
Just a clarfication:  We fixed the OUTPUT for SHOW GRANTS as REVOKE ALL PRIVILEGES shouldn't remove the GRANT PRIVILEGE.
(The GRANT PRIVILEGE as such is of course quite useless as the user doesn't have anything to grant to someone else)

Regards,
Monty