<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="connection-access">
      </a>
      8.2.6 Access Control, Stage 1: Connection Verification
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045249868544">
  </a>
  <a class="indexterm" name="idm46045249867504">
  </a>
  <a class="indexterm" name="idm46045249866016">
  </a>
  <p>
   When you attempt to connect to a MySQL server, the server accepts
      or rejects the connection based on these conditions:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Your identity and whether you can verify it by supplying the
          proper credentials.
     </p>
    </li>
    <li class="listitem">
     <p>
      Whether your account is locked or unlocked.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The server checks credentials first, then account locking state. A
      failure at either step causes the server to deny access to you
      completely. Otherwise, the server accepts the connection, and then
      enters Stage 2 and waits for requests.
  </p>
  <p>
   The server performs identity and credentials checking using
      columns in the
   <code class="literal">
    user
   </code>
   table, accepting the
      connection only if these conditions are satisfied:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The client host name and user name match the
      <code class="literal">
       Host
      </code>
      and
      <code class="literal">
       User
      </code>
      columns in
          some
      <code class="literal">
       user
      </code>
      table row. For the rules
          governing permissible
      <code class="literal">
       Host
      </code>
      and
      <code class="literal">
       User
      </code>
      values, see
      <a class="xref" href="account-names.html" title="8.2.4 Specifying Account Names">
       Section 8.2.4, “Specifying Account Names”
      </a>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The client supplies the credentials specified in the row (for
          example, a password), as indicated by the
      <code class="literal">
       authentication_string
      </code>
      column. Credentials
          are interpreted using the authentication plugin named in the
      <code class="literal">
       plugin
      </code>
      column.
     </p>
    </li>
    <li class="listitem">
     <p>
      The row indicates that the account is unlocked. Locking state
          is recorded in the
      <code class="literal">
       account_locked
      </code>
      column,
          which must have a value of
      <code class="literal">
       'N'
      </code>
      . Account
          locking can be set or changed with the
      <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       <code class="literal">
        CREATE USER
       </code>
      </a>
      or
      <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
       <code class="literal">
        ALTER USER
       </code>
      </a>
      statement.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Your identity is based on two pieces of information:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Your MySQL user name.
     </p>
    </li>
    <li class="listitem">
     <p>
      The client host from which you connect.
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045249844528">
  </a>
  <p>
   If the
   <code class="literal">
    User
   </code>
   column value is nonblank, the user
      name in an incoming connection must match exactly. If the
   <code class="literal">
    User
   </code>
   value is blank, it matches any user name.
      If the
   <code class="literal">
    user
   </code>
   table row that matches an incoming
      connection has a blank user name, 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 user name is
      used for all further access checking for the duration of the
      connection (that is, during Stage 2).
  </p>
  <p>
   The
   <code class="literal">
    authentication_string
   </code>
   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. The authentication method implemented by the plugin that
      authenticates the client may or may not use the password in the
   <code class="literal">
    authentication_string
   </code>
   column. In this case, it
      is possible that an external password is also used to authenticate
      to the MySQL server.
  </p>
  <p>
   Nonblank password values stored in the
   <code class="literal">
    authentication_string
   </code>
   column of the
   <code class="literal">
    user
   </code>
   table are encrypted. MySQL does not store
      passwords as cleartext for anyone to see. Rather, the password
      supplied by a user who is attempting to connect is encrypted
      (using the password hashing method implemented by the account
      authentication plugin). 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. See
   <a class="xref" href="user-names.html" title="8.2.1 Account User Names and Passwords">
    Section 8.2.1, “Account User Names and Passwords”
   </a>
   .
  </p>
  <p>
   From the MySQL server's point of view, the encrypted password
      is the
   <span class="emphasis">
    <em>
     real
    </em>
   </span>
   password, so you should never
      give anyone access to it. In particular,
   <span class="emphasis">
    <em>
     do not give
      nonadministrative users read access to tables in the
     <code class="literal">
      mysql
     </code>
     system database
    </em>
   </span>
   .
  </p>
  <p>
   The following table shows how various combinations of
   <code class="literal">
    User
   </code>
   and
   <code class="literal">
    Host
   </code>
   values in the
   <code class="literal">
    user
   </code>
   table apply to incoming connections.
  </p>
  <div class="informaltable">
   <table summary="How various combinations of User and Host values in the user table apply to incoming connections to a MySQL server.">
    <colgroup>
     <col style="width: 15%"/>
     <col style="width: 35%"/>
     <col style="width: 50%"/>
    </colgroup>
    <thead>
     <tr>
      <th scope="col">
       <code class="literal">
        User
       </code>
       Value
      </th>
      <th scope="col">
       <code class="literal">
        Host
       </code>
       Value
      </th>
      <th scope="col">
       Permissible Connections
      </th>
     </tr>
    </thead>
    <tbody>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        'h1.example.net'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from
       <code class="literal">
        h1.example.net
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        ''
       </code>
      </th>
      <td>
       <code class="literal">
        'h1.example.net'
       </code>
      </td>
      <td>
       Any user, connecting from
       <code class="literal">
        h1.example.net
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        '%'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from any host
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        ''
       </code>
      </th>
      <td>
       <code class="literal">
        '%'
       </code>
      </td>
      <td>
       Any user, connecting from any host
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        '%.example.net'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from any host in the
       <code class="literal">
        example.net
       </code>
       domain
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        'x.example.%'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from
       <code class="literal">
        x.example.net
       </code>
       ,
       <code class="literal">
        x.example.com
       </code>
       ,
       <code class="literal">
        x.example.edu
       </code>
       , and so on; this is
            probably not useful
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        '198.51.100.177'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from the host with IP address
       <code class="literal">
        198.51.100.177
       </code>
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        '198.51.100.%'
       </code>
      </td>
      <td>
       <code class="literal">
        fred
       </code>
       , connecting from any host in the
       <code class="literal">
        198.51.100
       </code>
       class C subnet
      </td>
     </tr>
     <tr>
      <th scope="row">
       <code class="literal">
        'fred'
       </code>
      </th>
      <td>
       <code class="literal">
        '198.51.100.0/255.255.255.0'
       </code>
      </td>
      <td>
       Same as previous example
      </td>
     </tr>
    </tbody>
   </table>
  </div>
  <p>
   It is possible for the client host name and user name of an
      incoming connection to match more than one row in the
   <code class="literal">
    user
   </code>
   table. The preceding set of examples
      demonstrates this: Several of the entries shown match a connection
      from
   <code class="literal">
    h1.example.net
   </code>
   by
   <code class="literal">
    fred
   </code>
   .
  </p>
  <p>
   When multiple matches are possible, the server must determine
      which of them to use. It resolves this issue as follows:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Whenever the server reads the
      <code class="literal">
       user
      </code>
      table
          into memory, it sorts the rows.
     </p>
    </li>
    <li class="listitem">
     <p>
      When a client attempts to connect, the server looks through
          the rows in sorted order.
     </p>
    </li>
    <li class="listitem">
     <p>
      The server uses the first row that matches the client host
          name and user name.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The server uses sorting rules that order rows with the
      most-specific
   <code class="literal">
    Host
   </code>
   values first:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Literal IP addresses and host names are the most specific.
     </p>
    </li>
    <li class="listitem">
     <p>
      Accounts with an IP address in the host part have this order
          of specificity:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Accounts that have the host part given as an IP address:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa94323672"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'127.0.0.1'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'198.51.100.44'</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         Accounts that have the host part given as an IP address
              using CIDR notation:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa45900276"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'192.0.2.21/8'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'198.51.100.44/16'</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
       <li class="listitem">
        <p>
         Accounts that have the host part given as an IP address
              with a subnet mask:
        </p>
        <div class="copytoclipboard-wrapper">
         <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93305307"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'192.0.2.0/255.255.255.0'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'<em class="replaceable">user_name</em>'</span>@<span class="token string">'198.51.0.0/255.255.0.0'</span><span class="token punctuation">;</span></code></pre>
        </div>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      The pattern
      <code class="literal">
       '%'
      </code>
      means
      <span class="quote">
       “
       <span class="quote">
        any
          host
       </span>
       ”
      </span>
      and is least specific.
     </p>
    </li>
    <li class="listitem">
     <p>
      The empty string
      <code class="literal">
       ''
      </code>
      also means
      <span class="quote">
       “
       <span class="quote">
        any
          host
       </span>
       ”
      </span>
      but sorts after
      <code class="literal">
       '%'
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   Non-TCP (socket file, named pipe, and shared memory) connections
      are treated as local connections and match a host part of
   <code class="literal">
    localhost
   </code>
   if there are any such accounts, or
      host parts with wildcards that match
   <code class="literal">
    localhost
   </code>
   otherwise (for example,
   <code class="literal">
    local%
   </code>
   ,
   <code class="literal">
    l%
   </code>
   ,
   <code class="literal">
    %
   </code>
   ).
  </p>
  <p>
   The treatment of
   <code class="literal">
    '%'
   </code>
   as equivalent to
   <code class="literal">
    localhost
   </code>
   is deprecated; you should expect this
      behavior to removed from a future version of MySQL.
  </p>
  <p>
   Rows with the same
   <code class="literal">
    Host
   </code>
   value are ordered with
      the most-specific
   <code class="literal">
    User
   </code>
   values first. A blank
   <code class="literal">
    User
   </code>
   value means
   <span class="quote">
    “
    <span class="quote">
     any user
    </span>
    ”
   </span>
   and is
      least specific, so for rows with the same
   <code class="literal">
    Host
   </code>
   value, nonanonymous users sort before anonymous users.
  </p>
  <p>
   For rows with equally-specific
   <code class="literal">
    Host
   </code>
   and
   <code class="literal">
    User
   </code>
   values, the order is nondeterministic.
  </p>
  <p>
   To see how this works, suppose that the
   <code class="literal">
    user
   </code>
   table looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa39612687"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| %         | root     | ...
