Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Related to my MySQL issue

I have a user with USAGE grant in mysql.user, but in mysql.db this user has Select, Insert, Update, Delete. Thus the user can successfully query the database.

I can't find any information about how this mysql.db works, is it like some kind of cached permission in there ? Will a mysqld restart flush it ?

mysql> show grants for user@'xx.xx.xx.%';
| Grants for user@xx.xx.xx.%                                      |

 mysql> select host,db,user, select_priv,update_priv,delete_priv  from
 mysql.db where  user='user'; 
 | host         | db   | user    | select_priv | update_priv |  delete_priv | 
 | xx.xx.xx.1   | myDB | user    | Y           | Y           | Y
share|improve this question
Can you add the SHOW GRANTS FOR user@host to your question? – Derek Downey Feb 17 '12 at 14:59
up vote 5 down vote accepted

From the docs, mysql.db is the table that handles database-specific GRANTS. That is to say, if you explicitly indicated a database in your GRANT command, it would show up in this table:

GRANT SELECT, INSERT ON foo.* TO `bar`@`localhost`;

So the user bar@localhost would have SELECT and INSERT marked as 'Y' in the mysql.db table.

To remove entries from this table, likewise you need to specify the database:

REVOKE SELECT, INSERT ON foo.* FROM `bar`@`localhost`;

Issuing a REVOKE INSERT ON *.* statement (all databases) will not affect this table.

Also, a DROP USER statement will clean up all the entries in the various mysql.* tables that deal with user authentication, such as mysql.db.

share|improve this answer
So, that means that if I grant a user on * .* , mysql.db will explicity show me those grants for each database ? Meaning a grant select, update, delete on * .* has been made in the past but than revoked ? – Bastien974 Feb 17 '12 at 16:13
If you do a GRANT x ON *.*, it should not update the mysql.db table...the table is only updated if you specifically mention the database in the GRANT statement. – Derek Downey Feb 17 '12 at 16:16
So the only way grant on multiple databases are in mysql.db is when it's explicitly granted on each one of them. Then after a revoke on * .* , I would see USAGE in mysql.user BUT still database-specific permission in mysql.db ? – Bastien974 Feb 17 '12 at 16:21
@Bastien974 Yes. You can remove them from mysql.db by issuing multiple REVOKE statements on each database.* – Derek Downey Feb 17 '12 at 16:24
Good and concise (aka quick and dirty) explanation. +1 !!! – RolandoMySQLDBA Feb 17 '12 at 17:50

It's not exactly a cache, but rather the physical store of your system internals. Permissions are one thing that's stored in there. You will also find tables that house information for stored procedures, functions and events.

If you have query logging or profiling enabled and configured in a certain way, you will also find that information in mysql db tables.

If your question is about users being able to query the mysql db tables, avoid . grants. Instead give the appropriate perms to specific db.* or db.tablename.

share|improve this answer

I have a great precaution you must exercise for your mysql.db

Run this query, please:

SELECT COUNT(1) test_db_count FROM mysql.db WHERE SUBSTR(db,4) = 'test';

If you get test_db_count = 2, get rid of them immediately !!!

Here is why : Anonymous users have access to any database whose first 4 letters are test. You can perform lots of CRUD intensive things in a test database. You may also want to rename the test databases to something completely different. Please read these links because I have addressed this issue before in the DBA StackExchange.

To confirm the need to do this, please note what MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

  • Set a password for the root accounts
  • Remove any remotely accessible root accounts.
  • Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
  • Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

To get rid of those bad entries, run this please:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test';
share|improve this answer
Thanks for the advice ;) Great best-practice – Bastien974 Feb 17 '12 at 18:31
@RolandoMySQLDBA, The row in mysql.db writes test\_%. Does this mean that databases must start with test_ before they can be accessed? So databases like test123 are still secured right? – Pacerier Jan 23 '15 at 12:53
@Pacerier To thoroughly answer your question, see my question and my answer – RolandoMySQLDBA Jan 23 '15 at 15:34
@RolandoMySQLDBA, I've read that thread but it doesn't answer it. Since there's only two entries test and test\_%, does that mean that names that start with test but not test_ (e.g. test123) do not qualify for a match? – Pacerier Jan 24 '15 at 23:54
@Pacerier Look at my answer again (…), please. Here is what I said in my answer : Based on this, the following databases can be accessed fully by anonymous users: test test_db test_001 test_1 test_data While the following databases cannot be accessed fully by anonymous users: testdb test1 testdata. – RolandoMySQLDBA Jan 25 '15 at 3:16

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.