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

mysql privileges grant
user1063287 picture user1063287 · Apr 26, 2013 · Viewed 19.3k times · Source

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

Rolando Isidoro picture Rolando Isidoro · Apr 26, 2013

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';

Related questions

Why is a "GRANT USAGE" created the first time I grant a user privileges?

I'm new to the admin side of DBMS and was setting up a new database tonight (using MySQL) when I noticed this. After granting a user a privilege for the first time, another grant is created that looks like GRANT …

Read More
Remove privileges from MySQL database

Before you think this is a duplicate question, I believe I have a unique, even if it is somewhat dim-witted, case. A few days ago, I upgraded the version of MySQL on my Ubuntu 10.04 server to 5.3.3 (it's ahead of the …

Read More
"IDENTIFIED BY 'password'" in MySQL

I often see in many MySQL tutorials that people use command IDENTIFIED BY 'password' both during user creation and granting him privileges. For example: CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON database.* TO 'username'@'localhost' IDENTIFIED …

Read More