How to revoke access to *.* for new user in mysql?

Question

In mysql 5.6 command line client (when logged in as root), i created a user with:

CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

Then granted privileges with:

GRANT ALL PRIVILEGES ON databasename.* TO 'admin'@'localhost';

When checking privileges with:

SHOW GRANTS FOR 'admin'@'localhost';

Privileges for the assigned database above are showing as well as one for:

GRANT USAGE ON *.* TO 'admin'@'localhost' IDENTIFIED BY PASSWORD...

I tried to revoke privilege with:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'admin'@'localhost';

But it does not remove the usage on *.*

How do i revoke access to *.* for new user in mysql?

Answer 1

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

The USAGE privilege specifier stands for "no privileges." It is used at the global level with GRANT to modify account attributes such as resource limits or SSL characteristics without affecting existing account privileges.

from Privileges Provided by MySQL documentation.

So basically if you want to remove the USAGE privilege just use:

DROP USER 'admin'@'localhost';

Answer 2

It is possible to remove USAGE grant from a user in MySQL using following

SHOW GRANTS FOR 'user'@'%';

if you see this output of the command

GRANT USAGE ON `<database>`.* TO 'user'@'%' WITH GRANT OPTION

then to remove it use revoke with flush privileges

REVOKE ALL PRIVILEGES ON `<database>`.* from 'user'@'%';

FLUSH PRIVILEGES;

REVOKE GRANT OPTION ON `<database>`.* from 'user'@'%';

FLUSH PRIVILEGES;

use this command to verify updated user privileges

SHOW GRANTS FOR 'user'@'%';