DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(mysql.info) connection-access

Info Catalog (mysql.info) connecting (mysql.info) privilege-system (mysql.info) request-access
 
 5.8.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, and 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
 row match the client hostname and username and the client supplies the
 password specified in that row.
 
 `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 `'%.mysql.com'' matches any host in the `mysql.com'
      domain.
 
    * 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:
 
           GRANT ALL PRIVILEGES ON db.* TO david@'192.58.197.0/255.255.255.0';
 
      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 & 255.255.255.0 = 192.58.197.0
 
      IP numbers that satisfy this condition and can connect to the
      MySQL server are those in the range from `192.58.197.0' to
      `192.58.197.255'.
 
      Note: The netmask can only be used to tell the server to use 8,
      16, 24, or 32 bits of the address. Examples:
 
         * `192.0.0.0/255.0.0.0': anything on the 192 class A network
 
         * `192.168.0.0/255.255.0.0': anything on the 192.168 class B
           network
 
         * `192.168.1.0/255.255.255.0': anything on the 192.168.1 class
           C network
 
         * `192.168.1.1': only this specific IP
 
      The following netmask (28 bits) will not work:
 
           192.168.0.1/255.255.255.240
 
    * A blank `Host' value in a `db' table row 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).  
      request-access, discusses use of the `host' table further.
 
      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
 `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 never matches
 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
 never give anyone access to it. In particular, _do not give
 non-administrative users read access to tables in the `mysql' database_.
 
 MySQL 5.0 employs the stronger authentication method (first implemented
 in MySQL 4.1) 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-hashing, discusses password encryption further.
 
 The following table shows how various combinations of `Host' and `User'
 values in the `user' table apply to incoming connections.
 
 `Host' *Value*            `User'      *Allowable Connections*
                           *Value*     
 `'thomas.loc.gov''        `'fred''    `fred', connecting from
                                       `thomas.loc.gov'
 `'thomas.loc.gov''        `'''        Any user, connecting from
                                       `thomas.loc.gov'
 `'%''                     `'fred''    `fred', connecting from any host
 `'%''                     `'''        Any user, connecting from any host
 `'%.loc.gov''             `'fred''    `fred', connecting from any host in
                                       the `loc.gov' domain
 `'x.y.%''                 `'fred''    `fred', connecting from `x.y.net',
                                       `x.y.com', `x.y.edu', and so on
                                       (this is probably not useful)
 `'144.155.166.177''       `'fred''    `fred', connecting from the host
                                       with IP address `144.155.166.177'
 `'144.155.166.%''         `'fred''    `fred', connecting from any host in
                                       the `144.155.166' class C subnet
 `'144.155.166.0/255.255.255.0''`'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 `thomas.loc.gov' 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 rows.
 
    * When a client attempts to connect, the server looks through the
      rows in sorted order.
 
    * The server uses the first row that matches the client hostname and
      username.
 
 To see how this works, suppose that the `user' table looks like this:
 
      +-----------+----------+-
      | Host      | User     | ...
      +-----------+----------+-
      | %         | root     | ...
      | %         | jeffrey  | ...
      | localhost | root     | ...
      | localhost |          | ...
      +-----------+----------+-
 
 When the server reads the table into memory, it orders the rows 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. Rows 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
 rows and uses the first match found. For a connection from `localhost'
 by `jeffrey', two of the rows from 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  | ...
      | thomas.loc.gov |          | ...
      +----------------+----------+-
 
 The sorted table looks like this:
 
      +----------------+----------+-
      | Host           | User     | ...
      +----------------+----------+-
      | thomas.loc.gov |          | ...
      | %              | jeffrey  | ...
      +----------------+----------+-
 
 A connection by `jeffrey' from `thomas.loc.gov' is matched by the first
 row, whereas a connection by `jeffrey' from `whitehouse.gov' is matched
 by the second.
 
 It is a common misconception to think that, for a given username, all
 rows that explicitly name that user are 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 row
 containing `'jeffrey'' as the `User' column value, but by the row with
 no username. As a result, `jeffrey' is 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. (See  information-functions.)
 It returns a value in `USER_NAME@HOST_NAME' format that indicates the
 `User' and `Host' values from the matching `user' table row. 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.
 
 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 (mysql.info) connecting (mysql.info) privilege-system (mysql.info) request-access
automatically generated byinfo2html