can't remove "GRANT USAGE"

I was testing some stuff, and added a:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

so now when I do

show grants for cptnotsoawesome@localhost;

I can see that one of them is:

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Now I want to remove it as I think it's a security hazard, so i do the:

REVOKE USAGE ON *.* FROM 'cptnotsoawesome'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

But it still shows that USAGE grant in the grant list.

Grants for cptnotsoawesome@localhost
----------------------------------
GRANT USAGE ON *.* TO 'cptnotsoawesome'@'localhost' IDENTIFIED BY PASSWORD 'somePEW-PEWstring'

Any ideas why? What am I doing wrong?

Under the hood, when you see a user with USAGE only, that the user is written in the mysql.user table with all global privileges turned off.

You originally stated the the user had this:

grant usage on statistics.* to cptnotsoawesome@localhost identified by 'password';

You should see a row in mysql.user with the MD5 password and all globals privs set to N. You should also see a row in mysql.db with

• user='cptnotsoawesome'

• host='localhost'

• db='statistics'

• all DB levels privs set to 'Y'

You should be able to see them with this query

SELECT * FROM mysql.db
WHERE user='cptnotsoawesome'
AND host='localhost'
AND db='statistics'\G

When you ran the REVOKE command, you simply removed the row from mysql.db. This did not touch the row in mysql.user. Thus, you could still login to mysql and only have the privs to run

SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;

If there is a test database called test or a database whose first 5 characters are test_, (look for it using SELECT * FROM mysql.db WHERE db LIKE 'test%';) then the user with just USAGE can have full rights to the test databases. I wrote about this in ServerFault Sep 2011.

If you want to remove the row from mysql.user you could either run

DROP USER cptnotsoawesome@localhost;

or

DELETE FROM mysql.user WHERE
WHERE user='cptnotsoawesome'
AND host='localhost';
FLUSH PRIVILEGES;

StackExchange Database Administrators Q#13083, answer score: 20