0

I am hoping to create a mysql user account which can access everything but one secret DB. I understand how to create a account to access specific db, but deny to access to anything else. But I don't know how to do it the other way around.

  • 3
    http://dba.stackexchange.com/questions/98949/grant-select-on-all-databases-except-one-mysql – Hanky Panky Jun 02 '15 at 08:56
  • Thanks. So there is not an easy way to exclude certain database(s), is it correct? If this is the case, I may have to do it in the application. My application allows users to dynamically create their own db, but I don't want the users to access the metadata db owned by me. –  Jun 02 '15 at 09:00

1 Answers1

5

I don't think there is a straightforward way. But here is an automated way:

Use the mysql commandline tool.

  1. Give the user a login ("USAGE"), something like:

    GRANT USAGE ON *.* TO 'most'@'localhost' IDENTIFIED BY 'pwd';

  2. Generate the rest of the grants:

    SELECT DISTINCT
    CONCAT('GRANT ALL PRIVILEGES ON ', TABLE_SCHEMA, '.* TO most@localhost;')
    FROM information_schema.TABLES
    WHERE TABLE_SCHEMA
    NOT IN('mysql', 'performance_schema', 'information_schema', 'secret');

  3. Copy & paste them to execute them.

(Change 'most', 'localhost', 'pwd', 'secret' as needed.)

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • 1
    You may need to schedule this #2 as a SP and an event to periodically grant access to new DB's. This is a common problem with sharding while preventing access to mysql.*. I found a 1 minute EVENT was sufficient for my needs. – winmutt Jun 09 '15 at 17:21
  • Thanks Rick, I tried it, but the `information_schema` is still visible. –  Jun 10 '15 at 07:00
  • I read it from this link: http://stackoverflow.com/questions/17247590/revoke-privileges-from-user-in-mysql `information_schema` is a read-only pseudo database built on-the-fly on request. Users will always be able to consult this database, but it only presents entries to which they already have access to otherwise. –  Jun 10 '15 at 07:06
  • 1
    Good point about the I_S. P_S might follow the same logic. However, `mysql` should be given no more than `SELECT`, and even that is risky because of what a hacker can get from the list of users, etc. – Rick James Jun 10 '15 at 15:13