<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="account-categories">
      </a>
      8.2.11 Account Categories
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045249170624">
  </a>
  <a class="indexterm" name="idm46045249169136">
  </a>
  <a class="indexterm" name="idm46045249167648">
  </a>
  <a class="indexterm" name="idm46045249166576">
  </a>
  <a class="indexterm" name="idm46045249165088">
  </a>
  <p>
   MySQL incorporates the concept of user account categories, based
      on the
   <a class="link" href="privileges-provided.html#priv_system-user">
    <code class="literal">
     SYSTEM_USER
    </code>
   </a>
   privilege.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="account-categories.html#system-user-accounts" title="System and Regular Accounts">
       System and Regular Accounts
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="account-categories.html#system-user-operations" title="Operations Affected by the SYSTEM_USER Privilege">
       Operations Affected by the SYSTEM_USER Privilege
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="account-categories.html#system-user-sessions" title="System and Regular Sessions">
       System and Regular Sessions
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="account-categories.html#protecting-system-accounts" title="Protecting System Accounts Against Manipulation by Regular Accounts">
       Protecting System Accounts Against Manipulation by Regular Accounts
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="system-user-accounts">
       </a>
       System and Regular Accounts
      </h4>
     </div>
    </div>
   </div>
   <p>
    MySQL incorporates the concept of user account categories, with
        system and regular users distinguished according to whether they
        have the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A user with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is a system user.
      </p>
     </li>
     <li class="listitem">
      <p>
       A user without the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is a
            regular user.
      </p>
     </li>
    </ul>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege has an
        effect on the accounts to which a given user can apply its other
        privileges, as well as whether the user is protected from other
        accounts:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A system user can modify both system and regular accounts.
            That is, a user who has the appropriate privileges to
            perform a given operation on regular accounts is enabled by
            possession of
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       to
            also perform the operation on system accounts. A system
            account can be modified only by system users with
            appropriate privileges, not by regular users.
      </p>
     </li>
     <li class="listitem">
      <p>
       A regular user with appropriate privileges can modify
            regular accounts, but not system accounts. A regular account
            can be modified by both system and regular users with
            appropriate privileges.
      </p>
     </li>
    </ul>
   </div>
   <p>
    If a user has the appropriate privileges to perform a given
        operation on regular accounts,
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    enables the user to
        also perform the operation on system accounts.
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    does not imply any
        other privilege, so the ability to perform a given account
        operation remains predicated on possession of any other required
        privileges. For example, if a user can grant 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_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    privileges to regular
        accounts, then with
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    the user can also grant
    <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>
    to system accounts.
   </p>
   <p>
    The distinction between system and regular accounts enables
        better control over certain account administration issues by
        protecting accounts that have the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege from
        accounts that do not have the privilege. For example, the
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    privilege enables not
        only creation of new accounts, but modification and removal of
        existing accounts. Without the system user concept, a user who
        has the
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    privilege can
        modify or drop any existing account, including the
    <code class="literal">
     root
    </code>
    account. The concept of system user
        enables restricting modifications to the
    <code class="literal">
     root
    </code>
    account (itself a system account) so they can be made only by
        system users. Regular users with the
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE
        USER
     </code>
    </a>
    privilege can still modify or drop existing
        accounts, but only regular accounts.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="system-user-operations">
       </a>
       Operations Affected by the SYSTEM_USER Privilege
      </h4>
     </div>
    </div>
   </div>
   <p>
    The
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege affects
        these operations:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Account manipulation.
      </p>
      <p>
       Account manipulation includes creating and dropping
            accounts, granting and revoking privileges, changing account
            authentication characteristics such as credentials or
            authentication plugin, and changing other account
            characteristics such as password expiration policy.
      </p>
      <p>
       The
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is
            required to manipulate system accounts using
            account-management statements such as
       <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
        <code class="literal">
         CREATE USER
        </code>
       </a>
       and
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       . To prevent an account
            from modifying system accounts this way, make it a regular
            account by not granting it the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege.
            (However, to fully protect system accounts against regular
            accounts, you must also withhold modification privileges for
            the
       <code class="literal">
        mysql
       </code>
       system schema from regular
            accounts. See
       <a class="xref" href="account-categories.html#protecting-system-accounts" title="Protecting System Accounts Against Manipulation by Regular Accounts">
        Protecting System Accounts Against Manipulation by Regular Accounts
       </a>
       .)
      </p>
     </li>
     <li class="listitem">
      <p>
       Killing current sessions and statements executing within
            them.
      </p>
      <p>
       To kill a session or statement that is executing with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, your
            own session must have the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, in
            addition to any other required privilege
            (
       <a class="link" href="privileges-provided.html#priv_connection-admin">
        <code class="literal">
         CONNECTION_ADMIN
        </code>
       </a>
       or the
            deprecated
       <a class="link" href="privileges-provided.html#priv_super">
        <code class="literal">
         SUPER
        </code>
       </a>
       privilege).
      </p>
      <p>
       If the user that puts a server in offline mode does not have
            the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege,
            connected client users who have the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege are
            also not disconnected. However, these users cannot initiate
            new connections to the server while it is in offline mode,
            unless they have the
       <a class="link" href="privileges-provided.html#priv_connection-admin">
        <code class="literal">
         CONNECTION_ADMIN
        </code>
       </a>
       or
       <a class="link" href="privileges-provided.html#priv_super">
        <code class="literal">
         SUPER
        </code>
       </a>
       privilege as well. It
            is only their existing connection that is not terminated,
            because the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is required to do that.
      </p>
     </li>
     <li class="listitem">
      <p>
       Setting the
       <code class="literal">
        DEFINER
       </code>
       attribute for stored
            objects.
      </p>
      <p>
       To set the
       <code class="literal">
        DEFINER
       </code>
       attribute for a stored
            object to an account 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, in addition to any other required privilege.
      </p>
     </li>
     <li class="listitem">
      <p>
       Specifying mandatory roles.
      </p>
      <p>
       A role that has the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege cannot
            be listed in the value of the
       <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
        <code class="literal">
         mandatory_roles
        </code>
       </a>
       system
            variable.
      </p>
     </li>
     <li class="listitem">
      <p>
       Overriding
       <span class="quote">
        “
        <span class="quote">
         abort
        </span>
        ”
       </span>
       items in MySQL Enterprise
            Audit’s audit log filter.
      </p>
      <p>
       Accounts with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege are automatically assigned the
       <a class="link" href="privileges-provided.html#priv_audit-abort-exempt">
        <code class="literal">
         AUDIT_ABORT_EXEMPT
        </code>
       </a>
       privilege,
            so that queries from the account are always executed even if
            an
       <span class="quote">
        “
        <span class="quote">
         abort
        </span>
        ”
       </span>
       item in the audit log filter would
            block them. Accounts with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege can
            therefore be used to regain access to a system following an
            audit misconfiguration. See
       <a class="xref" href="audit-log.html" title="8.4.5 MySQL Enterprise Audit">
        Section 8.4.5, “MySQL Enterprise Audit”
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="system-user-sessions">
       </a>
       System and Regular Sessions
      </h4>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045249084816">
   </a>
   <a class="indexterm" name="idm46045249083360">
   </a>
   <a class="indexterm" name="idm46045249081872">
   </a>
   <p>
    Sessions executing within the server are distinguished as system
        or regular sessions, similar to the distinction between system
        and regular users:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       A session that possesses the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is a
            system session.
      </p>
     </li>
     <li class="listitem">
      <p>
       A session that does not possess the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is a
            regular session.
      </p>
     </li>
    </ul>
   </div>
   <p>
    A regular session is able to perform only operations permitted
        to regular users. A system session is additionally able to
        perform operations permitted only to system users.
   </p>
   <p>
    The privileges possessed by a session are those granted directly
        to its underlying account, plus those granted to all roles
        currently active within the session. Thus, a session may be a
        system session because its account has been granted the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege directly,
        or because the session has activated a role that has the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege. Roles
        granted to an account that are not active within the session do
        not affect session privileges.
   </p>
   <p>
    Because activating and deactivating roles can change the
        privileges possessed by sessions, a session may change from a
        regular session to a system session or vice versa. If a session
        activates or deactivates a role that has the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege, the
        appropriate change between regular and system session takes
        place immediately, for that session only:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If a regular session activates a role with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, the
            session becomes a system session.
      </p>
     </li>
     <li class="listitem">
      <p>
       If a system session deactivates a role with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, the
            session becomes a regular session, unless some other role
            with the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege remains active.
      </p>
     </li>
    </ul>
   </div>
   <p>
    These operations have no effect on existing sessions:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege
            is granted to or revoked from an account, existing sessions
            for the account do not change between regular and system
            sessions. The grant or revoke operation affects only
            sessions for subsequent connections by the account.
      </p>
     </li>
     <li class="listitem">
      <p>
       Statements executed by a stored object invoked within a
            session execute with the system or regular status of the
            parent session, even if the object
       <code class="literal">
        DEFINER
       </code>
       attribute names a system account.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Because role activation affects only sessions and not accounts,
        granting a role that has the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege to a
        regular account does not protect that account against regular
        users. The role protects only sessions for the account in which
        the role has been activated, and protects the session only
        against being killed by regular sessions.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="protecting-system-accounts">
       </a>
       Protecting System Accounts Against Manipulation by Regular Accounts
      </h4>
     </div>
    </div>
   </div>
   <p>
    Account manipulation includes creating and dropping accounts,
        granting and revoking privileges, changing account
        authentication characteristics such as credentials or
        authentication plugin, and changing other account
        characteristics such as password expiration policy.
   </p>
   <p>
    Account manipulation can be done two ways:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       By using account-management statements such as
       <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
        <code class="literal">
         CREATE USER
        </code>
       </a>
       and
       <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
        <code class="literal">
         GRANT
        </code>
       </a>
       . This is the preferred
            method.
      </p>
     </li>
     <li class="listitem">
      <p>
       By direct grant-table modification using statements such as
       <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
        <code class="literal">
         INSERT
        </code>
       </a>
       and
       <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
        <code class="literal">
         UPDATE
        </code>
       </a>
       . This method is
            discouraged but possible for users with the appropriate
            privileges on the
       <code class="literal">
        mysql
       </code>
       system schema
            that contains the grant tables.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To fully protect system accounts against modification by a given
        account, make it a regular account and do not grant it
        modification privileges for the
    <code class="literal">
     mysql
    </code>
    schema:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege is
            required to manipulate system accounts using
            account-management statements. To prevent an account from
            modifying system accounts this way, make it a regular
            account by not granting
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       to it. This
            includes not granting
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       to any roles
            granted to the account.
      </p>
     </li>
     <li class="listitem">
      <p>
       Privileges for the
       <code class="literal">
        mysql
       </code>
       schema enable
            manipulation of system accounts through direct modification
            of the grant tables, even if the modifying account is a
            regular account. To restrict unauthorized direct
            modification of system accounts by a regular account, do not
            grant modification privileges for the
       <code class="literal">
        mysql
       </code>
       schema to the account (or any roles
            granted to the account). If a regular account must have
            global privileges that apply to all schemas,
       <code class="literal">
        mysql
       </code>
       schema modifications can be
            prevented using privilege restrictions imposed using 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>
     </li>
    </ul>
   </div>
   <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
    <div class="admon-title">
     Note
    </div>
    <p>
     Unlike withholding the
     <a class="link" href="privileges-provided.html#priv_system-user">
      <code class="literal">
       SYSTEM_USER
      </code>
     </a>
     privilege, which
          prevents an account from modifying system accounts but not
          regular accounts, withholding
     <code class="literal">
      mysql
     </code>
     schema
          privileges prevents an account from modifying system accounts
          as well as regular accounts. This should not be an issue
          because, as mentioned, direct grant-table modification is
          discouraged.
    </p>
   </div>
   <p>
    Suppose that you want to create a user
    <code class="literal">
     u1
    </code>
    who
        has all privileges on all schemas, except that
    <code class="literal">
     u1
    </code>
    should be a regular user without the
        ability to modify system accounts. Assuming that the
    <a class="link" href="server-system-variables.html#sysvar_partial_revokes">
     <code class="literal">
      partial_revokes
     </code>
    </a>
    system variable
        is enabled, configure
    <code class="literal">
     u1
    </code>
    as follows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa80402029"><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 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> <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 keyword">WITH</span> <span class="token keyword">GRANT</span> <span class="token keyword">OPTION</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- GRANT ALL includes SYSTEM_USER, so at this point</span>
