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> 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> sele

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> 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> select user,host,db from mysql.db;
+------+------+---------+
| user | host | db      |
+------+------+---------+
|      | %    | test    |
|      | %    | test\_% |
+------+------+---------+
2 rows in set (0.05 sec)

mysql>

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

I'll create a database test_mysqldb

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

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

mysql>

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

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

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

mysql>

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

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

C:\>

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:\

StackExchange Database Administrators Q#13361, answer score: 30