I have a mysql user account who has following Global privileges
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, PROCESS, REFERENCES, INDEX, ALTER,
SHOW DATABASES, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW,
CREATE ROUTINE, ALTER ROUTINE, EVENT,
TRIGGER ON *.* TO 'maintenance'@'host' IDENTIFIED BY PASSWORD 'password';
I need to revoke insert, delete or update privileges on the certain tables for this user. So, I tried this:
mysql> REVOKE INSERT, DELETE, UPDATE ON Hotels.AllHotels FROM 'user'@'host';
An I am seeing the following error
ERROR 1147 (42000): There is no such grant defined for user 'user' on host 'host' on table 'AllHotels'
I am performing the above actions as the mysql root user.
Could someone help please?
Best Answer
Use this script to help you create the grant script syntax : Run a root od mysql admin user.
This will create an output that you can run after.
Just to replicate the error :
Now revoke select from a table form inside the BASE_BIB database:
on host '%' on table 'users'
No the right way to do it :
No error now !!
Managing access in mysql can be quite dificult !!
Once you gave him database.* you cannot revoke access for an object that is in that class. MySQL doesn't expand the Hotels.* wildcard to the individual tables The permissions tables store the granted permissions. Therefore, since you didn't actually grant anything on Hotels.AllHotels , there's nothing for MySQL to revoke. In this case you need to do it granular form the start !
Remove all privileges on database, table, column levels, etccc.