<span class="token comment" spellcheck="true">-- u1 can manipulate system or regular accounts</span>

<span class="token keyword">REVOKE</span> SYSTEM_USER <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">FROM</span> u1<span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Revoking SYSTEM_USER makes u1 a regular user;</span>
<span class="token comment" spellcheck="true">-- now u1 can use account-management statements</span>
<span class="token comment" spellcheck="true">-- to manipulate only regular accounts</span>

<span class="token keyword">REVOKE</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> mysql<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 comment" spellcheck="true">-- This partial revoke prevents u1 from directly</span>
<span class="token comment" spellcheck="true">-- modifying grant tables to manipulate accounts</span></code></pre>
   </div>
   <p>
    To prevent all
    <code class="literal">
     mysql
    </code>
    system schema access by
        an account, revoke all its privileges on the
    <code class="literal">
     mysql
    </code>
    schema, as just shown. It is also
        possible to permit partial
    <code class="literal">
     mysql
    </code>
    schema
        access, such as read-only access. The following example creates
        an account that has
    <code class="literal">
     SELECT
    </code>
    ,
    <code class="literal">
     INSERT
    </code>
    ,
    <code class="literal">
     UPDATE
    </code>
    , and
    <code class="literal">
     DELETE
    </code>
    privileges globally for all schemas,
        but only
    <code class="literal">
     SELECT
    </code>
    for the
    <code class="literal">
     mysql
    </code>
    schema:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa84885593"><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> u2 <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">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> u2<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 punctuation">,</span> <span class="token keyword">DELETE</span> <span class="token keyword">ON</span> mysql<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> u2<span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Another possibility is to revoke all
    <code class="literal">
     mysql
    </code>
    schema privileges but grant access to specific
    <code class="literal">
     mysql
    </code>
    tables or columns. This can be done
        even with a partial revoke on
    <code class="literal">
     mysql
    </code>
    . The
        following statements enable read-only access to
    <code class="literal">
     u1
    </code>
    within the
    <code class="literal">
     mysql
    </code>
    schema, but only for the
    <code class="literal">
     db
    </code>
    table and the
    <code class="literal">
     Host
    </code>
    and
    <code class="literal">
     User
    </code>
    columns of
        the
    <code class="literal">
     user
    </code>
    table:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa39829176"><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> u3 <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> <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 punctuation">;</span>
<span class="token keyword">REVOKE</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> mysql<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> u3<span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> mysql<span class="token punctuation">.</span>db <span class="token keyword">TO</span> u3<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">Host</span><span class="token punctuation">,</span><span class="token keyword">User</span><span class="token punctuation">)</span> <span class="token keyword">ON</span> mysql<span class="token punctuation">.</span><span class="token keyword">user</span> <span class="token keyword">TO</span> u3<span class="token punctuation">;</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
