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?

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 !!!