MySQL error: Access denied for user 'a'@'localhost' (using password: YES)

​​​​​​​​​​Here is a quick-and-dirty method for checking out how MySQL performs successful authentication.

Please run this query:

SELECT USER(),CURRENT_USER();

USER() reports how you attempted to authenticate in mysqld

CURRENT_USER() reports how you were allowed to authenticate by mysqld

Sometimes, USER() and CURRENT_USER() are different. That's because mysql authentication follows a specfic protocol.

According to MySQL 5.0 Certification Study Guide

pages 486,487 state the following on mysql's authentication algorithm:

There are two stages of client access control:

In the first stage, a client attempts to connect and the server either accepts or rejects the connection. For the attempt to succeed, some entry in the user table must match the host from which the client connects, the username, and the password.

In the second stage (which occurs only if a client has already connected sucessfully), the server checks every query it receives from the client to see whether the client has sufficient privileges to execute it.

The server matches a client against entries in the grant tables based on the host from which the client connects and the user the client provides. However, it's possible for more than one record to match:

Host values in grant tables may be specified as patterns contains wildcard values. If a grant table contains entries from myhost.example.com, %.example.com, %.com, and %, all of them match a client who connects from myhost.example.com.

Patterns are not allowed for the User values in grant table entries, but a username may be given as an empty string to specify an anonymous user. The empty string matches any username and thus effectively acts as a wildcard.

When the Host and the User values in more than one user table record match a client, the server must decide which one to use. It does this by sorting records with the most specific Host and User column values first, and choosing the matching record that occurs first in the sorted list, Sorting take place as follows:

In the Host Column, literal values such as localhost, 127.0.0.1, and myhost.example.com sort ahead of values such as %.example.com that have pattern characters in them. Pattern values are sorted according to how specific they are. For example, %.example.com is more specific than %.com, which is more specific than %.

In the User column, non-blank usernames sort ahead of blank usernames. That is, non-anonymous users sort ahead of anonymous users.

The server performs this sorting when it starts. It reads the grant tables into memory, sorts them, and uses the in-memory copies for access control.

From this description, you do not need to worry about the order of the mysql.user tables since there is an in-memory copy of the grant tables which is sorted as previously mentioned.

With regard to how you logged in, only mysql -u a worked. Go back and login again and run these commands

SELECT USER(),CURRENT_USER();
SELECT user,host,password FROM mysql.user;

Make sure that

• every user has a password.
• there are no anonymous users (when user is blank)

This is just a guess, but I suspect mysql -u a of connecting via localhost because when the connection protocol is not specified, the default is to connect via the socket file. There may exist an entry in mysql.user that allow anonymous localhost connection.

Run this query:

SELECT user,host,password FROM mysql.user WHERE user='' AND host='localhost';

If you get back a row with no password, that fully explains why mysq -u a works.

UPDATE 2012-01-19 11:12 EDT

Craig Efrein brought up an interesting question: if two identical usernames exist in the mysql.user table, one with a password and one without, does that mean that MySQL denies authentication when not using a password?

This question is an excellent heads up about MySQL user authentication.

Please note that the primary key of mysql.user is host,user. There are no other indexes. This allows multiple occurrences of a username. Each occurrence can have a different password or no password. This allows user 'dbuser' to login locally (dbuser@localhost) using no password and the same user login from another server within a given netblock (dbuser@'10.1.2.20') with a password like 'pass1' and that user to login remotely from anywhere (dbuser@'%') with a remote password like 'pass2'.

Given the authentication algorithm that MySQL uses, there are no restrictions placed on users with the presence or absense of a password.

This is why MySQL 5.0 Certification Study Guide says on Page 498 Paragraph 6 in its bulletpoints brings out how to cleanup the authentication process:

On Unix, MySQL comes with a mysql_secure_installation script that can perform several helpful security-related operations on your installation. The script has the following capabilities:

• Set a password for the root accounts
• Remove any remotely accessible root accounts.
• Remove the anonymous user accounts. This improves security because it prevents the possibility of anyone connecting to the MySQL server as root from a remote host. The results is that anyone who wants to connect as root must first be able to log in on the server host, which provides an additional barrier against attack.
• Remove the test database (If you remove the anonymous accounts, you might also want to remove the test database to which they have access).

The '%' host wildcard doesn't match 'localhost'. By default the mysql client will try to connect via a socket rather than tcp (usually some place like /var/lib/mysql/mysql.sock).

You can either change your grant to 'a'@'localhost', or force the client to operate over the TCP stack like:

mysql -u a -p --protocol=TCP

Did you check to make sure that MySQL is actually listening on 3306? Run a netstat -tlpn and provide the results. If you don't see 3306 then its probably not.

In my.cnf you should verify that --skip-networking is commented out

[mysqld]
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
language        = /usr/share/mysql/English
bind-address    = 65.55.55.2
# skip-networking