0

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!

migrated from stackoverflow.com Nov 3 '15 at 20:12

This question came from our site for professional and enthusiast programmers.

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:

SELECT
       CONCAT('GRANT ALL ON ',
              SCHEMA_NAME,
              '.* 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.

-2

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 '15 at 17:21
  • Table privileges are in INFORMATION_SCHEMA.TABLE_PRIVILGES, not mysq.user. – Barmar Nov 3 '15 at 17:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy