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 [email protected].%                                      |

 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

3 Answers 3


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.

  • 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
    Commented Feb 17, 2012 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. Commented Feb 17, 2012 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
    Commented Feb 17, 2012 at 16:21
  • @Bastien974 Yes. You can remove them from mysql.db by issuing multiple REVOKE statements on each database.* Commented Feb 17, 2012 at 16:24

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.


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';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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