MySQL Database Permission Question

Question

I've become accustom to mysql user permissions along the lines of

GRANT ALL PRIVILEGES ON db_base.phonebook TO db_user @'%' IDENTIFIED BY 'db_passwd';

and other things that affect how a certain user interacts with mysql. I have a database that was created by a program I ran and it has its own permissions. I can't figure out how to edit the permissions of that database. Yes, that is worded poorly, so I show you this:

(root@localhost) [mysql]> SELECT host,db,user,select_priv,insert_priv FROM mysql.db;
+------+--------------------+------+-------------+-------------+
| host | db                 | user | select_priv | insert_priv |
+------+--------------------+------+-------------+-------------+
| %    | test               |      | Y           | Y           |
| %    | test\_%            |      | Y           | Y           |
| %    | lux_watertank_pmts | lux  | Y           | N           |
+------+--------------------+------+-------------+-------------+
3 rows in set (0.00 sec)

The test database I want to limit access to. How do I change the priv's of a database rather than a user? Thanks for help and understanding.

Answer 1

I have run into the same problem (i.e. wanting to restrict access to the "test" database that is open to all by default). The best I can come up with is deleting the following two lines from mysql.db and restarting mysql.

| %    | test               |      | Y           | Y           |
| %    | test\_%            |      | Y           | Y           |

I haven't tried it yet, but I am fairly certain it will work. Is there another way?

Answer 2

MySQL uses a tiered access control model, i. e. you can specify privileges on the global, database, table and even column level.

I do not think you can do exactly what you want - MySQL's privileges revolve around a user account. Setting permissions by database would mean the privileges tables had to e queried the other way round, which they aren't. I tried using a % for the user, but that did not work either.

So I think you are stuck without a solution to your specific problem.

As for the permissions in general:

While you can modify them manually using the GRANT statements I personally find it tedious to do so. Instead I recommend you use the MySQL Administrator Tool from the MySQL GUI Tools.

If you modify the privileges tables directly - which is possible but not necessarily recommended - remember you have to issue a FLUSH PRIVILEGES command make these changes effective.

It will allow you to visually configure permissions. I do not remember off the top of my head whether you can even see the GRANT statements in advance, but you can of course always have a look at these later with a SHOW GRANTS FOR user@'host' statement.

One caveat if you have never used the tool before: You will have to go to the Tools/Options menu and enable "Show Global Privileges", "Show Schema Object Privileges" and "Show hosts in user list" to enable full access to the finer grained permissions configuration - otherwise it will only show you the global privileges.