MySQL Users, Authentication Order, and Anonymous Logins

Last week we ran in to an issue wherein MySQL stopped allowing logins from several of our user accounts. This post documents how that happened, and what you might need to prevent this issue going forward.

Suppose you have a few mysql users.

 
mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user'@'%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;"
mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user2'@'127.0.0.1' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;"
 

Then, through some magic, another user is created:

 
mysql -e "GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ''@'127.0.0.1' IDENTIFIED BY '';"
 

You will begin to notice a couple of things.

  1. user can no longer log in with their password. (mysql -u user -h 127.0.0.1 -P 3306 -ppassword)
  2. user2 can still log in with their password. (mysql -u user2 -h 127.0.0.1 -P 3306 -ppassword)
  3. totallyrandomtestuser can suddenly log in without a password (mysql -u totallyrandomtestuser -h 127.0.0.1 -P 3306)
  4. user can log in without any password (mysql -u user -h 127.0.0.1 -P 3306)

This is because of the order in which MySQL performs access checking and verification. Because there is a record for an empty username that is more specific than the user@%, that record is chosen for authentication methods. You can verify this with select current_user(); once logged in to the server.

Assuming you have a root user, you can change the host value in the mysql.user table for the affected users. In our case (because we're using Amazon RDS), we don't have a root user. This exacerbated our problem immensely.

This is a documented feature because this allows you to have anonymous users. In our case, we definitely didn't want to allow anonymous users, especially when they then prevented our actual users from being able to log in. To my knowledge, there is no way to prevent anonymous users from being created, so diligence will be your prevention.