Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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;
share|improve this answer
    
+1 for such an explanatory answer... –  Abdul Manaf Feb 16 '12 at 6:12

USAGE means that user doesn't have any privileges.

You have to use 'DROP USER'. 

drop user cptnotsoawesome@localhost;

You can't actually revoke USAGE, without dropping the user.USAGE is a global level privilege.

have a look at This Link.

share|improve this answer
    
I would do it, if that was doing anything wrong to the database, like if it is security threat or something like that, otherwise - i may just leave it –  Katafalkas Feb 15 '12 at 12:31

Please look at this question:-

http://stackoverflow.com/questions/2126225/why-is-a-grant-usage-created-the-first-time-i-grant-a-user-privileges

Basically the USAGE privilege acts like a system level privilege which you can't remove. It's used internally by MySQL so you can manipulate the privilege system.

share|improve this answer
    
So it is not some security threat after all ? –  Katafalkas Feb 15 '12 at 11:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.