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