Friday Jan 17, 2014

The "test" Database and Security

Many installations of MySQL server come with a built-in database called test. It's initially empty, and you might wonder what it's for, or even if you can delete it without any problems.

What is it for? 

The test database is installed by the MySQL Server RPM as part of the mysql_install_db process, and some other package managers run that script too. If you run that script as part of a manual install of MySQL, you'll get the same effect. It creates the database by creating an empty directory called "test" in the data directory, and creates wide-open access to the database test and any database with a name beginning with test_ by inserting a couple of rows into the mysql.db table that give everyone full access to create or use those databases.

The configuration is designed to make it easy for new users to create a playground or sandbox database to work with, one that doesn't require asking the DBA to open up permissions every time a user wants to create a database for testing purposes.

The test security settings 

These are the lines the mysql_install_db script executes (slightly modified):
    INSERT INTO mysql.db VALUES ('%','test','','Y','Y','Y','Y',
       'Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y');
    INSERT INTO mysql.db VALUES ('%','test\_%','','Y','Y','Y','Y',
       'Y','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N','N','Y','Y');

This is quite significant, because if you create a database called test_db or test_banana or test_anything_else_really, all those databases are wide open to any MySQL user on that server, even otherwise low-privilege users. This is particularly important if you run a MySQL server shared across many projects or customers.

Securing the Default "test" Database Configuration 

It's actually a best-practice to remove the test database along with the rows inserted by the lines above—they make the test database (and others beginning with test_) completely accessible to anyone with a MySQL account. When it's created during installation (as it is with the Oracle-built RPM distributions), anyone has full access to it as a sort of sandbox environment, and could in theory use it as a launching point for an attack.

Run the mysql_secure_installation script to perform a number of security optimizations, including removing the lines added by the mysql_install_db process. Another way is simply to delete the two rows:

DELETE FROM mysql.db WHERE Db IN('test', 'test\_%');

About

Jeremy Smyth writes MySQL training courses, and likes exploring interesting questions that come up from novices and experts alike.

Connect

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today