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.
usercan no longer log in with their password. (
mysql -u user -h 127.0.0.1 -P 3306 -ppassword)
user2can still log in with their password. (
mysql -u user2 -h 127.0.0.1 -P 3306 -ppassword)
totallyrandomtestusercan suddenly log in without a password (
mysql -u totallyrandomtestuser -h 127.0.0.1 -P 3306)
usercan 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.