Why Any MySQL User Can Access test* Databases and How to Fix It

By default the mysql.db table contains two rows whose User field is empty, meaning they match any MySQL user. These rows give full privileges (Select, Insert, Update, Delete, Create, Drop, etc.) on the database test and any database whose name starts with test_. As a result, any user can freely operate on those databases even without explicit rights.

Default rows in mysql.db

mysql> select * from mysql.db\G

Row 1:

Host: %

Db: test

User: (empty)

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

Event_priv: Y

Trigger_priv: Y

Row 2 (Db: test_%): same privileges as above.

Verification with a read‑only account

Create a user that only has SELECT rights on a specific database:

mysql> grant select on yujx.t to 'select'@'localhost' identified by 'select';

mysql> flush privileges;

Connect with this user and run the following commands on the test database:

mysql> use test;

mysql> create table t(x int);

mysql> insert into t select 1;

mysql> drop database test;

All commands succeed, showing that the read‑only user can still create tables, insert data, and even drop the test database because of the default mysql.db entries.

Effect on databases prefixed with test_

The same privileges apply to any database whose name begins with test_. Screenshots (below) illustrate successful operations on such databases.

Creating databases with test prefix

Using the same read‑only user, the following statements succeed for databases whose names start with test:

mysql> create database test;

mysql> create database test_a;

mysql> create database test_b;

Attempting to create a database without the test prefix fails: mysql> create database a; Result:

ERROR 1044 (42000): Access denied for user 'select'@'localhost' to database 'a'

Removing the unintended permissions

If you do not want any user to have unrestricted access to test or test_ databases, delete the corresponding rows from mysql.db:

DELETE FROM mysql.db WHERE db LIKE 'test%';

After this cleanup, the read‑only user can no longer operate on test databases, confirming that the default rows were the source of the problem.

Best‑practice recommendations

Never use a database named test or with the test_ prefix to store production data.

Avoid testing user permissions on the test database, as it may give misleading results.

To completely eliminate the issue, delete the test related rows from mysql.db or drop the test database entirely.