What is the mysql.db table used for?

Question

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].%                                      |
+-------------------------------------------------------------------------------------------------------------------------------+ 
| GRANT USAGE ON *.* TO 'user'@'xx.xx.xx.%' IDENTIFIED BY PASSWORD 'xxx' REQUIRE SSL |



 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

Answer 1

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.

Answer 2

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.

Answer 3

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.

• Cannot drop anonymous user from mysql.user

• Is this a normal set of MySQL privileges?

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