CODEWITHSUNDEEP
mysqlpermissions
A.Jac
A.Jac

Reputation: 1663

Revoke mysql permissions on database

Im trying to remove all permissions for a user on a database.

REVOKE ALL PRIVILEGES ON database.* FROM 'user'@'%';

This just gives an error saying:

ERROR 1141 (42000): There is no such grant defined for user 'user' on host '%'

I am logged in as root user when running the query. And running show grants for user query shows that the user has permissions on all databases

Output from show grants query:

GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'xxxxxx'

Upvotes: 1

Views: 2760

Answers (2)

user7562197
user7562197

Reputation:

You can execute the below queries,

1) REVOKE ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE TABLESPACE, CREATE TEMPORARY TABLES, CREATE USER, CREATE VIEW, DELETE, DROP, EVENT, EXECUTE, FILE, INDEX, INSERT, LOCK TABLES, PROCESS, REFERENCES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SELECT, SHOW DATABASES, SHOW VIEW, SHUTDOWN, SUPER, TRIGGER, UPDATE ON . FROM ‘user'@'%’;

OR

REVOKE ALL PRIVILEGES ON . FROM ‘user'@'%';

2) REVOKE GRANT OPTION ON . FROM ‘user'@'%';

Upvotes: 0

Alex Tartan
Alex Tartan

Reputation: 6826

Revoke statement has to match the grants issued. If grant is issued to *.*, you can only revoke *.* as well.

since SHOW GRANTS for 'user'@'%' shows a line like:

GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD 'xxxxxx'

You need to revoke that!

This should work:

REVOKE ALL PRIVILEGES ON *.* FROM 'user'@'%';

database.* denotes all tables in the "database" database
*.* denotes all tables in all databases

Upvotes: 2

Related Questions