MySQL Bugs: #20059: REVOKE USAGE statement doesn't appear to do anything

Bug #20059	REVOKE USAGE statement doesn't appear to do anything
Submitted:	25 May 2006 2:20	Modified:	14 Jun 2006 11:23
Reporter:	Erica Moss	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.21-community-nt	OS:	Windows (win32 - XP SP2)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
This "getting started" page:
http://dev.mysql.com/tech-resources/articles/mysql_intro.html#SECTION0006100000

states, "Refer to Table 1-1 for a complete listing of privilege types which can be used within the REVOKE command."

In the cited table is listed the most basic Grant of "USAGE".  You can in fact issue the following statement:

REVOKE USAGE ON *.* FROM user@hostname

However this doesn't appear to do anything at all.  The user can log in, and issue a SHOW GRANT statement which shows the USAGE grant.

How to repeat:
# do as root

GRANT USAGE ON *.* TO foo@localhost IDENTIFIED BY 'foo';
REVOKE USAGE ON *.* FROM foo@localhost;

# do as foo
SHOW GRANTS

#results
#| Grants for foo@localhost
#| GRANT USAGE ON *.* TO 'foo'@'localhost' IDENTIFIED BY PASSWORD... 

Suggested fix:
We probably shouldn't allow as legal, a statement that doesn't have the anticipated effects.  If the effect of GRANT USAGE is to create a user account, then the effect of REVOKE USAGE would be to drop that user account.

If we don't want to do that, then the statement should throw an error, references to the syntax should be removed from the docs, and we should state clearly that the only way to REVOKE USAGE is to either issue a DROP USER statement or manually update the mysql.user table.

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Additional info:

GRANT anything will grant the given privileges (and may create an
account as necessary as a side-effect). The "given privileges" in this
case are "USAGE", vulgo, "NOTHING" (see manual, "USAGE is a synonym
for 'no privileges'"), so the use case is, "give no privileges to user
X".

"REVOKE USAGE" therefore means "REVOKE NOTHING" and behaves as
expected inasmuch it revokes nothing.

This dummy nature of USAGE is further illustrated in the manual
like so: "To specify any of these resource-limit options for an
existing user without affecting existing privileges, use GRANT USAGE
ON *.* ... WITH MAX_...")

GRANT implicitly creating users but REVOKE not dropping them is also
documented behavior.