MySQL – Transferring Grants from One DB to Another

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

The fundamental problem you have is this:

You cannot grant another user a privilege which you yourself do not have; the GRANT OPTION privilege enables you to assign only those privileges which you yourself possess.

^{— http://dev.mysql.com/doc/refman/5.6/en/grant.html}

This makes sense, since if it were otherwise, then you could just grant privileges to yourself.

There is one way around this, depending on how much flexibility you have with the calling code -- stored procedures can run with the credentials of the defining user (as opposed to the invoking user). If the calling system could call a procedure instead of using GRANT ..., then you can create a procedure that is executable by the restricted user, granting other privileges as appropriate.