MySQL and MySQL Community information
- January 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
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\_%');
Test Database good one for first time user and For experienced user.
A test database is a temporary database that you create when you are developing a new schema or a new version of an existing schema. With the test database you can make sure that the new schema performs as expected, without running the risk of damaging production data in a user database.
The procedure for creating a test database is the same as the procedure for creating a user database.