| %         | jeffrey  | ...
| localhost | root     | ...
| localhost |          | ...
+-----------+----------+-</code></pre>
  </div>
  <p>
   When the server reads the table into memory, it sorts the rows
      using the rules just described. The result after sorting looks
      like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa60137583"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+-----------+----------+-
| Host      | User     | ...
+-----------+----------+-
| localhost | root     | ...
| localhost |          | ...
| %         | jeffrey  | ...
| %         | root     | ...
+-----------+----------+-</code></pre>
  </div>
  <a class="indexterm" name="idm46045249735936">
  </a>
  <a class="indexterm" name="idm46045249734448">
  </a>
  <a class="indexterm" name="idm46045249732960">
  </a>
  <p>
   When a client attempts to connect, the server looks through the
      sorted rows and uses the first match found. For a connection from
   <code class="literal">
    localhost
   </code>
   by
   <code class="literal">
    jeffrey
   </code>
   , two of
      the rows from the table match: the one with
   <code class="literal">
    Host
   </code>
   and
   <code class="literal">
    User
   </code>
   values of
   <code class="literal">
    'localhost'
   </code>
   and
   <code class="literal">
    ''
   </code>
   , and the
      one with values of
   <code class="literal">
    '%'
   </code>
   and
   <code class="literal">
    'jeffrey'
   </code>
   . The
   <code class="literal">
    'localhost'
   </code>
   row appears first in sorted order, so that is the one the server
      uses.
  </p>
  <p>
   Here is another example. Suppose that the
   <code class="literal">
    user
   </code>
   table looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa10167360"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| %              | jeffrey  | ...
| h1.example.net |          | ...
+----------------+----------+-</code></pre>
  </div>
  <p>
   The sorted table looks like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-none"><div class="docs-select-all right" id="sa2957553"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-none">+----------------+----------+-
| Host           | User     | ...
+----------------+----------+-
| h1.example.net |          | ...
| %              | jeffrey  | ...
+----------------+----------+-</code></pre>
  </div>
  <p>
   The first row matches a connection by any user from
   <code class="literal">
    h1.example.net
   </code>
   , whereas the second row matches
      a connection by
   <code class="literal">
    jeffrey
   </code>
   from any host.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    It is a common misconception to think that, for a given user
        name, all rows that explicitly name that user are used first
        when the server attempts to find a match for the connection.
        This is not true. The preceding example illustrates this, where
        a connection from
    <code class="literal">
     h1.example.net
    </code>
    by
    <code class="literal">
     jeffrey
    </code>
    is first matched not by the row
        containing
    <code class="literal">
     'jeffrey'
    </code>
    as the
    <code class="literal">
     User
    </code>
    column value, but by the row with no
        user name. As a result,
    <code class="literal">
     jeffrey
    </code>
    is
        authenticated as an anonymous user, even though he specified a
        user name when connecting.
   </p>
  </div>
  <p>
   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
   <a class="link" href="information-functions.html#function_current-user">
    <code class="literal">
     CURRENT_USER()
    </code>
   </a>
   function. (See
   <a class="xref" href="information-functions.html" title="14.15 Information Functions">
    Section 14.15, “Information Functions”
   </a>
   .) It returns a value in
   <code class="literal">
    <em class="replaceable">
     <code>
      user_name
     </code>
    </em>
    @
    <em class="replaceable">
     <code>
      host_name
     </code>
    </em>
   </code>
   format that indicates the
   <code class="literal">
    User
   </code>
   and
   <code class="literal">
    Host
   </code>
   values from the matching
   <code class="literal">
    user
   </code>
   table row. Suppose that
   <code class="literal">
    jeffrey
   </code>
   connects and issues the following
      query:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28536110"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_USER</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_USER() <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> @localhost     <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
  </div>
  <p>
   The result shown here indicates that the matching
   <code class="literal">
    user
   </code>
   table row had a blank
   <code class="literal">
    User
   </code>
   column value. In other words, the server
      is treating
   <code class="literal">
    jeffrey
   </code>
   as an anonymous user.
  </p>
  <p>
   Another way to diagnose authentication problems is to print out
      the
   <code class="literal">
    user
   </code>
   table and sort it by hand to see where
      the first match is being made.
  </p>
 </div>
 <br/>
</div>
