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).
Best Answer
You looking for Uniform Resource Identifier (URI) scheme, and the syntax is:
See the MySQL documentation