Databases Reference
In-Depth Information
How does MySQL decide which user to use when you establish a connection? The
answer has two parts: first, MySQL sorts the user entries by host from most to least
specific and, for duplicate hosts, any anonymous user entry appears last. Consider an
example. Suppose you have four user and host combinations:
•
'dave'@'localhost'
•
'dave'@'%'
•
''@'localhost'
•
'hugh'@'192.168.1.%'
From most specific to least, the hosts are
localhost
, then
192.168.1.%
, and finally
%
.
There are two entries for users on the
localhost
, and, since one is anonymous, the one
with a name (
dave
) is more specific. Overall, this leads to the following sort order:
•
'dave'@'localhost'
•
''@'localhost'
•
'hugh'@'192.168.1.%'
•
'dave'@'%'
The second step in establishing a connection is matching your connection request
against the sorted list. The first entry that matches your connection requirements is
used; if none match, you're denied access. Suppose you try to connect from the
local
host
using the username
dave
. The first entry in the list,
'dave'@'localhost'
matches,
and so you're authenticated and given the privileges of that user. Suppose now you try
to connect from the
localhost
using the username
hugh
. Here's a surprise: MySQL
ignores the username you provide, and you're connected as
''@'localhost'
because,
as discussed in the previous section, the anonymous username is a wildcard that
matches all usernames! You might find this annoying, but some argue it's a feature that
can be used to ensure users from particular hosts get at least a minimal set of privileges.
Consider a final example, where you try to connect from the network machine
yazd
(192.168.1.6) as
dave
. The first two entries are for the
localhost
and so don't match.
The third entry has a host specification that matches, but the username
hugh
does not.
The final entry's host specification matches, and so does the username, and therefore
the connection is established with the privileges of the user
'dave'@'%'
.
Checking Privileges
We've explained how to grant privileges and how to understand the scope of those
privileges. This section explains how to identify the privileges that a user has, and how
to revoke those privileges.
If you've been following our examples, you have created more than 10 users so far in
this chapter, and you probably can't remember all of them. It's important to know the
users you have defined and the privileges that these users have, and that you understand
