Bug #69313 REVOKE ALLPRIVILEGES, GRANT OPTION ON db.* FROM user@host is a syntax error
Submitted: 24 May 2013 15:09 Modified: 16 Jun 2013 20:22
Reporter: Mathieu Lemoine Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Parser Severity:S3 (Non-critical)
Version:5.5.30 OS:Linux (Ubuntu 13.04)
Assigned to: CPU Architecture:Any

[24 May 2013 15:09] Mathieu Lemoine
Description:
I can
GRANT ALL PRIVILEGES ON `db`.* TO 'user'@'host' WITH GRANT OPTION ;

I can
revoke all privileges on `db`.* from 'user'@'host' ;

I can
revoke grant option on `db`.* from 'user'@'host' ;

I can
revoke all privileges, grant option from 'user'@'host' ;

But I cannot
revoke all privileges, grant option on `db`.* from 'user'@'host' ;

It gives me the following error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on `db`.* from 'user'@'host'' at line 1

This seems very weird and inconsistent especially since keeping the grant option on a database keeps several privileges on.

How to repeat:
1. Create a db database
2. Create a user@host user

Suggested fix:
Slight modification on the parser to allow

REVOKE ALL PRIVILEGES, GRANT OPTION ON object FROM user@host;
[25 May 2013 20:26] MySQL Verification Team
Thank you for the bug report.

http://dev.mysql.com/doc/refman/5.5/en/revoke.html

"To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ..."

So:

revoke all privileges, grant option on `db`.* from 'user'@'host' ;
                                    ˆˆˆˆˆˆˆˆˆ
It's a syntax error.
[16 Jun 2013 20:22] Mathieu Lemoine
Hello,

Yes, I know it is. And that's my point: it should not be.

It's inconsistent and weird that we can do revoke all privileges, grant option from 'user'@'host' ; but not revoke all privileges, grant option on `db`.* from 'user'@'host' ;

Especially since the restriction doesn't apply to the GRANT statement.