I've got the users: root, lmanager. And too, a few databases: db1, db2, db3,... I want to grant all privileges to user 'root' over all databases, except for the db2. The user 'lmanager' will get all permissions only to db2.

It is possible to make it work? it is possible to make db2 'invisible' to user root? If it's possible, how i can do it? Thanks in advance!


2 Answers 2


Unfortunately, you must do lots of these:

GRANT ALL ON db1.* TO ...;
GRANT ALL ON db3.* TO ...;  -- skipping db2

The syntax of GRANT/REVOKE is too clumsy to get what you want in any easier way.

You could write a query something like this to generate them:

              '.* TO ...;')
    FROM information_schema.SCHEMATA
    WHERE SCHEMA_NAME NOT IN ('db2', 'mysql');

Then manually execute them.

Precede that by doing a GRANT USAGE TO ... to provide the password.


You should be able to do this by having a row in your mysql.user table permissioning root as having no permissions on db2, this row occurring before the row that grants it default full permissions on everything. (The permission tables are processed in order.) You may need to dump mysql.user to a text file and reconstruct it to achieve this.

  • Database tables don't have any ordering. How would you insert a row in a specific position?
    – Barmar
    Nov 3, 2015 at 17:21
  • Table privileges are in INFORMATION_SCHEMA.TABLE_PRIVILGES, not mysq.user.
    – Barmar
    Nov 3, 2015 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.