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.
Best Answer
Please note what MySQL 5.0 Certification Study Guide
say in its bulletpoints on Page 498 Paragraph 6:
To get rid of those bad entries, run this please:
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:
Run insert 30 times and you get a 7GB table
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:
Based on this, the following databases can be accessed fully by anonymous users:
While the following databases cannot be accessed fully by anonymous users:
Test
is different fromtest
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 namedtest
or a database name whose first 5 characters istest_
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:
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
According to this, any anonymous Joe can reach these databases.
I'll create a database test_mysqldb
Let's create an plain vanilla user called vanilla@localhost (no password)
Next, from the DOS Command Line, let's connect to the mysql schema
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
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 defaultdatadir
).As a reminder, this is how you do it:
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:Just as a public service announcement, please run
or just run mysql-secure-installation and put this potential danger to bed.