MySQL Bugs: #45684: GRANT ALL and REVOKE ALL doesn't work with GRANT OPTION

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

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'

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.

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.

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.

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

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. "

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.

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.

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.

The REVOKE for ALL and GRANT has an alternate syntax. 
It not having a 'symmetry' with GRANT is not a bug.

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.

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.

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".

Thank you for the feedback.

Verified as reasonable feature request.

I am sorry but this is not a feature request. It is a documented feature which doesn't work.

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.

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.