Take the 2-minute tour ×
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.

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> select version();
+------------+
| version()  |
+------------+
| 5.6.12-log |
+------------+
1 row in set (0.00 sec)

mysql> 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> select now();
+---------------------+
| now()               |
+---------------------+
| 2013-06-14 10:10:13 |
+---------------------+
1 row in set (0.00 sec)

mysql>

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.

share|improve this question
3  
+1 for bringing this issue to light. I never noticed it before, but I always run mysql_secure_installation on a fresh install, which removes the anonymous users. –  Derek Downey Feb 17 '12 at 20:58
    
Excuse me everyone in the DBA.SE, I just accepted my own answer. It's a lot like kissing your sister. Yuck !!! –  RolandoMySQLDBA Feb 24 '12 at 20:27
add comment

1 Answer

up vote 16 down vote accepted

Please note what MySQL 5.0 Certification Study Guide

enter image description here

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:\>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> CREATE TABLE rolando_tb (a bigint unsigned);
Query OK, 0 rows affected (0.06 sec)

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

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

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

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

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

mysql> 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> SELECT database();
+--------------+
| database()   |
+--------------+
| test_mysqldb |
+--------------+
1 row in set (0.00 sec)

mysql>

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> 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> delete from mysql.db where LEFT(db,4)='test';
Query OK, 2 rows affected (0.04 sec)

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

mysql>

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.

share|improve this answer
    
Isn't drop database test; preferred over directly fidgeting with the mysqldb? Deleting the entry from the db table won't remove the actual test db directory. If nothing else that just seems like better house keeping –  atxdba Feb 18 '12 at 14:08
    
@atxdba : I addressed your comment in my answer. –  RolandoMySQLDBA Feb 18 '12 at 21:46
    
I had to do a DELETE from mysql.db WHERE Db LIKE 'test%'; Note that the capitalization of the field name matters. So if your field name is Db and not db, the query above won't work. –  Avery Sep 13 '13 at 7:07
add comment

Your Answer

 
discard

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.