Cannot drop anonymous user from mysql.user

Question

I am trying to DROP the anonymous users from my mysql.users database. However, I have been getting odd behavior. When I enter the command:

DROP User ''@'WOPR';

I was getting a generic error message. So, I rebooted my machine, and tried it again. This time I got the response

Query OK, 0 rows affected.

But when I put in

SELECT User, Host, Password FROM mysql.user WHERE User='';

The return is:

+------+------+----------+
| User | Host | Password |
+------+------+----------+
| | WOPR | |
+------+------+----------+

(WOPR is my hostname)

I run the command

DROP User ''@'WOPR';

and get the same result.

I am running a fresh install of MySQL 5.5 on Arch Linux, kernel version 2.6.33.

Does anyone know what can cause this behavior?

• RolandoMySQLDBA: Please get the word out to send questions of this nature to the DBA StackExchange !!!

Answer 1

DELETE FROM mysql.user WHERE user='' AND host='WOPR';
FLUSH PRIVILEGES;

This should do it for you.

Give it a Try !!!

CAVEAT

MySQL has certain users preinstalled into mysql.user. Also, mysql.db comes with two users that have anonymous access and full privileges to test databases.

Just do this

SELECT * FROM mysql.db \G

and you will see that anyone that connects to test or any database starting with test_ can pretty much do everything in the test database. This is bad since a person with full access to any test database can eat up a disk in matter of minutes.

Example:

use test
CREATE TABLE junk (INT a) ENGINE=MyISAM;
INSERT INTO junk VALUES (1);

OK, big deal. It makes a table with 4 bytes.

Now trying running this SQL statement 30 times:

INSERT INTO junk SELECT * FROM junk;

Hey an instant table with 1,073,741,824 rows (4GB+ file) !!! Imaging having full rights to a test database where you can wreak this kind of havoc on a disk.

My advice to you is to run this to clean out test user access:

DELETE FROM mysql.db WHERE db LIKE 'tes%' AND user='';
FLUSH PRIVILEGES;

For further clarification, see my post MySQL : Why are there "test" entries in mysql.db?

Cheers !!!

• SirTasty: Hi Rolando, That did the trick, thanks! I am actually fixing my test database access right now.
• Pacerier: Isn't there a way to use the drop user syntax instead of modifying the underlying mysql.user table directly?
• RolandoMySQLDBA: @Pacerier Please look at the original question. The real problem is that the DROP USER syntax does not work when the user field is blank (empty string). You must do it the way answer specifies.
• Pacerier: @RolandoMySQLDBA, Are there other solutions besides hacking around with the mysql.user table?
• RolandoMySQLDBA: @Pacerier If you read my last link, you will realize that MySQL (now Oracle) places two rows directly into mysql.db upon installation that will allow anonymous users to access a test database. Interestingly, I was told that Percona Server's installation deletes those rows before its installation is complete (dba.stackexchange.com/questions/66584/…). Since you are using MySQL, either run mysql_secure_installation or delete those two rows yourself.