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.

Daniel Schneller · Accepted Answer · 2009-10-29 09:56:18Z

1

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.

edited Oct 29, 2009 at 9:56

answered Oct 28, 2009 at 13:28

Daniel Schneller

14k5 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

physicsmichael Over a year ago

So SHOW GRANTS FOR 'user'@'host' shows grants for A user on A host, but not the privs for a database independent of user or host. Like you said, the tiered controls are mixing me up. I can do user/host permissions, but how do you edit the databases privs? Directly editing the mysql.db table?

Daniel Schneller Over a year ago

I do not think you can do this - MySQL's privileges revolve around a user account. The other way round would mean the tables are queried the other way round, which they probably aren't. I tried using a % for the user, but that does not work either.

Daniel Schneller Over a year ago

amended my answer based on the two previous comments.

agf · Accepted Answer · 2011-08-15 13:11:39Z

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?

edited Aug 15, 2011 at 13:11

agf

178k45 gold badges300 silver badges241 bronze badges

answered Dec 2, 2010 at 19:17

jun

111 bronze badge

1 Comment

Ivan Solntsev Over a year ago

after manualy editing db or user tables you need to call FLUSH PRIVILEGES

Collectives™ on Stack Overflow

MySQL Database Permission Question

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related