( Connection access

Info Catalog ( Connecting ( Privilege system ( Request access
 5.5.5 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 client host from which you connect
    * Your MySQL username
 Identity checking is performed using the three `user' table scope
 columns (`Host', `User', and `Password').  The server accepts the
 connection only if the `Host' and `User' columns in some `user' table
 record match the client hostname and username, and the client supplies
 the password specified in that record.
 `Host' values in the `user' table 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 `Host' column
      values.  These have the same meaning as for pattern-matching
      operations performed with the `LIKE' operator.  For example, a
      `Host' value of `'%'' matches any hostname, whereas a value of
      `''' matches any host in the `' domain.
    * As of MySQL 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@'';
      This allows `david' to connect from any client host having an IP
      number `client_ip' for which the following condition is true:
           client_ip & netmask = host_ip
      That is, for the `GRANT' statement just shown:
           client_ip & =
      IP numbers that satisfy this condition and can connect to the
      MySQL server are those that lie in the range from `' to
    * A blank `Host' value in a `db' table record means that its
      privileges should be combined with those in the row in the `host'
      table that matches the client hostname.  The privileges are
      combined using an AND (intersection) operation, not OR (union).
      You can find more information about the `host' table in 
      Request access.
      A blank `Host' value in the other grant tables is the same as
 Because you can use IP wildcard values in the `Host' column (for
 example, `'144.155.166.%'' to match every host on a subnet), someone
 could try to exploit this capability by naming a host
 `'.  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 `', its name will never
 match the `Host' column of the grant tables.  An IP wildcard value can
 match only IP numbers, not hostnames.
 In the `User' column, wildcard characters are not allowed, but you can
 specify a blank value, which matches any name.  If the `user' table row
 that matches an incoming connection has a blank username, the user is
 considered to be an anonymous user with no name, not a user with the
 name that the client actually specified.  This means that a blank
 username is used for all further access checking for the duration of
 the connection (that is, during Stage 2).
 The `Password' column can be blank.  This is not a wildcard and does
 not mean that any password matches. It means that the user must connect
 without specifying a password.
 Non-blank `Password' values in the `user' table 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 then is used during the connection process when checking
 whether the password is correct. (This is done without the encrypted
 password ever traveling over the connection.)  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 non-administrative
 users read access to the tables in the `mysql' database!
 From version 4.1 on, MySQL employs a stronger authentication method
 that has better password protection during the connection process than
 in earlier versions. It is secure even if TCP/IP packets are sniffed or
 the `mysql' database is captured.  Password encryption is discussed
 further in  Password hashing.
 The following examples show how various combinations of `Host' and
 `User' values in the `user' table apply to incoming connections:
 `Host' *Value*            `User'      *Connections Matched by Entry*
 `'''        `'fred''    `fred', connecting from
 `'''        `'''        Any user, connecting from
 `'%''                     `'fred''    `fred', connecting from any host
 `'%''                     `'''        Any user, connecting from any host
 `'''             `'fred''    `fred', connecting from any host in
                                       the `' domain
 `'x.y.%''                 `'fred''    `fred', connecting from `',
                                       `', `', and so on.
                                       (this is probably not useful)
 `'''       `'fred''    `fred', connecting from the host
                                       with IP address `'
 `'144.155.166.%''         `'fred''    `fred', connecting from any host in
                                       the `144.155.166' class C subnet
 `'''`'fred''    Same as previous example
 It is possible for the client hostname and username of an incoming
 connection to match more than one row in the `user' table.  The
 preceding set of examples demonstrates this: Several of the entries
 shown match a connection from `' by `fred'.
 When multiple matches are possible, the server must determine which of
 them to use.  It resolves this issue as follows:
    * Whenever the server reads the `user' table into memory, it sorts
      the entries.
    * When a client attempts to connect, the server looks through the
      entries in sorted order.
    * The server uses the first row that matches the client hostname and
 To see how this works, suppose that 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.  Literal hostnames and IP numbers
 are the most specific. The pattern `'%'' 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). For the `user' table just shown, the
 result after sorting looks like this:
      | Host      | User     | ...
      | localhost | root     | ...
      | localhost |          | ...
      | %         | jeffrey  | ...
      | %         | root     | ...
 When a client attempts to connect, the server looks through the sorted
 entries and uses the first match found.  For a connection from
 `localhost' by `jeffrey', two of the entries in the table match: the
 one with `Host' and `User' values of `'localhost'' and `''', and the
 one with values of `'%'' and `'jeffrey''.  The `'localhost'' row
 appears first in sorted order, so that is the one the server uses.
 Here is another example.  Suppose that the `user' table looks like this:
      | Host           | User     | ...
      | %              | jeffrey  | ...
      | |          | ...
 The sorted table looks like this:
      | Host           | User     | ...
      | |          | ...
      | %              | jeffrey  | ...
 A connection by `jeffrey' from `' is matched by the first
 row, whereas a connection by `jeffrey' from `' is matched
 by the second.
 It is a common misconception to think that, for a given username, 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
 `' by `jeffrey' is first matched not by the row
 containing `'jeffrey'' as the `User' column value, but by the row with
 no username!  As a result, `jeffrey' will be authenticated as an
 anonymous user, even though he specified a username when connecting.
 If you are able to connect to the server, but your privileges are not
 what you expect, you probably are being authenticated as some other
 account.  To find out what account the server used to authenticate you,
 use the `CURRENT_USER()' function. It returns a value in
 `USER_NAME@HOST_NAME' format that indicates the `User' and `Host'
 values from the matching `user' table record.  Suppose that `jeffrey'
 connects and issues the following query:
      mysql> SELECT CURRENT_USER();
      | CURRENT_USER() |
      | @localhost     |
 The result shown here indicates that the matching `user' table row had
 a blank `User' column value.  In other words, the server is treating
 `jeffrey' as an anonymous user.
 The `CURRENT_USER()' function is available as of MySQL 4.0.6.  
 Information functions.  Another thing you can do to diagnose
 authentication problems is to print out the `user' table and sort it by
 hand to see where the first match is being made.
Info Catalog ( Connecting ( Privilege system ( Request access
automatically generated byinfo2html