Take the 2-minute tour ×
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.

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?

share|improve this question

migrated from stackoverflow.com Sep 10 '11 at 15:44

This question came from our site for professional and enthusiast programmers.

    
This is actually a very good question because mysql comes with default permissions which are not published. +1 !!! –  RolandoMySQLDBA Sep 12 '11 at 14:11
add comment

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer
    
Thanks for the explanation, but will DELETE FROM mysql.db delete anything else a part from those 3 users? I don't want to delete any data/priviliges apart from those 3 –  Chris Sep 12 '11 at 4:47
    
@Chris - I updated my answer 3 hours ago to address your comment. –  RolandoMySQLDBA Sep 12 '11 at 17:13
    
Ok that clears it up. Thanks Rolando –  Chris Sep 13 '11 at 0:18
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.