MySQL 5.5.16 allows anonymous connections

Question

Apparently, I can connect to MySQL (at least from my localhost) without having to provide any username or password. I can even put anything as username, as long as the password is empty. I have access to the information_schema and test databases. I can create tables. If I run SELECT USER() it returns the proper value randomstring@localhost, where the random string is whatever I used for username when I connected.

The mysql.user table contains root@localhost, root@127.0.0.1, @localhost and pma@localhost. I manually set encrypted passwords to all the records (UPDATE user SET password=...) and only root has any privileges, the other two records have "N" for all privileges. That record with the empty username looks suspicious, but I don't think it has anything to do with this and I think it was there from the beginning.

To make matters worse, if I add a user with a password, I can only connect using empty string as password (just like if I put anything as username). If I try to connect using the username I created and password I supplied when creating it (CREATE USER .. IDENTIFIED BY ..) it doesn't work.

Does anyone know what is wrong and how I can fix this?

Note: I am using MySQL 5.5.16 for Windows, provided by the XAMPP package.

Answer 1

You need to simply remove the anonymous user.

In the future, running /usr/bin/mysql_secure_installation (Location in RPM Installation) will prompt you to remove these anonymous users.

By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.

Remove anonymous users? [Y/n] y ... Success!

Answer 2

Remove the user entry with the empty username. It is the cause. Use select user(); when logged in to verify that it's the user that you logged in as.

See here:

Some accounts are for anonymous users. These have an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.