Why Does MySQL Return 'Access Denied' Even With Correct Credentials?

MySQL ERROR 1045 (28000): Access Denied for User ‘bill’

When attempting to connect to MySQL using the user 'bill' with the correct password, the error message "ERROR 1045 (28000): Access denied for user 'bill'@'localhost' (using password: YES)" appears. This error is encountered even though the user 'bill' has been created, granted all privileges, and specified with the host '%'.

The Root of the Problem

The issue arises from the possible existence of an anonymous user with a blank username and host set to 'localhost' or '127.0.0.1' in the MySQL user table.

How MySQL Resolves User Authentication

When a client attempts to connect, MySQL checks the user table and selects the first row that matches the client's host name and user name. The selection is sorted with the most specific Host values first, giving literal host names and IP addresses priority.

In this case, the anonymous user with 'localhost' as its host matches the connection attempt better than the user 'bill'@'%', blocking access to 'bill'.

Recommended Solution

The recommended solution is to drop the anonymous user, as it can create security vulnerabilities. To do so, execute the following command:

DROP USER ''@'localhost';

Additional Considerations

1. Connecting Through a Socket: When authenticating through a socket, it is possible to use the user 'bill'@'%'.
2. Impact of Connection Type: The TCP connection or socket connection does not affect the authentication process, but only anonymous users can connect through a socket.
3. Anonymous User Password: Providing the anonymous user's password in the connection string allows access even when specifying a different user name, due to the sorting rules.

The above is the detailed content of Why Does MySQL Return 'Access Denied' Even With Correct Credentials?. For more information, please follow other related articles on the PHP Chinese website!