revoking mysql table level privileges

Question

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?

Answer 1

Use this script to help you create the grant script syntax : Run a root od mysql admin user.

  select 'grant all privileges on ', table_name, 'to 
someuser@somehost;' from information_schema.tables where table_schema = 'database name' and 
table_name not in ('table without access', 'table without access');

This will create an output that you can run after.

Just to replicate the error :

mysql> grant select on BASE_BIB.* to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> use BASE_BIB;
Database changed
mysql> show tables ;

Now revoke select from a table form inside the BASE_BIB database:

mysql> revoke select on BASE_BIB.users from test123@'%';
ERROR 1147 (42000): There is no such grant defined for user 'test123' 

on host '%' on table 'users'

No the right way to do it :

mysql> grant select on BASE_BIB.users to test123@'%' identified by 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke select on BASE_BIB.users from test123@'%';
Query OK, 0 rows affected (0.00 sec)

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.

• Grant privileges to EACH table, except 'you choose'.
• Grant privilege to specified fields in table 'you choose'.