9

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?

Noel
  • 10,152
  • 30
  • 45
  • 67
user1063287
  • 10,265
  • 25
  • 122
  • 218
  • I never create users from the command line but I'd say the counterpart for `GRANT USAGE ON *.*` would be `REVOKE USAGE ON *.*` :-? You forgot the `ON *.*` bit. – Álvaro González Apr 26 '13 at 08:33

2 Answers2

12

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';
Rolando Isidoro
  • 4,983
  • 2
  • 31
  • 43
  • `mysql> REVOKE ALL PRIVILEGES, GRANT OPTION ON *.* FROM 'admin'@'localhost'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON *.* FROM 'admin'@'localhost'' at line 1` – user1063287 Apr 26 '13 at 09:40
  • Sorry, misread your question at 1st. Look at the updated answer. – Rolando Isidoro Apr 26 '13 at 10:05
4

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'@'%';
J00MZ
  • 675
  • 1
  • 10
  • 27
MohsnSaqb
  • 41
  • 1
  • You should format your code to make it more readable. https://meta.stackexchange.com/questions/22186/how-do-i-format-my-code-blocks is a good resource for proper code formatting. – Dan Jan 30 '20 at 21:01