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.

1 Answer
1

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).

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.

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:

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).

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 notdb, the query above won't work.
–
AverySep 13 '13 at 7:07