4.2.9 Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct password. If not, the server denies access to you completely. Otherwise, the server accepts the connection, then enters Stage 2 and waits for requests.

Your identity is based on two pieces of information:

• The host from which you connect
• Your MySQL user name

Identity checking is performed using the three user table scope fields (Host, User, and Password). The server accepts the connection only if a user table entry matches your hostname and user name, and you supply the correct password.

Values in the user table scope fields may be specified as follows:

• A Host value may be a hostname or an IP number, or 'localhost' to indicate the local host.
• You can use the wildcard characters `%' and `_' in the Host field.
• A Host value of '%' matches any hostname.
• A blank Host value means that the privilege should be anded with the entry in the host table that matches the given host name. You can find more information about this in the next chapter.
• As of MySQL Version 3.23, for Host values specified as IP numbers, you can specify a netmask indicating how many address bits to use for the network number. For example:

  mysql> GRANT ALL PRIVILEGES ON db.*
      -> TO david@'192.58.197.0/255.255.255.0';
  

  This will allow everyone to connect from an IP where the following is true:

  user_ip & netmask = host_ip.
  

  In the above example all IP:s in the interval 192.58.197.0 - 192.58.197.255 can connect to the MySQL server.
• Wildcard characters are not allowed in the User field, but you can specify a blank value, which matches any name. If the user table entry that matches an incoming connection has a blank user name, the user is considered to be the anonymous user (the user with no name), rather than the name that the client actually specified. This means that a blank user name is used for all further access checking for the duration of the connection (that is, during Stage 2).
• The Password field can be blank. This does not mean that any password matches, it means the user must connect without specifying a password.

Non-blank Password values represent encrypted passwords. MySQL does not store passwords in plaintext form for anyone to see. Rather, the password supplied by a user who is attempting to connect is encrypted (using the PASSWORD() function). The encrypted password is then used when the client/server is checking if the password is correct. (This is done without the encrypted password ever traveling over the connection.) Note that from MySQL's point of view the encrypted password is the REAL password, so you should not give anyone access to it! In particular, don't give normal users read access to the tables in the mysql database! From version 4.1, MySQL employs a different password and login mechanism that is secure even if TCP/IP packets are sniffed and/or the mysql database is captured.

The examples here show how various combinations of Host and User values in user table entries apply to incoming connections:

Because you can use IP wildcard values in the Host field (for example, '144.155.166.%' to match every host on a subnet), there is the possibility that someone might try to exploit this capability by naming a host 144.155.166.somewhere.com. To foil such attempts, MySQL disallows matching on hostnames that start with digits and a dot. Thus, if you have a host named something like 1.2.foo.com, its name will never match the Host column of the grant tables. Only an IP number can match an IP wildcard value.

An incoming connection may be matched by more than one entry in the user table. For example, a connection from thomas.loc.gov by fred would be matched by several of the entries just shown above. How does the server choose which entry to use if more than one matches? The server resolves this question by sorting the user table after reading it at startup time, then looking through the entries in sorted order when a user attempts to connect. The first matching entry is the one that is used.

user table sorting works as follows. Suppose the user table looks like this:

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-

When the server reads in the table, it orders the entries with the most-specific Host values first ('%' in the Host column means ``any host'' and is least specific). Entries with the same Host value are ordered with the most-specific User values first (a blank User value means ``any user'' and is least specific). The resulting sorted user table looks like this:

+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-

When a connection is attempted, the server looks through the sorted entries and uses the first match found. For a connection from localhost by jeffrey, the entries with 'localhost' in the Host column match first. Of those, the entry with the blank user name matches both the connecting hostname and user name. (The '%'/'jeffrey' entry would have matched, too, but it is not the first match in the table.)

Here is another example. Suppose the user table looks like this:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| thomas.loc.gov |          | ...
+----------------+----------+-

The sorted table looks like this:

+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| thomas.loc.gov |          | ...
| %              | jeffrey  | ...
+----------------+----------+-

A connection from thomas.loc.gov by jeffrey is matched by the first entry, whereas a connection from whitehouse.gov by jeffrey is matched by the second.

A common misconception is to think that for a given user name, all entries that explicitly name that user will be used first when the server attempts to find a match for the connection. This is simply not true. The previous example illustrates this, where a connection from thomas.loc.gov by jeffrey is first matched not by the entry containing 'jeffrey' as the User field value, but by the entry with no user name!

If you have problems connecting to the server, print out the user table and sort it by hand to see where the first match is being made. If connection was successful, but your privileges are not what you expected you may use CURRENT_USER() function (new in version 4.0.6) to see what user/host combination your connection actually matched. See section 6.3.6.2 Miscellaneous Functions.

User Comments

I installed the binary distribution on a new
server running solaris 2.7 (although this could
happen on anything). When I went to set the
initial password for root and root@myhost per the
INSTALL documentation it accepted the root
password but then said it would not allow
connections from the fully qualified domain name
(myhost.myhome.com) of the server it resided on...
Of course I looked at the Host and User columns in
the mysql:user table and didn't get anywhere....
finally I realized it was using the FQDN from the
/etc/hosts file (which when properly formatted
includes IP,FQDN,NAME,ALIAS,ALIAS,ALIAS). Updated
/etc/nsswitch.conf to use dns and voila, it works
fine now). Easy to overlook.

After viewing the previous comment, I solved a
similar problem. Apparently mySQL doesn't like hard
addressses. I coun't login to mySQL from a remote
client no matter what permissions I gave the user.
Once I added an entry like the following:

<blockquote>
192.214.33.156 myRemoteHost
</blockquote>
to the /etc/hosts file, problem was solved.

Under 3.23.55 with syntax as follows:
GRANT ... ON `foo\_bar`.* TO ...

results in:
Unknown command '\_'

However it appears the grant tables are still updated.
Strange

I have Redhat 8 and had a problem connecting using telnet test (refused). Seems it was trying to connect as localhost.localdomain. Swapped the order in /etc/hosts for 127.0.0.1 and it then connected ok. OpenCms now installing ok...

Something this doesn't mention is that you can only use the % between dots.
Example:
%.com works.
www.%.com works.
www%.com does not work.

A common problem is getting an "xxx is not allowed to connect to this MySQL server" message when connecting to a remote MySQL server despite having edited the user table in mysql db.
What solved the problem for me (after 2 hours) was just executing "FLUSH PRIVILEGES;".

I had a very similar problem after migrating my databases to a new MySQL installation on a different machine. All efforts to connect to the server from a networked PC failed until I realized that PHPMyAdmin didn't automatically enter new user passwords using the password() function. Once I re-entered the password through the function, I connected easily with the new user's credentials.

Interestingly enough, I still cannot connect via any other username/password combination - just the new user I created.

Add your own comment.