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.

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?

share|improve this question

migrated from unix.stackexchange.com Aug 14 '11 at 0:46

This question came from our site for users of Linux, FreeBSD and other Un*x-like operating systems..

    
Please get the word out to send questions of this nature to the DBA StackExchange !!! –  RolandoMySQLDBA Aug 14 '11 at 1:45
add comment

1 Answer

up vote 4 down vote accepted
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 user LIKE 'tes%';
FLUSH PRIVILEGES;

Cheers !!!

share|improve this answer
    
Hi Rolando, That did the trick, thanks! I am actually fixing my test database access right now. –  SirTasty Aug 14 '11 at 7:57
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.