Bug #45684 GRANT ALL and REVOKE ALL doesn't work with GRANT OPTION
Submitted: 23 Jun 2009 15:58 Modified: 14 Jul 2009 15:58
Reporter: Jakub Vrana (Candidate Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.1.34 OS:Any
Assigned to: Paul Dubois CPU Architecture:Any
Triage: Needs Triage: D4 (Minor)

[23 Jun 2009 15:58] Jakub Vrana
Description:
GRANT ALL and REVOKE ALL doesn't allow including any other privileges after comma. But ALL doesn't include GRANT OPTION so it may be necessary.

For GRANT, there is a workaround in using 'WITH GRANT OPTION' at the end of the command. For REVOKE, the only workaround is issuing two commands.

How to repeat:
GRANT ALL, GRANT OPTION ON *.* TO x@localhost;
-- syntax error near ' GRANT OPTION ON *.* TO x@localhost'

REVOKE ALL, GRANT OPTION ON *.* FROM x@localhost;
-- syntax error near 'ON *.* FROM x@localhost'
[23 Jun 2009 19:38] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read at http://dev.mysql.com/doc/refman/5.1/en/revoke.html about correct syntax for REVOKE.
[24 Jun 2009 7:42] Jakub Vrana
According to the documentation, REVOKE allows syntax "REVOKE priv_type [, priv_type] ON priv_level FROM user". Possible values of priv_type are listed on GRANT page and include ALL and GRANT OPTION. Outcome is that "REVOKE ALL, GRANT OPTION ON db.* FROM user" should be perfectly valid.

By the same logic, "GRANT ALL, GRANT OPTION ON *.* TO x@localhost" should be valid too.
[24 Jun 2009 7:52] Sveta Smirnova
Thank you for the feedback.

Please see quote from http://dev.mysql.com/doc/refman/5.1/en/revoke.html below:

12.5.1.5. REVOKE Syntax

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

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

So if I understood your request correctly REVOKE ALL PRIVILEGES, GRANT OPTION is valid and does what you want REVOKE ALL, GRANT OPTION to do. Please correct me if I am wrong.
[24 Jun 2009 7:59] Jakub Vrana
According to documentation, both syntaxes should be valid.

If you want to revoke all privileges and the grant option from a single object, you need to use the first syntax:

REVOKE ALL, GRANT OPTION ON db.* FROM user
[24 Jun 2009 9:00] Sveta Smirnova
Thank you for the feedback.

But there is no sense to remove ALL, GRANT OPTION ON db1.*, because GRANT OPTION is for any privilege you have. GRANT OPTION is just separate privilege: "GRANT OPTION 	Enable privileges to be granted to or removed from other accounts" And you can revoke GRANT OPTION separately. Another quote from http://dev.mysql.com/doc/refman/5.1/en/grant.html: "Be aware that when you grant a user the GRANT OPTION privilege at a particular privilege level, any privileges the user possesses (or may be given in the future) at that level can also be granted by that user to other users. Suppose that you grant a user the INSERT privilege on a database. If you then grant the SELECT privilege on the database and specify WITH GRANT OPTION, that user can give to other users not only the SELECT privilege, but also INSERT. If you then grant the UPDATE privilege to the user on the database, the user can grant INSERT, SELECT, and UPDATE. "
[24 Jun 2009 9:09] Jakub Vrana
This is a bug for two reasons:

1. Documentation describes a syntax which is not allowed by MySQL.

2. Revoking all privileges and grant option from a specific object is a perfectly valid request. If I want a user to lost all connection with some object then I need to revoke both all privileges and grant option.

There is a workaround but it is not the fix.
[24 Jun 2009 9:25] Sveta Smirnova
Thank you for the feedback.

With 1. I agree documentation can be improved.

According to 2 please read http://dev.mysql.com/doc/refman/5.1/en/grant.html again: "To use GRANT, you must have the GRANT OPTION  privilege, and you must have the privileges that you are granting." and big quote from my last comment.
[24 Jun 2009 9:34] Jakub Vrana
Your quote has nothing to do with this bug.

I want to revoke all privileges and a grant option from some user at once. There is no reason why this should be prohibited.

Similarly, I want to grant all privileges and a grant option to some user at once. GRANT SELECT, GRANT OPTION is allowed and there is no reason why GRANT ALL, GRANT OPTION should be prohibited.
[26 Jun 2009 0:11] Omer Barnir
The REVOKE for ALL and GRANT has an alternate syntax. 
It not having a 'symmetry' with GRANT is not a bug.
[26 Jun 2009 4:09] Jakub Vrana
It's not about an alternate syntax or a symmetry. Both GRANT and REVOKE allows list of privileges after the keyword in the form "priv_type, priv_type, ...". The priv_type can be any of privileges listed by the GRANT documentation. This includes ALL and GRANT OPTION but they don't work together.

I really don't get why you refuse to fix this simple and clear bug.
[29 Jun 2009 9:58] Sveta Smirnova
Thank you for the feedback.

Both GRANT and REVOKE sections in the user manual separate GRANT OPTION from other privileges, so I don't see any inconsistency and only can agree user manual can be improved, but not GRANT/REVOKE syntax. Or this can be verified as feature request only.
[30 Jun 2009 15:37] Jakub Vrana
Table summarizing allowable priv_type on http://dev.mysql.com/doc/refman/5.1/en/grant.html lists GRANT OPTION beside other privileges.

And it really works together with other privileges. Following commands are valid:

GRANT INSERT, GRANT OPTION, SELECT, UPDATE ON *.* TO 'x'@'localhost';
REVOKE INSERT, GRANT OPTION, SELECT, UPDATE ON *.* FROM 'x'@'localhost';

The only problem is with "GRANT ALL, GRANT OPTION" and "REVOKE ALL, GRANT OPTION".
[1 Jul 2009 6:59] Sveta Smirnova
Thank you for the feedback.

Verified as reasonable feature request.
[1 Jul 2009 7:38] Jakub Vrana
I am sorry but this is not a feature request. It is a documented feature which doesn't work.
[13 Jul 2009 17:43] Sergei Golubchik
Right, according to the manual it should work.

The manual is wrong, though, it was always supposed to behave like that, since the feature was implemented in 2000. One was allowed to specify either the list of privileges or ALL. The manual should be corrected.

I agree that this means some functionality is not available - one can grant all with grant option on a database with one command, but cannot revoke it with one command. unfortunately, my agreement doesn't make it a bug, it's still a missing functionality, and a feature request to add it.
[14 Jul 2009 15:58] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added to GRANT section:

In GRANT statements, the ALL [PRIVILEGES] privilege is named by
itself and cannot be specified along with other privileges. It stands
for all privileges available for the level at which privileges are to
be granted except for the GRANT OPTION privilege.

I did not change the REVOKE section because the special ALL PRIVILEGES, GRANT OPTION syntax is already noted.