MySQL : Why are there "test" entries in mysql.db? Recently, I posted an answer to a question about mysql.db.

Then, I got to thinking I should ask everyone this question:

I have noticed for years that upon installation of MySQL 5.0+, mysql.db is populated with two entries that allow test databases to be accessed by anonymous users.

You can see it by running this query:

mysql&gt; select * from mysql.db where SUBSTR(db,1,4) = 'test'\G
*************************** 1. row ***************************
                 Host: %
                   Db: test
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
*************************** 2. row ***************************
                 Host: %
                   Db: test\_%
                 User:
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: N
         Execute_priv: N
2 rows in set (0.00 sec)


Are these entries in the mysql.db a security risk, and if so, why are they added by default to a new install?

UPDATE 2013-06-14 10:13 EDT

This morning someone downvoted my question, which I truly don't understand. In light of this event, here is why I took the time to make a rebuttal:

I installed MySQL 5.6.12 for a client this week in their Staging Cluster. I decided to check to see if this was still an on-going problem:

mysql&gt; select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql&gt; select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.10 sec)

mysql&gt; select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-14 10:10:13 |
+---------------------+
1 row in set (0.00 sec)

mysql&gt;


Guess what? This is still a problem even to this day !!!

MORAL OF THE STORY : Please check your mysql.db immediately after installation and remove anonymous logins and erase these test entries from mysql.db without delay.
Please note what MySQL 5.0 Certification Study Guide



say in its bulletpoints on Page 498 Paragraph 6:


  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;


As @DTest mentioned in his comment to the question, you can also run mysql_secure_installation to this for you.

If an anonymous user can login to MySQL remotely, a simply disk attack can be launched to hurt the mysql installation. Here is an example:

USE test
CREATE TABLE rolando_tb (a int);
INSERT INTO rolando_tb VALUES (1);
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;
INSERT INTO rolando_tb SELECT a FROM rolando_tb;


Run insert 30 times and you get a 7GB table


Imagine creating several of these tables in the test database
Imagine creating a Stored Procedure in the test database
The possibilities are endless as long as test and test_% exist in mysql.db


The seriousness of securing the mysql installation has not been fully documented by MySQL AB, and I don't think Oracle is interested in doing so today.

UPDATE 2012-02-18 16:45 EDT

It was suggested by @atxdba's comment that just running 'DROP DATABASE test;' should be the preferred method over touching mysql.db. Dropping the database named test simply removes the database that opens a conduit to a potential security hole.

Please take note of this query:

mysql&gt; select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.09 sec)


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
Test (Test is different from test in Linux-based systems, but it is still problem for MySQL running in Windows)


You will have to remember this subtle rule based on the mysql.db table. If you do not remember this, creating a test database named test or a database name whose first 5 characters is test_ will reopen the same type of security hole.

The most secure way around having to remember these things is to run these lines after an initial installation:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;


then any database with any name can have a proper authentication setup. You can still run these two lines anytime.

UPDATE 2012-02-24 15:20 EDT

To openly demonstrate the danger of having anonymous users in mysql.db,
I would like to create a user that has only the usage privilege.

I will be using MySQL 5.5.12 on my Desktop

First, look at the mysql.db

mysql&gt; select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.05 sec)


mysql&gt;


According to this, any anonymous Joe can reach these databases.

I'll create a database test_mysqldb

mysql&gt; create database test_mysqldb;
Query OK, 1 row affected (0.00 sec)

mysql&gt; use test_mysqldb
Database changed
mysql&gt; show tables;
Empty set (0.00 sec)

mysql&gt;


Let's create an plain vanilla user called vanilla@localhost (no password)

mysql&gt; CREATE USER vanilla@localhost;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; SHOW GRANTS FOR vanilla@localhost;
+---------------------------------------------+
| Grants for vanilla@localhost                |
+---------------------------------------------+
| GRANT USAGE ON *.* TO 'vanilla'@'localhost' |
+---------------------------------------------+
1 row in set (0.00 sec)

mysql&gt;


Next, from the DOS Command Line, let's connect to the mysql schema

C:\&gt;mysql -uvanilla -Dmysql
ERROR 1044 (42000): Access denied for user 'vanilla'@'localhost' to database 'mysql'

C:\&gt;


OK great. That's what I expected.

Next, from the DOS Command Line, let's connect to the test_mysqldb schema, create a table, and load it with numbers

C:\&gt;mysql -uvanilla -Dtest_mysqldb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.5.12-log MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql&gt; CREATE TABLE rolando_tb (a bigint unsigned);
Query OK, 0 rows affected (0.06 sec)

mysql&gt; INSERT INTO rolando_tb VALUES (1);
Query OK, 1 row affected (0.06 sec)

mysql&gt; INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 1 row affected (0.06 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql&gt; INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql&gt; INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql&gt; INSERT INTO rolando_tb SELECT * FROM rolando_tb;
Query OK, 8 rows affected (0.06 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql&gt; SELECT * FROM rolando_tb;
+------+
| a    |
+------+
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
|    1 |
+------+
16 rows in set (0.00 sec)

mysql&gt; SELECT database();
+--------------+
| database()   |
+--------------+
| test_mysqldb |
+--------------+
1 row in set (0.00 sec)

mysql&gt;


Did you see that? A user with USAGE privilege can create a table in a test database and fill it with data. This a clear and present danger. This is why I strongly recommend deleteing those test entries out of mysql.db to deter anonymous users from reaching test databases or accessing newly created test databases (via making a subfolder under the default datadir).

As a reminder, this is how you do it:

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;


UPDATE 2013-09-14 20:05 EDT

To demonstrate that the DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user=''; actually works, I ran this on MySQL 5.6.13 today:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13-log MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql&gt; select db,user,host from mysql.db where LEFT(db,4)='test';
+---------+------+------+
| db      | user | host |
+---------+------+------+
| test    |      | %    |
| test\_% |      | %    |
+---------+------+------+
2 rows in set (0.43 sec)

mysql&gt; delete from mysql.db where LEFT(db,4)='test';
Query OK, 2 rows affected (0.04 sec)

mysql&gt; select db,user,host from mysql.db2 where LEFT(db,4)='test';
Empty set (0.00 sec)

mysql&gt;


Just as a public service announcement, please run

DELETE FROM mysql.db WHERE SUBSTR(db,4) = 'test' AND user='';
FLUSH PRIVILEGES;


or just run mysql-secure-installation and put this potential danger to bed.