<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="grant">
      </a>
      15.7.1.6 GRANT Statement
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045173992352">
  </a>
  <a class="indexterm" name="idm46045173991280">
  </a>
  <a class="indexterm" name="idm46045173990192">
  </a>
  <a class="indexterm" name="idm46045173988704">
  </a>
  <a class="indexterm" name="idm46045173987632">
  </a>
  <a class="indexterm" name="idm46045173986144">
  </a>
  <a class="indexterm" name="idm46045173984656">
  </a>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60414600"><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">GRANT</span>
    <em class="replaceable">priv_type</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">column_list</em><span class="token punctuation">)</span><span class="token punctuation">]</span>
      <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">priv_type</em> <span class="token punctuation">[</span><span class="token punctuation">(</span><em class="replaceable">column_list</em><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 keyword">ON</span> <span class="token punctuation">[</span><em class="replaceable">object_type</em><span class="token punctuation">]</span> <em class="replaceable">priv_level</em>
    <span class="token keyword">TO</span> <em class="replaceable">user_or_role</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">user_or_role</em><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 keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">]</span>
    <span class="token punctuation">[</span><span class="token keyword">AS</span> <span class="token keyword"><em class="replaceable">user</em></span>
        <span class="token punctuation">[</span><span class="token keyword">WITH</span> <span class="token keyword">ROLE</span>
            <span class="token keyword">DEFAULT</span>
          <span class="token operator">|</span> <span class="token keyword">NONE</span>
          <span class="token operator">|</span> <span class="token keyword">ALL</span>
          <span class="token operator">|</span> <span class="token keyword">ALL</span> <span class="token keyword">EXCEPT</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
          <span class="token operator">|</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">role</em></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 keyword">GRANT</span> <span class="token keyword">PROXY</span> <span class="token keyword">ON</span> <em class="replaceable">user_or_role</em>
    <span class="token keyword">TO</span> <em class="replaceable">user_or_role</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">user_or_role</em><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 keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">]</span>

<span class="token keyword">GRANT</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token punctuation">[</span><span class="token punctuation">,</span> <span class="token keyword"><em class="replaceable">role</em></span><span class="token punctuation">]</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
    <span class="token keyword">TO</span> <em class="replaceable">user_or_role</em> <span class="token punctuation">[</span><span class="token punctuation">,</span> <em class="replaceable">user_or_role</em><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 keyword">WITH</span> <span class="token keyword">ADMIN</span> <span class="token keyword">OPTION</span><span class="token punctuation">]</span>

<em class="replaceable">object_type</em>: {
    <span class="token keyword">TABLE</span>
  <span class="token operator">|</span> <span class="token keyword">FUNCTION</span>
  <span class="token operator">|</span> <span class="token keyword">PROCEDURE</span>
}

<em class="replaceable">priv_level</em>: {
    <span class="token operator">*</span>
  <span class="token operator">|</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span>
  <span class="token operator">|</span> <em class="replaceable">db_name</em><span class="token punctuation">.</span><span class="token operator">*</span>
  <span class="token operator">|</span> <em class="replaceable">db_name<span class="token punctuation">.</span>tbl_name</em>
  <span class="token operator">|</span> <em class="replaceable">tbl_name</em>
  <span class="token operator">|</span> <em class="replaceable">db_name</em><span class="token punctuation">.</span><em class="replaceable">routine_name</em>
}

<em class="replaceable">user_or_role</em>: {
    <span class="token function"><em class="replaceable">user</em></span> <span class="token punctuation">(</span>see Section <span class="token number">8.2</span><span class="token punctuation">.</span><span class="token number">4</span><span class="token punctuation">,</span> “Specifying <span class="token keyword">Account</span> <span class="token keyword">Names</span>”<span class="token punctuation">)</span>
  <span class="token operator">|</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token punctuation">(</span>see Section <span class="token number">8.2</span><span class="token punctuation">.</span><span class="token number">5</span><span class="token punctuation">,</span> “Specifying <span class="token keyword">Role</span> <span class="token keyword">Names</span>”<span class="token punctuation">)</span>
}</code></pre>
  </div>
  <p>
   The
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   statement assigns
        privileges and roles to MySQL user accounts and roles. There are
        several aspects to the
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   statement, described under the following topics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-overview" title="GRANT General Overview">
       GRANT General Overview
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-quoting" title="Object Quoting Guidelines">
       Object Quoting Guidelines
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-accounts" title="Account Names">
       Account Names
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-privileges" title="Privileges Supported by MySQL">
       Privileges Supported by MySQL
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-global-privileges" title="Global Privileges">
       Global Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-database-privileges" title="Database Privileges">
       Database Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-table-privileges" title="Table Privileges">
       Table Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-column-privileges" title="Column Privileges">
       Column Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-routine-privileges" title="Stored Routine Privileges">
       Stored Routine Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-proxy-privileges" title="Proxy User Privileges">
       Proxy User Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-roles" title="Granting Roles">
       Granting Roles
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-as" title="The AS Clause and Privilege Restrictions">
       The
       <code class="literal">
        AS
       </code>
       Clause and Privilege Restrictions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-other-characteristics" title="Other Account Characteristics">
       Other Account Characteristics
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="grant.html#grant-mysql-vs-standard-sql" title="MySQL and Standard SQL Versions of GRANT">
       MySQL and Standard SQL Versions of GRANT
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-overview">
       </a>
       GRANT General Overview
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement enables
          system administrators to grant privileges and roles, which can
          be granted to user accounts and roles. These syntax
          restrictions apply:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       cannot mix granting
              both privileges and roles in the same statement. A given
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement must grant
              either privileges or roles.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        ON
       </code>
       clause distinguishes whether the
              statement grants privileges or roles:
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          With
          <code class="literal">
           ON
          </code>
          , the statement grants
                  privileges.
         </p>
        </li>
        <li class="listitem">
         <p>
          Without
          <code class="literal">
           ON
          </code>
          , the statement grants
                  roles.
         </p>
        </li>
        <li class="listitem">
         <p>
          It is permitted to assign both privileges and roles to
                  an account, but you must use separate
          <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
           <code class="literal">
            GRANT
           </code>
          </a>
          statements, each
                  with syntax appropriate to what is to be granted.
         </p>
        </li>
       </ul>
      </div>
     </li>
    </ul>
   </div>
   <p>
    For more information about roles, see
    <a class="xref" href="roles.html" title="8.2.10 Using Roles">
     Section 8.2.10, “Using Roles”
    </a>
    .
   </p>
   <p>
    To grant a privilege with
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    , you must have the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    privilege, and you
          must have the privileges that you are granting.
          (Alternatively, if you have the
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    privilege for the grant
          tables in the
    <code class="literal">
     mysql
    </code>
    system schema, you can
          grant any account any privilege.) When the
    <a class="link" href="server-system-variables.html#sysvar_read_only">
     <code class="literal">
      read_only
     </code>
    </a>
    system variable is
          enabled,
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    additionally
          requires the
    <a class="link" href="privileges-provided.html#priv_connection-admin">
     <code class="literal">
      CONNECTION_ADMIN
     </code>
    </a>
    privilege (or the deprecated
    <a class="link" href="privileges-provided.html#priv_super">
     <code class="literal">
      SUPER
     </code>
    </a>
    privilege).
   </p>
   <p>
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    either succeeds for all
          named users and roles or rolls back and has no effect if any
          error occurs. The statement is written to the binary log only
          if it succeeds for all named users and roles.
   </p>
   <p>
    The
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    statement is related
          to
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    and enables
          administrators to remove account privileges. See
    <a class="xref" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     Section 15.7.1.8, “REVOKE Statement”
    </a>
    .
   </p>
   <p>
    Each account name uses the format described in
    <a class="xref" href="account-names.html" title="8.2.4 Specifying Account Names">
     Section 8.2.4, “Specifying Account Names”
    </a>
    . Each role name uses the
          format described in
    <a class="xref" href="role-names.html" title="8.2.5 Specifying Role Names">
     Section 8.2.5, “Specifying Role Names”
    </a>
    . For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa50881731"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> db1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'jeffrey'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'role1'</span><span class="token punctuation">,</span> <span class="token string">'role2'</span> <span class="token keyword">TO</span> <span class="token string">'user1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token string">'user2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> world<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'role3'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The host name part of the account or role name, if omitted,
          defaults to
    <code class="literal">
     '%'
    </code>
    .
   </p>
   <p>
    Normally, a database administrator first uses
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    to create an
          account and define its nonprivilege characteristics such as
          its password, whether it uses secure connections, and limits
          on access to server resources, then uses
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    to define its privileges.
    <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
     <code class="literal">
      ALTER USER
     </code>
    </a>
    may be used to
          change the nonprivilege characteristics of existing accounts.
          For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40766853"><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">'jeffrey'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'<em class="replaceable">password</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> db1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'jeffrey'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> db2<span class="token punctuation">.</span>invoice <span class="token keyword">TO</span> <span class="token string">'jeffrey'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'jeffrey'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_QUERIES_PER_HOUR</span> <span class="token number">90</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    From the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    program,
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    responds with
    <code class="literal">
     Query OK, 0 rows affected
    </code>
    when executed
          successfully. To determine what privileges result from the
          operation, use
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW GRANTS
     </code>
    </a>
    . See
    <a class="xref" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     Section 15.7.7.22, “SHOW GRANTS Statement”
    </a>
    .
   </p>
   <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Important
    </div>
    <p>
     Under some circumstances,
     <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
      <code class="literal">
       GRANT
      </code>
     </a>
     may be recorded in
            server logs or on the client side in a history file such as
     <code class="filename">
      ~/.mysql_history
     </code>
     , which means that
            cleartext passwords may be read by anyone having read access
            to that information. For information about the conditions
            under which this occurs for the server logs and how to
            control it, see
     <a class="xref" href="password-logging.html" title="8.1.2.3 Passwords and Logging">
      Section 8.1.2.3, “Passwords and Logging”
     </a>
     . For
            similar information about client-side logging, see
     <a class="xref" href="mysql-logging.html" title="6.5.1.3 mysql Client Logging">
      Section 6.5.1.3, “mysql Client Logging”
     </a>
     .
    </p>
   </div>
   <p>
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    supports host names up to
          255 characters long. User names can be up to 32 characters.
          Database, table, column, and routine names can be up to 64
          characters.
   </p>
   <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Warning
    </div>
    <p>
     <span class="emphasis">
      <em>
       Do not attempt to change the permissible length
            for user names by altering the
       <code class="literal">
        mysql.user
       </code>
       system table. Doing so results in unpredictable behavior
            which may even make it impossible for users to log in to the
            MySQL server
      </em>
     </span>
     . Never alter the structure of tables
            in the
     <code class="literal">
      mysql
     </code>
     system schema in any manner
            except by means of the procedure described in
     <a class="xref" href="upgrading.html" title="Chapter 3 Upgrading MySQL">
      Chapter 3,
      <i>
       Upgrading MySQL
      </i>
     </a>
     .
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-quoting">
       </a>
       Object Quoting Guidelines
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045173887968">
   </a>
   <p>
    Several objects within
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statements are subject to quoting, although quoting is
          optional in many cases: Account, role, database, table,
          column, and routine names. For example, if a
    <em class="replaceable">
     <code>
      user_name
     </code>
    </em>
    or
    <em class="replaceable">
     <code>
      host_name
     </code>
    </em>
    value in an account name
          is legal as an unquoted identifier, you need not quote it.
          However, quotation marks are necessary to specify a
    <em class="replaceable">
     <code>
      user_name
     </code>
    </em>
    string containing special
          characters (such as
    <code class="literal">
     -
    </code>
    ), or a
    <em class="replaceable">
     <code>
      host_name
     </code>
    </em>
    string containing special
          characters or wildcard characters such as
    <code class="literal">
     %
    </code>
    (for example,
    <code class="literal">
     'test-user'@'%.com'
    </code>
    ). Quote
          the user name and host name separately.
   </p>
   <p>
    To specify quoted values:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Quote database, table, column, and routine names as
              identifiers.
      </p>
     </li>
     <li class="listitem">
      <p>
       Quote user names and host names as identifiers or as
              strings.
      </p>
     </li>
     <li class="listitem">
      <p>
       Quote passwords as strings.
      </p>
     </li>
    </ul>
   </div>
   <p>
    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>
    .
   </p>
   <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Important
    </div>
    <p>
     The use of the wildcard characters
     <code class="literal">
      %
     </code>
     and
     <code class="literal">
      _
     </code>
     as described in the next few paragraphs
            is deprecated, and thus subject to removal in a future
            version of MySQL.
    </p>
   </div>
   <p>
    The
    <code class="literal">
     _
    </code>
    and
    <code class="literal">
     %
    </code>
    wildcards
          are permitted when specifying database names in
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statements that grant
          privileges at the database level (
    <code class="literal">
     GRANT ... ON
     <em class="replaceable">
      <code>
       db_name
      </code>
     </em>
     .*
    </code>
    ). This means,
          for example, that to use a
    <code class="literal">
     _
    </code>
    character as
          part of a database name, specify it using the
    <code class="literal">
     \
    </code>
    escape character as
    <code class="literal">
     \_
    </code>
    in the
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement, to
          prevent the user from being able to access additional
          databases matching the wildcard pattern (for example,
    <code class="literal">
     GRANT ... ON `foo\_bar`.* TO ...
    </code>
    ).
   </p>
   <p>
    Issuing multiple
    <code class="literal">
     GRANT
    </code>
    statements
          containing wildcards may not have the expected effect on DML
          statements; when resolving grants involving wildcards, MySQL
          takes only the first matching grant into consideration. In
          other words, if a user has two database-level grants using
          wildcards that match the same database, the grant which was
          created first is applied. Consider the database
    <code class="literal">
     db
    </code>
    and table
    <code class="literal">
     t
    </code>
    created
          using the statements shown here:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51146250"><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">CREATE</span> <span class="token keyword">DATABASE</span> db<span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">TABLE</span> db<span class="token punctuation">.</span>t <span class="token punctuation">(</span>c <span class="token datatype">INT</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> db<span class="token punctuation">.</span>t <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">1</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">Query OK, 1 row affected (0.00 sec)</span></code></pre>
   </div>
   <p>
    Next (assuming that the current account is the MySQL
    <code class="literal">
     root
    </code>
    account or another account having the
          necessary privileges), we create a user
    <code class="literal">
     u
    </code>
    then issue two
    <code class="literal">
     GRANT
    </code>
    statements containing
          wildcards, like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa11958706"><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">CREATE</span> <span class="token keyword">USER</span> u<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> <span class="token punctuation">`</span>d_<span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.01 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token punctuation">`</span>d<span class="token operator">%</span><span class="token punctuation">`</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u<span class="token punctuation">;</span>
<span class="token output">Query OK, 0 rows affected (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">EXIT</span></code></pre>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa75848072"><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-terminal">Bye</code></pre>
   </div>
   <p>
    If we end the session and then log in again with the
    <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
     <span class="command">
      <strong>
       mysql
      </strong>
     </span>
    </a>
    client, this time as
    <span class="command">
     <strong>
      u
     </strong>
    </span>
    , we see that this account has only the
          privilege provided by the first matching grant, but not the
          second:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa52823381"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">-uu</span> <span class="token property">-hlocalhost</span></code></pre>
   </div>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6900208"><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">Welcome <span class="token keyword">to</span> the MySQL monitor<span class="token punctuation">.</span>  Commands <span class="token keyword">end</span> <span class="token keyword">with</span> <span class="token punctuation">;</span> <span class="token operator">or</span> \g<span class="token punctuation">.</span>
Your MySQL <span class="token keyword">connection</span> id <span class="token operator">is</span> <span class="token number">10</span>
<span class="token keyword">Server</span> version: <span class="token number">8.4</span><span class="token punctuation">.</span><span class="token number">4</span><span class="token operator">-</span>tr <span class="token keyword">Source</span> distribution

Copyright <span class="token punctuation">(</span>c<span class="token punctuation">)</span> <span class="token number">2000</span><span class="token punctuation">,</span> <span class="token number">2023</span><span class="token punctuation">,</span> Oracle <span class="token operator">and</span><span class="token operator">/</span><span class="token operator">or</span> its affiliates<span class="token punctuation">.</span>

Oracle <span class="token operator">is</span> a registered trademark <span class="token keyword">of</span> Oracle Corporation <span class="token operator">and</span><span class="token operator">/</span><span class="token operator">or</span> its
affiliates<span class="token punctuation">.</span> Other <span class="token keyword">names</span> may be trademarks <span class="token keyword">of</span> their respective
owners<span class="token punctuation">.</span>

<span class="token keyword">Type</span> <span class="token string">'help;'</span> <span class="token operator">or</span> <span class="token string">'\h'</span> <span class="token keyword">for</span> <span class="token keyword">help</span><span class="token punctuation">.</span> <span class="token keyword">Type</span> <span class="token string">'\c'</span> <span class="token keyword">to</span> clear the <span class="token keyword">current</span> input
statement<span class="token punctuation">.</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">TABLE</span> db<span class="token punctuation">.</span>t<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>
<span class="token output"><span class="token punctuation">|</span> c    <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>
<span class="token output"><span class="token punctuation">|</span>    1 <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>
<span class="token output">1 row in set (0.00 sec)</span>

<span class="token prompt">mysql&gt;</span> <span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> db<span class="token punctuation">.</span>t <span class="token keyword">VALUES</span> <span class="token keyword">ROW</span><span class="token punctuation">(</span><span class="token number">2</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1142 (42000)<span class="token punctuation">:</span> INSERT command denied to user 'u'@'localhost' for table 't'</span></code></pre>
   </div>
   <p>
    In privilege assignments, MySQL interprets occurrences of
          unescaped
    <code class="literal">
     _
    </code>
    and
    <code class="literal">
     %
    </code>
    SQL
          wildcard characters in database names as literal characters
          under these circumstances:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       When a database name is not used to grant privileges at
              the database level, but as a qualifier for granting
              privileges to some other object such as a table or routine
              (for example,
       <code class="literal">
        GRANT ... ON
        <em class="replaceable">
         <code>
          db_name
         </code>
        </em>
        .
        <em class="replaceable">
         <code>
          tbl_name
         </code>
        </em>
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       Enabling
       <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
        <code class="literal">
         partial_revokes
        </code>
       </a>
       causes MySQL to interpret unescaped
       <code class="literal">
        _
       </code>
       and
       <code class="literal">
        %
       </code>
       wildcard characters in database
              names as literal characters, just as if they had been
              escaped as
       <code class="literal">
        \_
       </code>
       and
       <code class="literal">
        \%
       </code>
       . Because this changes how MySQL
              interprets privileges, it may be advisable to avoid
              unescaped wildcard characters in privilege assignments for
              installations where
       <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
        <code class="literal">
         partial_revokes
        </code>
       </a>
       may be
              enabled. For more information, see
       <a class="xref" href="partial-revokes.html" title="8.2.12 Privilege Restriction Using Partial Revokes">
        Section 8.2.12, “Privilege Restriction Using Partial Revokes”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-accounts">
       </a>
       Account Names
      </h5>
     </div>
    </div>
   </div>
   <p>
    A
    <em class="replaceable">
     <code>
      user
     </code>
    </em>
    value in a
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement indicates a
          MySQL account to which the statement applies. To accommodate
          granting rights to users from arbitrary hosts, MySQL supports
          specifying the
    <em class="replaceable">
     <code>
      user
     </code>
    </em>
    value in the
          form
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '@'
     <em class="replaceable">
      <code>
       host_name
      </code>
     </em>
     '
    </code>
    .
   </p>
   <p>
    You can specify wildcards in the host name. For example,
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '@'%.example.com'
    </code>
    applies to
    <em class="replaceable">
     <code>
      user_name
     </code>
    </em>
    for any host
          in the
    <code class="literal">
     example.com
    </code>
    domain, and
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '@'198.51.100.%'
    </code>
    applies to
    <em class="replaceable">
     <code>
      user_name
     </code>
    </em>
    for any host
          in the
    <code class="literal">
     198.51.100
    </code>
    class C subnet.
   </p>
   <p>
    The simple form
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '
    </code>
    is a
          synonym for
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '@'%'
    </code>
    .
   </p>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     MySQL automatically assigns all privileges granted to
     <code class="literal">
      '
      <em class="replaceable">
       <code>
        username
       </code>
      </em>
      '@'%'
     </code>
     to the
     <code class="literal">
      '
      <em class="replaceable">
       <code>
        username
       </code>
      </em>
      '@'localhost'
     </code>
     account as well. This behavior is deprecated, and is subject
            to removal in a future version of MySQL.
    </p>
   </div>
   <p>
    <span class="emphasis">
     <em>
      MySQL does not support wildcards in user
          names
     </em>
    </span>
    . To refer to an anonymous user, specify an
          account with an empty user name with the
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa32967029"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> test<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">''</span>@<span class="token string">'localhost'</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    In this case, any user who connects from the local host with
          the correct password for the anonymous user is permitted
          access, with the privileges associated with the anonymous-user
          account.
   </p>
   <p>
    For additional information about user name and host name
          values in account names, see
    <a class="xref" href="account-names.html" title="8.2.4 Specifying Account Names">
     Section 8.2.4, “Specifying Account Names”
    </a>
    .
   </p>
   <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Warning
    </div>
    <p>
     If you permit local anonymous users to connect to the MySQL
            server, you should also grant privileges to all local users
            as
     <code class="literal">
      '
      <em class="replaceable">
       <code>
        user_name
       </code>
      </em>
      '@'localhost'
     </code>
     .
            Otherwise, the anonymous user account for
     <code class="literal">
      localhost
     </code>
     in the
     <code class="literal">
      mysql.user
     </code>
     system table is used when
            named users try to log in to the MySQL server from the local
            machine. For details, see
     <a class="xref" href="connection-access.html" title="8.2.6 Access Control, Stage 1: Connection Verification">
      Section 8.2.6, “Access Control, Stage 1: Connection Verification”
     </a>
     .
    </p>
    <p>
     To determine whether this issue applies to you, execute the
            following query, which lists any anonymous users:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa35770037"><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">SELECT</span> <span class="token keyword">Host</span><span class="token punctuation">,</span> <span class="token keyword">User</span> <span class="token keyword">FROM</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span> <span class="token keyword">WHERE</span> <span class="token keyword">User</span><span class="token operator">=</span><span class="token string">''</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     To avoid the problem just described, delete the local
            anonymous user account using this statement:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa22742286"><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">DROP</span> <span class="token keyword">USER</span> <span class="token string">''</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
    </div>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-privileges">
       </a>
       Privileges Supported by MySQL
      </h5>
     </div>
    </div>
   </div>
   <p>
    The following tables summarize the permissible static and
          dynamic
    <em class="replaceable">
     <code>
      priv_type
     </code>
    </em>
    privilege types
          that can be specified for the
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    and
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    statements, and the
          levels at which each privilege can be granted. For additional
          information about each privilege, see
    <a class="xref" href="privileges-provided.html" title="8.2.2 Privileges Provided by MySQL">
     Section 8.2.2, “Privileges Provided by MySQL”
    </a>
    . For information about
          the differences between static and dynamic privileges, see
    <a class="xref" href="privileges-provided.html#static-dynamic-privileges" title="Static Versus Dynamic Privileges">
     Static Versus Dynamic Privileges
    </a>
    .
   </p>
   <div class="table">
    <a name="idm46045173794304">
    </a>
    <p class="title">
     <b>
      Table 15.11 Permissible Static Privileges for GRANT and REVOKE
     </b>
    </p>
    <div class="table-contents">
     <table>
      <colgroup>
       <col style="width: 30%"/>
       <col style="width: 70%"/>
      </colgroup>
      <thead>
       <tr>
        <th>
         Privilege
        </th>
        <th>
         Meaning and Grantable Levels
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_all">
          <code class="literal">
           ALL [PRIVILEGES]
          </code>
         </a>
        </td>
        <td>
         Grant all privileges at specified access level except
         <a class="link" href="privileges-provided.html#priv_grant-option">
          <code class="literal">
           GRANT OPTION
          </code>
         </a>
         and
         <a class="link" href="privileges-provided.html#priv_proxy">
          <code class="literal">
           PROXY
          </code>
         </a>
         .
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_alter">
          <code class="literal">
           ALTER
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="alter-table.html" title="15.1.9 ALTER TABLE Statement">
          <code class="literal">
           ALTER TABLE
          </code>
         </a>
         . Levels:
                Global, database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_alter-routine">
          <code class="literal">
           ALTER ROUTINE
          </code>
         </a>
        </td>
        <td>
         Enable stored routines to be altered or dropped. Levels: Global,
                database, routine.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create">
          <code class="literal">
           CREATE
          </code>
         </a>
        </td>
        <td>
         Enable database and table creation. Levels: Global, database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-role">
          <code class="literal">
           CREATE ROLE
          </code>
         </a>
        </td>
        <td>
         Enable role creation. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-routine">
          <code class="literal">
           CREATE ROUTINE
          </code>
         </a>
        </td>
        <td>
         Enable stored routine creation. Levels: Global, database.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-tablespace">
          <code class="literal">
           CREATE TABLESPACE
          </code>
         </a>
        </td>
        <td>
         Enable tablespaces and log file groups to be created, altered, or
                dropped. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-temporary-tables">
          <code class="literal">
           CREATE TEMPORARY TABLES
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="create-table.html" title="15.1.20 CREATE TABLE Statement">
          <code class="literal">
           CREATE
                TEMPORARY TABLE
          </code>
         </a>
         . Levels: Global, database.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-user">
          <code class="literal">
           CREATE USER
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <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="drop-user.html" title="15.7.1.5 DROP USER Statement">
          <code class="literal">
           DROP USER
          </code>
         </a>
         ,
         <a class="link" href="rename-user.html" title="15.7.1.7 RENAME USER Statement">
          <code class="literal">
           RENAME USER
          </code>
         </a>
         , and
         <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
          <code class="literal">
           REVOKE ALL
                PRIVILEGES
          </code>
         </a>
         . Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_create-view">
          <code class="literal">
           CREATE VIEW
          </code>
         </a>
        </td>
        <td>
         Enable views to be created or altered. Levels: Global, database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_delete">
          <code class="literal">
           DELETE
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
          <code class="literal">
           DELETE
          </code>
         </a>
         . Level: Global,
                database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_drop">
          <code class="literal">
           DROP
          </code>
         </a>
        </td>
        <td>
         Enable databases, tables, and views to be dropped. Levels: Global,
                database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_drop-role">
          <code class="literal">
           DROP ROLE
          </code>
         </a>
        </td>
        <td>
         Enable roles to be dropped. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_event">
          <code class="literal">
           EVENT
          </code>
         </a>
        </td>
        <td>
         Enable use of events for the Event Scheduler. Levels: Global, database.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_execute">
          <code class="literal">
           EXECUTE
          </code>
         </a>
        </td>
        <td>
         Enable the user to execute stored routines. Levels: Global, database,
                routine.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_file">
          <code class="literal">
           FILE
          </code>
         </a>
        </td>
        <td>
         Enable the user to cause the server to read or write files. Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_flush-privileges">
          <code class="literal">
           FLUSH_PRIVILEGES
          </code>
         </a>
        </td>
        <td>
         Enable the user to issue
         <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
          <code class="literal">
           FLUSH
                PRIVILEGES
          </code>
         </a>
         statements. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_grant-option">
          <code class="literal">
           GRANT OPTION
          </code>
         </a>
        </td>
        <td>
         Enable privileges to be granted to or removed from other accounts.
                Levels: Global, database, table, routine, proxy.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_index">
          <code class="literal">
           INDEX
          </code>
         </a>
        </td>
        <td>
         Enable indexes to be created or dropped. Levels: Global, database,
                table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_insert">
          <code class="literal">
           INSERT
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
          <code class="literal">
           INSERT
          </code>
         </a>
         . Levels: Global,
                database, table, column.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_lock-tables">
          <code class="literal">
           LOCK TABLES
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="lock-tables.html" title="15.3.6 LOCK TABLES and UNLOCK TABLES Statements">
          <code class="literal">
           LOCK TABLES
          </code>
         </a>
         on tables for
                which you have the
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         privilege. Levels: Global, database.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_optimize-local-table">
          <code class="literal">
           OPTIMIZE_LOCAL_TABLE
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="optimize-table.html" title="15.7.3.4 OPTIMIZE TABLE Statement">
          <code class="literal">
           OPTIMIZE
                LOCAL TABLE
          </code>
         </a>
         or
         <code class="literal">
          OPTIMIZE
                NO_WRITE_TO_BINLOG TABLE
         </code>
         . Levels: Global,
                database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_process">
          <code class="literal">
           PROCESS
          </code>
         </a>
        </td>
        <td>
         Enable the user to see all processes with
         <a class="link" href="show-processlist.html" title="15.7.7.31 SHOW PROCESSLIST Statement">
          <code class="literal">
           SHOW
                PROCESSLIST
          </code>
         </a>
         . Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_proxy">
          <code class="literal">
           PROXY
          </code>
         </a>
        </td>
        <td>
         Enable user proxying. Level: From user to user.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_references">
          <code class="literal">
           REFERENCES
          </code>
         </a>
        </td>
        <td>
         Enable foreign key creation. Levels: Global, database, table, column.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_reload">
          <code class="literal">
           RELOAD
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="flush.html" title="15.7.8.3 FLUSH Statement">
          <code class="literal">
           FLUSH
          </code>
         </a>
         operations. Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_replication-client">
          <code class="literal">
           REPLICATION CLIENT
          </code>
         </a>
        </td>
        <td>
         Enable the user to ask where source or replica servers are. Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_replication-slave">
          <code class="literal">
           REPLICATION SLAVE
          </code>
         </a>
        </td>
        <td>
         Enable replicas to read binary log events from the source. Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_select">
          <code class="literal">
           SELECT
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="select.html" title="15.2.13 SELECT Statement">
          <code class="literal">
           SELECT
          </code>
         </a>
         . Levels: Global,
                database, table, column.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_show-databases">
          <code class="literal">
           SHOW DATABASES
          </code>
         </a>
        </td>
        <td>
         Enable
         <a class="link" href="show-databases.html" title="15.7.7.15 SHOW DATABASES Statement">
          <code class="literal">
           SHOW DATABASES
          </code>
         </a>
         to show all
                databases. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_show-view">
          <code class="literal">
           SHOW VIEW
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="show-create-view.html" title="15.7.7.14 SHOW CREATE VIEW Statement">
          <code class="literal">
           SHOW CREATE VIEW
          </code>
         </a>
         . Levels:
                Global, database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_shutdown">
          <code class="literal">
           SHUTDOWN
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
          <span class="command">
           <strong>
            mysqladmin shutdown
           </strong>
          </span>
         </a>
         . Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_super">
          <code class="literal">
           SUPER
          </code>
         </a>
        </td>
        <td>
         Enable use of other administrative operations such as
         <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
          <code class="literal">
           CHANGE REPLICATION SOURCE
                TO
          </code>
         </a>
         ,
         <a class="link" href="kill.html" title="15.7.8.4 KILL Statement">
          <code class="literal">
           KILL
          </code>
         </a>
         ,
         <a class="link" href="purge-binary-logs.html" title="15.4.1.1 PURGE BINARY LOGS Statement">
          <code class="literal">
           PURGE BINARY LOGS
          </code>
         </a>
         ,
         <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
          <code class="literal">
           SET
                GLOBAL
          </code>
         </a>
         , and
         <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
          <span class="command">
           <strong>
            mysqladmin
                debug
           </strong>
          </span>
         </a>
         command. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_trigger">
          <code class="literal">
           TRIGGER
          </code>
         </a>
        </td>
        <td>
         Enable trigger operations. Levels: Global, database, table.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_update">
          <code class="literal">
           UPDATE
          </code>
         </a>
        </td>
        <td>
         Enable use of
         <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
          <code class="literal">
           UPDATE
          </code>
         </a>
         . Levels: Global,
                database, table, column.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_usage">
          <code class="literal">
           USAGE
          </code>
         </a>
        </td>
        <td>
         Synonym for
         <span class="quote">
          “
          <span class="quote">
           no privileges
          </span>
          ”
         </span>
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th style="width: 221.391px;">
         Privilege
        </th>
        <th style="width: 516.609px;">
         Meaning and Grantable Levels
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <div class="table">
    <a name="idm46045173642080">
    </a>
    <p class="title">
     <b>
      Table 15.12 Permissible Dynamic Privileges for GRANT and REVOKE
     </b>
    </p>
    <div class="table-contents">
     <table>
      <colgroup>
       <col style="width: 30%"/>
       <col style="width: 70%"/>
      </colgroup>
      <thead>
       <tr>
        <th>
         Privilege
        </th>
        <th>
         Meaning and Grantable Levels
        </th>
       </tr>
      </thead>
      <tbody>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_application-password-admin">
          <code class="literal">
           APPLICATION_PASSWORD_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable dual password administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_audit-abort-exempt">
          <code class="literal">
           AUDIT_ABORT_EXEMPT
          </code>
         </a>
        </td>
        <td>
         Allow queries blocked by audit log filter. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_audit-admin">
          <code class="literal">
           AUDIT_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable audit log configuration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_authentication-policy-admin">
          <code class="literal">
           AUTHENTICATION_POLICY_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable authentication policy administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_backup-admin">
          <code class="literal">
           BACKUP_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable backup administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_binlog-admin">
          <code class="literal">
           BINLOG_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable binary log control. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_binlog-encryption-admin">
          <code class="literal">
           BINLOG_ENCRYPTION_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable activation and deactivation of binary log encryption. Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_clone-admin">
          <code class="literal">
           CLONE_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable clone administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_connection-admin">
          <code class="literal">
           CONNECTION_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable connection limit/restriction control. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_encryption-key-admin">
          <code class="literal">
           ENCRYPTION_KEY_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable
         <code class="literal">
          InnoDB
         </code>
         key rotation. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_firewall-admin">
          <code class="literal">
           FIREWALL_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable firewall rule administration, any user. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_firewall-exempt">
          <code class="literal">
           FIREWALL_EXEMPT
          </code>
         </a>
        </td>
        <td>
         Exempt user from firewall restrictions. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_firewall-user">
          <code class="literal">
           FIREWALL_USER
          </code>
         </a>
        </td>
        <td>
         Enable firewall rule administration, self. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_flush-optimizer-costs">
          <code class="literal">
           FLUSH_OPTIMIZER_COSTS
          </code>
         </a>
        </td>
        <td>
         Enable optimizer cost reloading. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_flush-status">
          <code class="literal">
           FLUSH_STATUS
          </code>
         </a>
        </td>
        <td>
         Enable status indicator flushing. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_flush-tables">
          <code class="literal">
           FLUSH_TABLES
          </code>
         </a>
        </td>
        <td>
         Enable table flushing. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_flush-user-resources">
          <code class="literal">
           FLUSH_USER_RESOURCES
          </code>
         </a>
        </td>
        <td>
         Enable user-resource flushing. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_group-replication-admin">
          <code class="literal">
           GROUP_REPLICATION_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable Group Replication control. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_innodb-redo-log-archive">
          <code class="literal">
           INNODB_REDO_LOG_ARCHIVE
          </code>
         </a>
        </td>
        <td>
         Enable redo log archiving administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_innodb-redo-log-enable">
          <code class="literal">
           INNODB_REDO_LOG_ENABLE
          </code>
         </a>
        </td>
        <td>
         Enable or disable redo logging. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_ndb-stored-user">
          <code class="literal">
           NDB_STORED_USER
          </code>
         </a>
        </td>
        <td>
         Enable sharing of user or role between SQL nodes (NDB Cluster). Level:
                Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_passwordless-user-admin">
          <code class="literal">
           PASSWORDLESS_USER_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable passwordless user account administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_persist-ro-variables-admin">
          <code class="literal">
           PERSIST_RO_VARIABLES_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable persisting read-only system variables. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_replication-applier">
          <code class="literal">
           REPLICATION_APPLIER
          </code>
         </a>
        </td>
        <td>
         Act as the
         <code class="literal">
          PRIVILEGE_CHECKS_USER
         </code>
         for a replication
                channel. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_replication-slave-admin">
          <code class="literal">
           REPLICATION_SLAVE_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable regular replication control. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_resource-group-admin">
          <code class="literal">
           RESOURCE_GROUP_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable resource group administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_resource-group-user">
          <code class="literal">
           RESOURCE_GROUP_USER
          </code>
         </a>
        </td>
        <td>
         Enable resource group administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_role-admin">
          <code class="literal">
           ROLE_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable roles to be granted or revoked, use of
         <code class="literal">
          WITH ADMIN
                OPTION
         </code>
         . Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_session-variables-admin">
          <code class="literal">
           SESSION_VARIABLES_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable setting restricted session system variables. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_show-routine">
          <code class="literal">
           SHOW_ROUTINE
          </code>
         </a>
        </td>
        <td>
         Enable access to stored routine definitions. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_skip-query-rewrite">
          <code class="literal">
           SKIP_QUERY_REWRITE
          </code>
         </a>
        </td>
        <td>
         Do not rewrite queries executed by this user. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_system-user">
          <code class="literal">
           SYSTEM_USER
          </code>
         </a>
        </td>
        <td>
         Designate account as system account. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_system-variables-admin">
          <code class="literal">
           SYSTEM_VARIABLES_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable modifying or persisting global system variables. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_table-encryption-admin">
          <code class="literal">
           TABLE_ENCRYPTION_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable overriding default encryption settings. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_telemetry-log-admin">
          <code class="literal">
           TELEMETRY_LOG_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable telemetry log configuration for HeatWave on AWS. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_tp-connection-admin">
          <code class="literal">
           TP_CONNECTION_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable thread pool connection administration. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_version-token-admin">
          <code class="literal">
           VERSION_TOKEN_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable use of Version Tokens functions. Level: Global.
        </td>
       </tr>
       <tr>
        <td>
         <a class="link" href="privileges-provided.html#priv_xa-recover-admin">
          <code class="literal">
           XA_RECOVER_ADMIN
          </code>
         </a>
        </td>
        <td>
         Enable
         <a class="link" href="xa-statements.html" title="15.3.8.1 XA Transaction SQL Statements">
          <code class="literal">
           XA
                RECOVER
          </code>
         </a>
         execution. Level: Global.
        </td>
       </tr>
      </tbody>
     </table>
    </div>
    <div class="table-contents">
     <table cellpadding="0" cellspacing="0" style="position: fixed; top: 0px; display: none; left: 401px; width: 739px;">
      <thead>
       <tr>
        <th style="width: 226.391px;">
         Privilege
        </th>
        <th style="width: 511.609px;">
         Meaning and Grantable Levels
        </th>
       </tr>
      </thead>
     </table>
    </div>
   </div>
   <br class="table-break"/>
   <p>
    A trigger is associated with a table. To create or drop a
          trigger, you must have the
    <a class="link" href="privileges-provided.html#priv_trigger">
     <code class="literal">
      TRIGGER
     </code>
    </a>
    privilege for the
          table, not the trigger.
   </p>
   <p>
    In
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statements, the
    <a class="link" href="privileges-provided.html#priv_all">
     <code class="literal">
      ALL
          [PRIVILEGES]
     </code>
    </a>
    or
    <a class="link" href="privileges-provided.html#priv_proxy">
     <code class="literal">
      PROXY
     </code>
    </a>
    privilege must be named by itself and cannot be specified
          along with other privileges.
    <a class="link" href="privileges-provided.html#priv_all">
     <code class="literal">
      ALL
          [PRIVILEGES]
     </code>
    </a>
    stands for all privileges available for
          the level at which privileges are to be granted except for the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    and
    <a class="link" href="privileges-provided.html#priv_proxy">
     <code class="literal">
      PROXY
     </code>
    </a>
    privileges.
   </p>
   <p>
    MySQL account information is stored in the tables of the
    <code class="literal">
     mysql
    </code>
    system schema. For additional
          details, consult
    <a class="xref" href="access-control.html" title="8.2 Access Control and Account Management">
     Section 8.2, “Access Control and Account Management”
    </a>
    , which
          discusses the
    <code class="literal">
     mysql
    </code>
    system schema and the
          access control system extensively.
   </p>
   <a class="indexterm" name="idm46045173501168">
   </a>
   <p>
    If the grant tables hold privilege rows that contain
          mixed-case database or table names and the
    <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
     <code class="literal">
      lower_case_table_names
     </code>
    </a>
    system
          variable is set to a nonzero value,
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    cannot be used to revoke
          these privileges. It is necessary in such cases to manipulate
          the grant tables directly.
          (
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    does not create such
          rows when
    <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
     <code class="literal">
      lower_case_table_names
     </code>
    </a>
    is
          set, but such rows might have been created prior to setting
          that variable. The
    <a class="link" href="server-system-variables.html#sysvar_lower_case_table_names">
     <code class="literal">
      lower_case_table_names
     </code>
    </a>
    setting can only be configured at server startup.)
   </p>
   <p>
    Privileges can be granted at several levels, depending on the
          syntax used for the
    <code class="literal">
     ON
    </code>
    clause. For
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    , the same
    <code class="literal">
     ON
    </code>
    syntax specifies which privileges to
          remove.
   </p>
   <p>
    For the global, database, table, and routine levels,
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT ALL
     </code>
    </a>
    assigns only the privileges that exist at the level you are
          granting. For example,
    <code class="literal">
     GRANT ALL ON
     <em class="replaceable">
      <code>
       db_name
      </code>
     </em>
     .*
    </code>
    is a
          database-level statement, so it does not grant any global-only
          privileges such as
    <a class="link" href="privileges-provided.html#priv_file">
     <code class="literal">
      FILE
     </code>
    </a>
    .
          Granting
    <a class="link" href="privileges-provided.html#priv_all">
     <code class="literal">
      ALL
     </code>
    </a>
    does not assign
          the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    or
    <a class="link" href="privileges-provided.html#priv_proxy">
     <code class="literal">
      PROXY
     </code>
    </a>
    privilege.
   </p>
   <p>
    The
    <em class="replaceable">
     <code>
      object_type
     </code>
    </em>
    clause, if present,
          should be specified as
    <code class="literal">
     TABLE
    </code>
    ,
    <code class="literal">
     FUNCTION
    </code>
    , or
    <code class="literal">
     PROCEDURE
    </code>
    when the following object is a table, a stored function, or a
          stored procedure.
   </p>
   <p>
    The privileges that a user holds for a database, table,
          column, or routine are formed additively as the logical
    <a class="link" href="logical-operators.html#operator_or">
     <code class="literal">
      OR
     </code>
    </a>
    of the account privileges at
          each of the privilege levels, including the global level. It
          is not possible to deny a privilege granted at a higher level
          by absence of that privilege at a lower level. For example,
          this statement grants the
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    and
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    privileges globally:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa48308398"><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">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The globally granted privileges apply to all databases,
          tables, and columns, even though not granted at any of those
          lower levels.
   </p>
   <p>
    It is possible to deny explicitly a privilege granted at the
          global level by revoking it for particular databases, if the
    <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
     <code class="literal">
      partial_revokes
     </code>
    </a>
    system
          variable is enabled:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa9989540"><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">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u1<span class="token punctuation">;</span>
<span class="token keyword">REVOKE</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> db1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> u1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The result of the preceding statements is that
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    applies globally to all
          tables, whereas
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    and
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    apply globally except to
          tables in
    <code class="literal">
     db1
    </code>
    . Account access to
    <code class="literal">
     db1
    </code>
    is read only.
   </p>
   <p>
    Details of the privilege-checking procedure are presented in
    <a class="xref" href="request-access.html" title="8.2.7 Access Control, Stage 2: Request Verification">
     Section 8.2.7, “Access Control, Stage 2: Request Verification”
    </a>
    .
   </p>
   <p>
    If you are using table, column, or routine privileges for even
          one user, the server examines table, column, and routine
          privileges for all users and this slows down MySQL a bit.
          Similarly, if you limit the number of queries, updates, or
          connections for any users, the server must monitor these
          values.
   </p>
   <p>
    MySQL enables you to grant privileges on databases or tables
          that do not exist. For tables, the privileges to be granted
          must include the
    <a class="link" href="privileges-provided.html#priv_create">
     <code class="literal">
      CREATE
     </code>
    </a>
    privilege.
    <span class="emphasis">
     <em>
      This behavior is by design
     </em>
    </span>
    ,
          and is intended to enable the database administrator to
          prepare user accounts and privileges for databases or tables
          that are to be created at a later time.
   </p>
   <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Important
    </div>
    <p>
     <span class="emphasis">
      <em>
       MySQL does not automatically revoke any privileges
            when you drop a database or table
      </em>
     </span>
     . However, if
            you drop a routine, any routine-level privileges granted for
            that routine are revoked.
    </p>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-global-privileges">
       </a>
       Global Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    Global privileges are administrative or apply to all databases
          on a given server. To assign global privileges, use
    <code class="literal">
     ON *.*
    </code>
    syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa82919249"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_create-tablespace">
     <code class="literal">
      CREATE TABLESPACE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_file">
     <code class="literal">
      FILE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_process">
     <code class="literal">
      PROCESS
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_reload">
     <code class="literal">
      RELOAD
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_replication-client">
     <code class="literal">
      REPLICATION CLIENT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_replication-slave">
     <code class="literal">
      REPLICATION SLAVE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_show-databases">
     <code class="literal">
      SHOW DATABASES
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_shutdown">
     <code class="literal">
      SHUTDOWN
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_super">
     <code class="literal">
      SUPER
     </code>
    </a>
    static privileges are
          administrative and can only be granted globally.
   </p>
   <p>
    Dynamic privileges are all global and can only be granted
          globally.
   </p>
   <p>
    Other privileges can be granted globally or at more specific
          levels.
   </p>
   <p>
    The effect of
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    granted at the global level differs for static and dynamic
          privileges:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="link" href="privileges-provided.html#priv_grant-option">
        <code class="literal">
         GRANT OPTION
        </code>
       </a>
       granted for
              any static global privilege applies to all static global
              privileges.
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="link" href="privileges-provided.html#priv_grant-option">
        <code class="literal">
         GRANT OPTION
        </code>
       </a>
       granted for
              any dynamic privilege applies only to that dynamic
              privilege.
      </p>
     </li>
    </ul>
   </div>
   <p>
    <code class="literal">
     GRANT ALL
    </code>
    at the global level grants all
          static global privileges and all currently registered dynamic
          privileges. A dynamic privilege registered subsequent to
          execution of the
    <code class="literal">
     GRANT
    </code>
    statement is not
          granted retroactively to any account.
   </p>
   <p>
    MySQL stores global privileges in the
    <code class="literal">
     mysql.user
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-database-privileges">
       </a>
       Database Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    Database privileges apply to all objects in a given database.
          To assign database-level privileges, use
    <code class="literal">
     ON
     <em class="replaceable">
      <code>
       db_name
      </code>
     </em>
     .*
    </code>
    syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa19140375"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you use
    <code class="literal">
     ON *
    </code>
    syntax (rather than
    <code class="literal">
     ON *.*
    </code>
    ), privileges are assigned at the
          database level for the default database. An error occurs if
          there is no default database.
   </p>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_create">
     <code class="literal">
      CREATE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_drop">
     <code class="literal">
      DROP
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_event">
     <code class="literal">
      EVENT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_lock-tables">
     <code class="literal">
      LOCK TABLES
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_references">
     <code class="literal">
      REFERENCES
     </code>
    </a>
    privileges can be
          specified at the database level. Table or routine privileges
          also can be specified at the database level, in which case
          they apply to all tables or routines in the database.
   </p>
   <p>
    MySQL stores database privileges in the
    <code class="literal">
     mysql.db
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-table-privileges">
       </a>
       Table Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    Table privileges apply to all columns in a given table. To
          assign table-level privileges, use
    <code class="literal">
     ON
     <em class="replaceable">
      <code>
       db_name.tbl_name
      </code>
     </em>
    </code>
    syntax:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa16420744"><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">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span>mytbl <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span>mytbl <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If you specify
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    rather than
    <em class="replaceable">
     <code>
      db_name.tbl_name
     </code>
    </em>
    , the statement
          applies to
    <em class="replaceable">
     <code>
      tbl_name
     </code>
    </em>
    in the default
          database. An error occurs if there is no default database.
   </p>
   <p>
    The permissible
    <em class="replaceable">
     <code>
      priv_type
     </code>
    </em>
    values at
          the table level are
    <a class="link" href="privileges-provided.html#priv_alter">
     <code class="literal">
      ALTER
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_create-view">
     <code class="literal">
      CREATE VIEW
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_create">
     <code class="literal">
      CREATE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_delete">
     <code class="literal">
      DELETE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_drop">
     <code class="literal">
      DROP
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_index">
     <code class="literal">
      INDEX
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_references">
     <code class="literal">
      REFERENCES
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_show-view">
     <code class="literal">
      SHOW VIEW
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_trigger">
     <code class="literal">
      TRIGGER
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    .
   </p>
   <p>
    Table-level privileges apply to base tables and views. They do
          not apply to tables created with
    <a class="link" href="create-temporary-table.html" title="15.1.20.2 CREATE TEMPORARY TABLE Statement">
     <code class="literal">
      CREATE
          TEMPORARY TABLE
     </code>
    </a>
    , even if the table names match. For
          information about
    <code class="literal">
     TEMPORARY
    </code>
    table
          privileges, see
    <a class="xref" href="create-temporary-table.html" title="15.1.20.2 CREATE TEMPORARY TABLE Statement">
     Section 15.1.20.2, “CREATE TEMPORARY TABLE Statement”
    </a>
    .
   </p>
   <a class="indexterm" name="idm46045173390208">
   </a>
   <a class="indexterm" name="idm46045173388720">
   </a>
   <p>
    MySQL stores table privileges in the
    <code class="literal">
     mysql.tables_priv
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-column-privileges">
       </a>
       Column Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    Column privileges apply to single columns in a given table.
          Each privilege to be granted at the column level must be
          followed by the column or columns, enclosed within
          parentheses.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa39804227"><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">GRANT</span> <span class="token keyword">SELECT</span> <span class="token punctuation">(</span>col1<span class="token punctuation">)</span><span class="token punctuation">,</span> <span class="token function">INSERT</span> <span class="token punctuation">(</span>col1<span class="token punctuation">,</span> col2<span class="token punctuation">)</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span>mytbl <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The permissible
    <em class="replaceable">
     <code>
      priv_type
     </code>
    </em>
    values
          for a column (that is, when you use a
    <em class="replaceable">
     <code>
      column_list
     </code>
    </em>
    clause) are
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_references">
     <code class="literal">
      REFERENCES
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    .
   </p>
   <p>
    MySQL stores column privileges in the
    <code class="literal">
     mysql.columns_priv
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-routine-privileges">
       </a>
       Stored Routine Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_alter-routine">
     <code class="literal">
      ALTER ROUTINE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_create-routine">
     <code class="literal">
      CREATE ROUTINE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_execute">
     <code class="literal">
      EXECUTE
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    privileges apply
          to stored routines (procedures and functions). They can be
          granted at the global and database levels. Except for
    <a class="link" href="privileges-provided.html#priv_create-routine">
     <code class="literal">
      CREATE ROUTINE
     </code>
    </a>
    , these
          privileges can be granted at the routine level for individual
          routines.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42214503"><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">GRANT</span> <span class="token keyword">CREATE</span> <span class="token keyword">ROUTINE</span> <span class="token keyword">ON</span> mydb<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">EXECUTE</span> <span class="token keyword">ON</span> <span class="token keyword">PROCEDURE</span> mydb<span class="token punctuation">.</span>myproc <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The permissible
    <em class="replaceable">
     <code>
      priv_type
     </code>
    </em>
    values at
          the routine level are
    <a class="link" href="privileges-provided.html#priv_alter-routine">
     <code class="literal">
      ALTER
          ROUTINE
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_execute">
     <code class="literal">
      EXECUTE
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    .
    <a class="link" href="privileges-provided.html#priv_create-routine">
     <code class="literal">
      CREATE ROUTINE
     </code>
    </a>
    is not a
          routine-level privilege because you must have the privilege at
          the global or database level to create a routine in the first
          place.
   </p>
   <p>
    MySQL stores routine-level privileges in the
    <code class="literal">
     mysql.procs_priv
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-proxy-privileges">
       </a>
       Proxy User Privileges
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_proxy">
     <code class="literal">
      PROXY
     </code>
    </a>
    privilege enables one
          user to be a proxy for another. The proxy user impersonates or
          takes the identity of the proxied user; that is, it assumes
          the privileges of the proxied user.
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa69395086"><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">GRANT</span> <span class="token keyword">PROXY</span> <span class="token keyword">ON</span> <span class="token string">'localuser'</span>@<span class="token string">'localhost'</span> <span class="token keyword">TO</span> <span class="token string">'externaluser'</span>@<span class="token string">'somehost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    When
    <a class="link" href="privileges-provided.html#priv_proxy">
     <code class="literal">
      PROXY
     </code>
    </a>
    is granted, it must
          be the only privilege named in the
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement, and the only
          permitted
    <code class="literal">
     WITH
    </code>
    option is
    <code class="literal">
     WITH
          GRANT OPTION
    </code>
    .
   </p>
   <p>
    Proxying requires that the proxy user authenticate through a
          plugin that returns the name of the proxied user to the server
          when the proxy user connects, and that the proxy user have the
    <code class="literal">
     PROXY
    </code>
    privilege for the proxied user. For
          details and examples, see
    <a class="xref" href="proxy-users.html" title="8.2.19 Proxy Users">
     Section 8.2.19, “Proxy Users”
    </a>
    .
   </p>
   <p>
    MySQL stores proxy privileges in the
    <code class="literal">
     mysql.proxies_priv
    </code>
    system table.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-roles">
       </a>
       Granting Roles
      </h5>
     </div>
    </div>
   </div>
   <p>
    <code class="literal">
     GRANT
    </code>
    syntax without an
    <code class="literal">
     ON
    </code>
    clause grants roles rather than
          individual privileges. A role is a named collection of
          privileges; see
    <a class="xref" href="roles.html" title="8.2.10 Using Roles">
     Section 8.2.10, “Using Roles”
    </a>
    . For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92253735"><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">GRANT</span> <span class="token string">'role1'</span><span class="token punctuation">,</span> <span class="token string">'role2'</span> <span class="token keyword">TO</span> <span class="token string">'user1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token string">'user2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Each role to be granted must exist, as well as each user
          account or role to which it is to be granted. Roles cannot be
          granted to anonymous users.
   </p>
   <p>
    Granting a role does not automatically cause the role to be
          active. For information about role activation and
          inactivation, see
    <a class="xref" href="roles.html#roles-activating" title="Activating Roles">
     Activating Roles
    </a>
    .
   </p>
   <p>
    These privileges are required to grant roles:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If you have the
       <a class="link" href="privileges-provided.html#priv_role-admin">
        <code class="literal">
         ROLE_ADMIN
        </code>
       </a>
       privilege (or the deprecated
       <a class="link" href="privileges-provided.html#priv_super">
        <code class="literal">
         SUPER
        </code>
       </a>
       privilege), you can
              grant or revoke any role to users or roles.
      </p>
     </li>
     <li class="listitem">
      <p>
       If you were granted a role with a
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement that
              includes the
       <code class="literal">
        WITH ADMIN OPTION
       </code>
       clause,
              you become able to grant that role to other users or
              roles, or revoke it from other users or roles, as long as
              the role is active at such time as you subsequently grant
              or revoke it. This includes the ability to use
       <code class="literal">
        WITH ADMIN OPTION
       </code>
       itself.
      </p>
     </li>
     <li class="listitem">
      <p>
       To grant a role that has the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, you
              must have the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege.
      </p>
     </li>
    </ul>
   </div>
   <p>
    It is possible to create circular references with
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    . For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63968337"><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">'u1'</span><span class="token punctuation">,</span> <span class="token string">'u2'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">ROLE</span> <span class="token string">'r1'</span><span class="token punctuation">,</span> <span class="token string">'r2'</span><span class="token punctuation">;</span>

<span class="token keyword">GRANT</span> <span class="token string">'u1'</span> <span class="token keyword">TO</span> <span class="token string">'u1'</span><span class="token punctuation">;</span>   <span class="token comment" spellcheck="true">-- simple loop: u1 =&gt; u1</span>
<span class="token keyword">GRANT</span> <span class="token string">'r1'</span> <span class="token keyword">TO</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>   <span class="token comment" spellcheck="true">-- simple loop: r1 =&gt; r1</span>

<span class="token keyword">GRANT</span> <span class="token string">'r2'</span> <span class="token keyword">TO</span> <span class="token string">'u2'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'u2'</span> <span class="token keyword">TO</span> <span class="token string">'r2'</span><span class="token punctuation">;</span>   <span class="token comment" spellcheck="true">-- mixed user/role loop: u2 =&gt; r2 =&gt; u2</span></code></pre>
   </div>
   <p>
    Circular grant references are permitted but add no new
          privileges or roles to the grantee because a user or role
          already has its privileges and roles.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-as">
       </a>
       The
       <code class="literal">
        AS
       </code>
       Clause and Privilege Restrictions
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045173324400">
   </a>
   <a class="indexterm" name="idm46045173322912">
   </a>
   <p>
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    can specify additional
          information about the privilege context to use for statement
          execution by using an
    <code class="literal">
     AS
     <em class="replaceable">
      <code>
       user
      </code>
     </em>
     [WITH ROLE]
    </code>
    clause.
          This syntax is visible at the SQL level, although its primary
          purpose is to enable uniform replication across all nodes of
          grantor privilege restrictions imposed by partial revokes, by
          causing those restrictions to appear in the binary log. For
          information about partial revokes, see
    <a class="xref" href="partial-revokes.html" title="8.2.12 Privilege Restriction Using Partial Revokes">
     Section 8.2.12, “Privilege Restriction Using Partial Revokes”
    </a>
    .
   </p>
   <p>
    When the
    <code class="literal">
     AS
     <em class="replaceable">
      <code>
       user
      </code>
     </em>
    </code>
    clause is specified, statement execution takes into account
          any privilege restrictions associated with the named user,
          including all roles specified by
    <code class="literal">
     WITH ROLE
    </code>
    ,
          if present. The result is that the privileges actually granted
          by the statement may be reduced relative to those specified.
   </p>
   <p>
    These conditions apply to the
    <code class="literal">
     AS
     <em class="replaceable">
      <code>
       user
      </code>
     </em>
    </code>
    clause:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        AS
       </code>
       has an effect only when the named
       <em class="replaceable">
        <code>
         user
        </code>
       </em>
       has privilege restrictions
              (which implies that the
       <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
        <code class="literal">
         partial_revokes
        </code>
       </a>
       system
              variable is enabled).
      </p>
     </li>
     <li class="listitem">
      <p>
       If
       <code class="literal">
        WITH ROLE
       </code>
       is given, all roles named
              must be granted to the named
       <em class="replaceable">
        <code>
         user
        </code>
       </em>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The named
       <em class="replaceable">
        <code>
         user
        </code>
       </em>
       should be a
              MySQL account specified as
       <code class="literal">
        '
        <em class="replaceable">
         <code>
          user_name
         </code>
        </em>
        '@'
        <em class="replaceable">
         <code>
          host_name
         </code>
        </em>
        '
       </code>
       ,
       <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>
       . The current
              user may be named together with
       <code class="literal">
        WITH
              ROLE
       </code>
       for the case that the executing user wants
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       to execute with a set
              of roles applied that may differ from the roles active
              within the current session.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        AS
       </code>
       cannot be used to gain privileges
              not possessed by the user who executes the
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement. The
              executing user must have at least the privileges to be
              granted, but the
       <code class="literal">
        AS
       </code>
       clause can only
              restrict the privileges granted, not escalate them.
      </p>
     </li>
     <li class="listitem">
      <p>
       With respect to the privileges to be granted,
       <code class="literal">
        AS
       </code>
       cannot specify a user/role
              combination that has more privileges (fewer restrictions)
              than the user who executes the
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement. The
       <code class="literal">
        AS
       </code>
       user/role combination is permitted
              to have more privileges than the executing user, but only
              if the statement does not grant those additional
              privileges.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        AS
       </code>
       is supported only for granting
              global privileges (
       <code class="literal">
        ON *.*
       </code>
       ).
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        AS
       </code>
       is not supported for
       <a class="link" href="privileges-provided.html#priv_proxy">
        <code class="literal">
         PROXY
        </code>
       </a>
       grants.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The following example illustrates the effect of the
    <code class="literal">
     AS
    </code>
    clause. Create a user
    <code class="literal">
     u1
    </code>
    that has some global privileges, as well
          as restrictions on those privileges:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75268111"><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> u1<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span><span class="token punctuation">,</span> <span class="token keyword">DELETE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u1<span class="token punctuation">;</span>
<span class="token keyword">REVOKE</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> schema1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> u1<span class="token punctuation">;</span>
<span class="token keyword">REVOKE</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> schema2<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> u1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Also create a role
    <code class="literal">
     r1
    </code>
    that lifts some of
          the privilege restrictions and grant the role to
    <code class="literal">
     u1
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60383714"><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">ROLE</span> r1<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">INSERT</span> <span class="token keyword">ON</span> schema1<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> r1<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> schema2<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> r1<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> r1 <span class="token keyword">TO</span> u1<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now, using an account that has no privilege restrictions of
          its own, grant to multiple users the same set of global
          privileges, but each with different restrictions imposed by
          the
    <code class="literal">
     AS
    </code>
    clause, and check which privileges
          are actually granted.
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement here
              has no
       <code class="literal">
        AS
       </code>
       clause, so the privileges
              granted are exactly those specified:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa64961744"><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">CREATE</span> <span class="token keyword">USER</span> u2<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u2<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> u2<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 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 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> Grants for u2@%                                 <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 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 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> GRANT SELECT, INSERT, UPDATE ON *.* TO `u2`@`%` <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 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 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>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement here
              has an
       <code class="literal">
        AS
       </code>
       clause, so the privileges
              granted are those specified but with the restrictions from
       <code class="literal">
        u1
       </code>
       applied:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa70930381"><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">CREATE</span> <span class="token keyword">USER</span> u3<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u3 <span class="token keyword">AS</span> u1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> u3<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 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 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> Grants for u3@%                                    <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 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 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> GRANT SELECT, INSERT, UPDATE ON *.* TO `u3`@`%`    <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> REVOKE INSERT, UPDATE ON `schema1`.* FROM `u3`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> REVOKE SELECT ON `schema2`.* FROM `u3`@`%`         <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 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 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>
       As mentioned previously, the
       <code class="literal">
        AS
       </code>
       clause
              can only add privilege restrictions; it cannot escalate
              privileges. Thus, although
       <code class="literal">
        u1
       </code>
       has the
       <a class="link" href="privileges-provided.html#priv_delete">
        <code class="literal">
         DELETE
        </code>
       </a>
       privilege, that is
              not included in the privileges granted because the
              statement does not specify granting
       <a class="link" href="privileges-provided.html#priv_delete">
        <code class="literal">
         DELETE
        </code>
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <code class="literal">
        AS
       </code>
       clause for the
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement here makes
              the role
       <code class="literal">
        r1
       </code>
       active for
       <code class="literal">
        u1
       </code>
       . That role lifts some of the
              restrictions on
       <code class="literal">
        u1
       </code>
       . Consequently, the
              privileges granted have some restrictions, but not so many
              as for the previous
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       statement:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51770087"><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">CREATE</span> <span class="token keyword">USER</span> u4<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span><span class="token punctuation">,</span> <span class="token keyword">INSERT</span><span class="token punctuation">,</span> <span class="token keyword">UPDATE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> u4 <span class="token keyword">AS</span> u1 <span class="token keyword">WITH</span> <span class="token keyword">ROLE</span> r1<span class="token punctuation">;</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> u4<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 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 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> Grants for u4@%                                 <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 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 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> GRANT SELECT, INSERT, UPDATE ON *.* TO `u4`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> REVOKE UPDATE ON `schema1`.* FROM `u4`@`%`      <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 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 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>
     </li>
    </ul>
   </div>
   <p>
    If a
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statement includes
          an
    <code class="literal">
     AS
     <em class="replaceable">
      <code>
       user
      </code>
     </em>
    </code>
    clause, privilege restrictions on the user who executes the
          statement are ignored (rather than applied as they would be in
          the absence of an
    <code class="literal">
     AS
    </code>
    clause).
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-other-characteristics">
       </a>
       Other Account Characteristics
      </h5>
     </div>
    </div>
   </div>
   <p>
    The optional
    <code class="literal">
     WITH
    </code>
    clause is used to enable
          a user to grant privileges to other users. The
    <code class="literal">
     WITH
          GRANT OPTION
    </code>
    clause gives the user the ability to
          give to other users any privileges the user has at the
          specified privilege level.
   </p>
   <p>
    To grant the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    privilege to an account without otherwise changing its
          privileges, do this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa93834064"><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">GRANT</span> <span class="token keyword">USAGE</span> <span class="token keyword">ON</span> <span class="token operator">*</span><span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'someuser'</span>@<span class="token string">'somehost'</span> <span class="token keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Be careful to whom you give the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT
          OPTION
     </code>
    </a>
    privilege because two users with different
          privileges may be able to combine privileges!
   </p>
   <p>
    You cannot grant another user a privilege which you yourself
          do not have; the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    privilege enables you to assign only those privileges which
          you yourself possess.
   </p>
   <p>
    Be aware that when you grant a user the
    <a class="link" href="privileges-provided.html#priv_grant-option">
     <code class="literal">
      GRANT OPTION
     </code>
    </a>
    privilege at a
          particular privilege level, any privileges the user possesses
          (or may be given in the future) at that level can also be
          granted by that user to other users. Suppose that you grant a
          user the
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    privilege on a
          database. If you then grant the
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    privilege on the
          database and specify
    <code class="literal">
     WITH GRANT OPTION
    </code>
    ,
          that user can give to other users not only the
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    privilege, but also
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    . If you then grant the
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    privilege to the user on
          the database, the user can grant
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_select">
     <code class="literal">
      SELECT
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    .
   </p>
   <p>
    For a nonadministrative user, you should not grant the
    <a class="link" href="privileges-provided.html#priv_alter">
     <code class="literal">
      ALTER
     </code>
    </a>
    privilege globally or for
          the
    <code class="literal">
     mysql
    </code>
    system schema. If you do that,
          the user can try to subvert the privilege system by renaming
          tables!
   </p>
   <p>
    For additional information about security risks associated
          with particular privileges, see
    <a class="xref" href="privileges-provided.html" title="8.2.2 Privileges Provided by MySQL">
     Section 8.2.2, “Privileges Provided by MySQL”
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="grant-mysql-vs-standard-sql">
       </a>
       MySQL and Standard SQL Versions of GRANT
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045173222000">
   </a>
   <p>
    The biggest differences between the MySQL and standard SQL
          versions of
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    are:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       MySQL associates privileges with the combination of a host
              name and user name and not with only a user name.
      </p>
     </li>
     <li class="listitem">
      <p>
       Standard SQL does not have global or database-level
              privileges, nor does it support all the privilege types
              that MySQL supports.
      </p>
     </li>
     <li class="listitem">
      <p>
       MySQL does not support the standard SQL
       <code class="literal">
        UNDER
       </code>
       privilege.
      </p>
     </li>
     <li class="listitem">
      <p>
       Standard SQL privileges are structured in a hierarchical
              manner. If you remove a user, all privileges the user has
              been granted are revoked. This is also true in MySQL if
              you use
       <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
        <code class="literal">
         DROP USER
        </code>
       </a>
       . See
       <a class="xref" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
        Section 15.7.1.5, “DROP USER Statement”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       In standard SQL, when you drop a table, all privileges for
              the table are revoked. In standard SQL, when you revoke a
              privilege, all privileges that were granted based on that
              privilege are also revoked. In MySQL, privileges can be
              dropped with
       <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
        <code class="literal">
         DROP USER
        </code>
       </a>
       or
       <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
        <code class="literal">
         REVOKE
        </code>
       </a>
       statements.
      </p>
     </li>
     <li class="listitem">
      <p>
       In MySQL, it is possible to have the
       <a class="link" href="privileges-provided.html#priv_insert">
        <code class="literal">
         INSERT
        </code>
       </a>
       privilege for only
              some of the columns in a table. In this case, you can
              still execute
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       statements on the table, provided that you insert values
              only for those columns for which you have the
       <a class="link" href="privileges-provided.html#priv_insert">
        <code class="literal">
         INSERT
        </code>
       </a>
       privilege. The
              omitted columns are set to their implicit default values
              if strict SQL mode is not enabled. In strict mode, the
              statement is rejected if any of the omitted columns have
              no default value. (Standard SQL requires you to have the
       <a class="link" href="privileges-provided.html#priv_insert">
        <code class="literal">
         INSERT
        </code>
       </a>
       privilege on all
              columns.) For information about strict SQL mode and
              implicit default values, see
       <a class="xref" href="sql-mode.html" title="7.1.11 Server SQL Modes">
        Section 7.1.11, “Server SQL Modes”
       </a>
       ,
              and
       <a class="xref" href="data-type-defaults.html" title="13.6 Data Type Default Values">
        Section 13.6, “Data Type Default Values”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
