Bug #109 Revoke ALL does not remove privileges in a way described in manual.
Submitted: 28 Feb 2003 7:11 Modified: 26 Mar 2003 8:00
Reporter: Alexander Keremidarski Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:all OS:
Assigned to: CPU Architecture:Any

[28 Feb 2003 7:11] Alexander Keremidarski
Description:
REVOKE ALL does not affect DB, Column and Table privileges.

Manual says:
4.3.1 GRANT and REVOKE Syntax
...
If you grant privileges for a database, an entry in the mysql.db table is created if needed. When all privileges for the database have been removed with REVOKE, this entry is deleted. 

Not true !

It can be read in different sources that REVOKE ALL will work with all privileges except for USAGE i.e. REVOKE ALL is expected to leave only row in mysql.user with host@user/password and all privileges set to N.

This is not the case.

How to repeat:
mysql> grant delete on bugs.* to salle@localhost;
Query OK, 0 rows affected (0.01 sec)

mysql> grant insert on bugs.a to salle@localhost;
Query OK, 0 rows affected (0.02 sec)

mysql> grant select (b) on bugs.a to salle@localhost;
Query OK, 0 rows affected (0.03 sec)

mysql> show grants to salle@localhost;
ERROR 1064: You have an error in your SQL syntax near 'to salle@localhost' at line 1
mysql> show grants for salle@localhost;
+-----------------------------------------------------------+
| Grants for salle@localhost                                |
+-----------------------------------------------------------+
| GRANT DELETE ON `bugs`.* TO 'salle'@'localhost'           |
| GRANT SELECT (b), INSERT ON bugs.a TO 'salle'@'localhost' |
+-----------------------------------------------------------+
2 rows in set (0.02 sec)

mysql> revoke ALL on *.* from salle@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for salle@localhost;
+-----------------------------------------------------------+
| Grants for salle@localhost                                |
+-----------------------------------------------------------+
| GRANT DELETE ON `bugs`.* TO 'salle'@'localhost'           |
| GRANT SELECT (b), INSERT ON bugs.a TO 'salle'@'localhost' |
+-----------------------------------------------------------+
[26 Mar 2003 8:00] Sinisa Milivojevic
REVOKE ALL ON *.* will revoke only global privs
REVOKE ALL ON db.* will revoke only database privs
REVOKE ALL ON db.table will revoke only table privs