Is this a normal set of MySQL privileges?

Question

Using phpmyadmin on a local test wamp setup, and well as pretty much the title states, there are 3 users marked as user = ANY, password = NO, such as:

USER | HOST    | Password | Global Priv | Grant
-----+---------+----------+-------------+------
Any  | %       | No       | USAGE       | No
Any  | Local   | No       | USAGE       | No
Any  | Domain  | No       | USAGE       | No
-----+---------+----------+-------------+------

None of the 3 any users have any specific privileges for any tables, so it appears they can't do much. Is this some sort of generic security measure, to explicitly state that 'Any' user that doesn't fall into any other user group has no privileges?

Answer 1

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.

Answer 2

The danger of keeping these anonymous accounts accounts has already been very well explained.

The manual provides a comprehensive check list of steps to take in order to "Secure the initial MySQL Accounts".

In particular, the section "Assigning Anonymous Account Passwords" advises removing these accounts.

Also, the "Securing Test Databases" chapter advises:

By default, the mysql.db table contains rows that permit access by any user to the test database and other databases with names that start with test_. (...) This means that such databases can be used even by accounts that otherwise possess no privileges. If you want to remove any-user access to test databases, do so as follows:

mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

(...) With the preceding change, only users who have global database privileges or privileges granted explicitly for the test database can use it.

In particular, on non-Windows systems, it is recommended to run the mysql_secure_installation script. The Windows installer I believe offers several installation profiles. Some of them will automatically disable insecure settings. If not, the check list will need to be run manually.

Care must be taken with third party installers such as WAMP. The default permissions indicated in the official MySQL Manual may not always apply to these custom (as in "outside of the scope of the manual) installations. Of course, for a production server, WAMP installations and the likes are to be avoided at all cost.