Learning Mysql

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'@'%'

Download 4.24 Mb.

Share with your friends: