<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="account-names">
      </a>
      8.2.4 Specifying Account Names
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045250009920">
  </a>
  <a class="indexterm" name="idm46045250008880">
  </a>
  <a class="indexterm" name="idm46045250007392">
  </a>
  <a class="indexterm" name="idm46045250005904">
  </a>
  <a class="indexterm" name="idm46045250004416">
  </a>
  <a class="indexterm" name="idm46045250002928">
  </a>
  <a class="indexterm" name="idm46045250001424">
  </a>
  <a class="indexterm" name="idm46045249999920">
  </a>
  <p>
   MySQL account names consist of a user name and a host name, which
      enables creation of distinct accounts for users with the same user
      name who connect from different hosts. This section describes the
      syntax for account names, including special values and wildcard
      rules.
  </p>
  <p>
   In most respects, account names are similar to MySQL role names,
      with some differences described at
   <a class="xref" href="role-names.html" title="8.2.5 Specifying Role Names">
    Section 8.2.5, “Specifying Role Names”
   </a>
   .
  </p>
  <p>
   Account names appear in SQL statements such as
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   ,
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   , and
   <a class="link" href="set-password.html" title="15.7.1.10 SET PASSWORD Statement">
    <code class="literal">
     SET
      PASSWORD
    </code>
   </a>
   and follow these rules:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Account name syntax is
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         user_name
        </code>
       </em>
       '@'
       <em class="replaceable">
        <code>
         host_name
        </code>
       </em>
       '
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       @'
       <em class="replaceable">
        <code>
         host_name
        </code>
       </em>
       '
      </code>
      part is optional. An account name consisting only of a user
          name is equivalent to
      <code class="literal">
       '
       <em class="replaceable">
        <code>
         user_name
        </code>
       </em>
       '@'%'
      </code>
      .
          For example,
      <code class="literal">
       'me'
      </code>
      is equivalent to
      <code class="literal">
       'me'@'%'
      </code>
      .
     </p>
    </li>
    <li class="listitem">
     <p>
      The user name and host name need not be quoted if they are
          legal as unquoted identifiers. Quotes must be used if a
      <em class="replaceable">
       <code>
        user_name
       </code>
      </em>
      string contains special
          characters (such as space or
      <code class="literal">
       -
      </code>
      ), or a
      <em class="replaceable">
       <code>
        host_name
       </code>
      </em>
      string contains special
          characters or wildcard characters (such as
      <code class="literal">
       .
      </code>
      or
      <code class="literal">
       %
      </code>
      ). For example, in
          the account name
      <code class="literal">
       'test-user'@'%.com'
      </code>
      , both
          the user name and host name parts require quotes.
     </p>
    </li>
    <li class="listitem">
     <p>
      Quote user names and host names as identifiers or as strings,
          using either backticks (
      <code class="literal">
       `
      </code>
      ), single
          quotation marks (
      <code class="literal">
       '
      </code>
      ), or double quotation
          marks (
      <code class="literal">
       "
      </code>
      ). For string-quoting and
          identifier-quoting guidelines, see
      <a class="xref" href="string-literals.html" title="11.1.1 String Literals">
       Section 11.1.1, “String Literals”
      </a>
      , and
      <a class="xref" href="identifiers.html" title="11.2 Schema Object Names">
       Section 11.2, “Schema Object Names”
      </a>
      . In
      <a class="link" href="show.html" title="15.7.7 SHOW Statements">
       <code class="literal">
        SHOW
       </code>
      </a>
      statement results, user
          names and host names are quoted using backticks
          (
      <code class="literal">
       `
      </code>
      ).
     </p>
    </li>
    <li class="listitem">
     <p>
      The user name and host name parts, if quoted, must be quoted
          separately. That is, write
      <code class="literal">
       'me'@'localhost'
      </code>
      , not
      <code class="literal">
       'me@localhost'
      </code>
      . (The latter is actually
          equivalent to
      <code class="literal">
       'me@localhost'@'%'
      </code>
      , although
          this behavior is now deprecated.)
     </p>
    </li>
    <li class="listitem">
     <p>
      A reference to the
      <a class="link" href="information-functions.html#function_current-user">
       <code class="literal">
        CURRENT_USER
       </code>
      </a>
      or
      <a class="link" href="information-functions.html#function_current-user">
       <code class="literal">
        CURRENT_USER()
       </code>
      </a>
      function is
          equivalent to specifying the current client's user name and
          host name literally.
     </p>
    </li>
   </ul>
  </div>
  <p>
   MySQL stores account names in grant tables in the
   <code class="literal">
    mysql
   </code>
   system database using separate columns
      for the user name and host name parts:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The
      <code class="literal">
       user
      </code>
      table contains one row for each
          account. The
      <code class="literal">
       User
      </code>
      and
      <code class="literal">
       Host
      </code>
      columns store the user name and host
          name. This table also indicates which global privileges the
          account has.
     </p>
    </li>
    <li class="listitem">
     <p>
      Other grant tables indicate privileges an account has for
          databases and objects within databases. These tables have
      <code class="literal">
       User
      </code>
      and
      <code class="literal">
       Host
      </code>
      columns to
          store the account name. Each row in these tables associates
          with the account in the
      <code class="literal">
       user
      </code>
      table that has
          the same
      <code class="literal">
       User
      </code>
      and
      <code class="literal">
       Host
      </code>
      values.
     </p>
    </li>
    <li class="listitem">
     <p>
      For access-checking purposes, comparisons of User values are
          case-sensitive. Comparisons of Host values are not
          case-sensitive.
     </p>
     <a class="indexterm" name="idm46045249957072">
     </a>
     <a class="indexterm" name="idm46045249955584">
     </a>
    </li>
   </ul>
  </div>
  <p>
   For additional detail about the properties of user names and host
      names as stored in the grant tables, such as maximum length, see
   <a class="xref" href="grant-tables.html#grant-tables-scope-column-properties" title="Grant Table Scope Column Properties">
    Grant Table Scope Column Properties
   </a>
   .
  </p>
  <p>
   User names and host names have certain special values or wildcard
      conventions, as described following.
  </p>
  <p>
   The user name part of an account name is either a nonblank value
      that literally matches the user name for incoming connection
      attempts, or a blank value (the empty string) that matches any
      user name. An account with a blank user name is an anonymous user.
      To specify an anonymous user in SQL statements, use a quoted empty
      user name part, such as
   <code class="literal">
    ''@'localhost'
   </code>
   .
  </p>
  <p>
   The host name part of an account name can take many forms, and
      wildcards are permitted:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A host value can be a host name or an IP address (IPv4 or
          IPv6). The name
      <code class="literal">
       'localhost'
      </code>
      indicates the
          local host. The IP address
      <code class="literal">
       '127.0.0.1'
      </code>
      indicates the IPv4 loopback interface. The IP address
      <code class="literal">
       '::1'
      </code>
      indicates the IPv6 loopback
          interface.
     </p>
    </li>
    <li class="listitem">
     <p>
      Use of the
      <code class="literal">
       %
      </code>
      and
      <code class="literal">
       _
      </code>
      wildcard characters is permitted in host name or IP address
          values, but is deprecated and thus subject to removal in a
          future version of MySQL. These characters have the same
          meaning as for pattern-matching operations performed with the
      <a class="link" href="string-comparison-functions.html#operator_like">
       <code class="literal">
        LIKE
       </code>
      </a>
      operator. For example, a
          host value of
      <code class="literal">
       '%'
      </code>
      matches any host name,
          whereas a value of
      <code class="literal">
       '%.mysql.com'
      </code>
      matches
          any host in the
      <code class="literal">
       mysql.com
      </code>
      domain.
      <code class="literal">
       '198.51.100.%'
      </code>
      matches any host in the
          198.51.100 class C network.
     </p>
     <a class="indexterm" name="idm46045249940032">
     </a>
     <a class="indexterm" name="idm46045249938528">
     </a>
     <p>
      Because IP wildcard values are permitted in host values (for
          example,
      <code class="literal">
       '198.51.100.%'
      </code>
      to match every host
          on a subnet), someone could try to exploit this capability by
          naming a host
      <code class="literal">
       198.51.100.somewhere.com
      </code>
      . To
          foil such attempts, MySQL does not perform matching on host
          names that start with digits and a dot. For example, if a host
          is named
      <code class="literal">
       1.2.example.com
      </code>
      , its name never
          matches the host part of account names. An IP wildcard value
          can match only IP addresses, not host names.
     </p>
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
       <code class="literal">
        partial_revokes
       </code>
      </a>
      is
      <code class="literal">
       ON
      </code>
      , MySQL treats
      <code class="literal">
       %
      </code>
      and
      <code class="literal">
       _
      </code>
      in grants as literal characters, and not
          as wildcards. Use of these wildcards is deprecated (regardless
          of this variable's value); you should expect this
          functionality to be removed in a future version of MySQL.
     </p>
    </li>
    <li class="listitem">
     <p>
      For a host value specified as an IPv4 address, a netmask can
          be given to indicate how many address bits to use for the
          network number. Netmask notation cannot be used for IPv6
          addresses.
     </p>
     <a class="indexterm" name="idm46045249929008">
     </a>
     <p>
      The syntax is
      <code class="literal">
       <em class="replaceable">
        <code>
         host_ip
        </code>
       </em>
       /
       <em class="replaceable">
        <code>
         netmask
        </code>
       </em>
      </code>
      .
          For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa57912228"><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">'david'</span>@<span class="token string">'198.51.100.0/255.255.255.0'</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This enables
      <code class="literal">
       david
      </code>
      to connect from any
          client host having an IP address
      <em class="replaceable">
       <code>
        client_ip
       </code>
      </em>
      for which the following
          condition is true:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa44927553"><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-clike"><em class="replaceable">client_ip</em> <span class="token operator">&amp;</span> <em class="replaceable">netmask</em> <span class="token operator">=</span> <em class="replaceable">host_ip</em></code></pre>
     </div>
     <p>
      That is, for the
      <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
       <code class="literal">
        CREATE USER
       </code>
      </a>
      statement just shown:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-clike"><div class="docs-select-all right" id="sa98418667"><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-clike"><em class="replaceable">client_ip</em> <span class="token operator">&amp;</span> <span class="token number">255.255</span><span class="token punctuation">.</span><span class="token number">255.0</span> <span class="token operator">=</span> <span class="token number">198.51</span><span class="token punctuation">.</span><span class="token number">100.0</span></code></pre>
     </div>
     <p>
      IP addresses that satisfy this condition range from
      <code class="literal">
       198.51.100.0
      </code>
      to
      <code class="literal">
       198.51.100.255
      </code>
      .
     </p>
     <p>
      A netmask typically begins with bits set to 1, followed by
          bits set to 0. Examples:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         <code class="literal">
          198.0.0.0/255.0.0.0
         </code>
         : Any host on the
              198 class A network
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          198.51.0.0/255.255.0.0
         </code>
         : Any host on the
              198.51 class B network
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          198.51.100.0/255.255.255.0
         </code>
         : Any host on
              the 198.51.100 class C network
        </p>
       </li>
       <li class="listitem">
        <p>
         <code class="literal">
          198.51.100.1
         </code>
         : Only the host with this
              specific IP address
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      A host value specified as an IPv4 address can be written using
          CIDR notation, such as
      <code class="literal">
       198.51.100.44/24
      </code>
      .
     </p>
     <a class="indexterm" name="idm46045249907456">
     </a>
    </li>
   </ul>
  </div>
  <p>
   The server performs matching of host values in account names
      against the client host using the value returned by the system DNS
      resolver for the client host name or IP address. Except in the
      case that the account host value is specified using netmask
      notation, the server performs this comparison as a string match,
      even for an account host value given as an IP address. This means
      that you should specify account host values in the same format
      used by DNS. Here are examples of problems to watch out for:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Suppose that a host on the local network has a fully qualified
          name of
      <code class="literal">
       host1.example.com
      </code>
      . If DNS returns
          name lookups for this host as
      <code class="literal">
       host1.example.com
      </code>
      , use that name in account
          host values. If DNS returns just
      <code class="literal">
       host1
      </code>
      , use
      <code class="literal">
       host1
      </code>
      instead.
     </p>
    </li>
    <li class="listitem">
     <p>
      If DNS returns the IP address for a given host as
      <code class="literal">
       198.51.100.2
      </code>
      , that matches an account host
          value of
      <code class="literal">
       198.51.100.2
      </code>
      but not
      <code class="literal">
       198.051.100.2
      </code>
      . Similarly, it matches an
          account host pattern like
      <code class="literal">
       198.51.100.%
      </code>
      but
          not
      <code class="literal">
       198.051.100.%
      </code>
      .
     </p>
    </li>
   </ul>
  </div>
  <p>
   To avoid problems like these, it is advisable to check the format
      in which your DNS returns host names and addresses. Use values in
      the same format in MySQL account names.
  </p>
 </div>
 <br/>
</div>
