<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="roles">
      </a>
      8.2.10 Using Roles
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045249426528">
  </a>
  <p>
   A MySQL role is a named collection of privileges. Like user
      accounts, roles can have privileges granted to and revoked from
      them.
  </p>
  <p>
   A user account can be granted roles, which grants to the account
      the privileges associated with each role. This enables assignment
      of sets of privileges to accounts and provides a convenient
      alternative to granting individual privileges, both for
      conceptualizing desired privilege assignments and implementing
      them.
  </p>
  <p>
   The following list summarizes role-management capabilities
      provided by MySQL:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="link" href="create-role.html" title="15.7.1.2 CREATE ROLE Statement">
       <code class="literal">
        CREATE ROLE
       </code>
      </a>
      and
      <a class="link" href="drop-role.html" title="15.7.1.4 DROP ROLE Statement">
       <code class="literal">
        DROP ROLE
       </code>
      </a>
      create and remove
          roles.
     </p>
    </li>
    <li class="listitem">
     <p>
      <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>
      assign privileges to
          revoke privileges from user accounts and roles.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
       <code class="literal">
        SHOW GRANTS
       </code>
      </a>
      displays privilege
          and role assignments for user accounts and roles.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="set-default-role.html" title="15.7.1.9 SET DEFAULT ROLE Statement">
       <code class="literal">
        SET DEFAULT ROLE
       </code>
      </a>
      specifies
          which account roles are active by default.
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
       <code class="literal">
        SET ROLE
       </code>
      </a>
      changes the active
          roles within the current session.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="information-functions.html#function_current-role">
       <code class="literal">
        CURRENT_ROLE()
       </code>
      </a>
      function
          displays the active roles within the current session.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
       <code class="literal">
        mandatory_roles
       </code>
      </a>
      and
      <a class="link" href="server-system-variables.html#sysvar_activate_all_roles_on_login">
       <code class="literal">
        activate_all_roles_on_login
       </code>
      </a>
      system variables enable defining mandatory roles and automatic
          activation of granted roles when users log in to the server.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For descriptions of individual role-manipulation statements
      (including the privileges required to use them), see
   <a class="xref" href="account-management-statements.html" title="15.7.1 Account Management Statements">
    Section 15.7.1, “Account Management Statements”
   </a>
   . The following
      discussion provides examples of role usage. Unless otherwise
      specified, SQL statements shown here should be executed using a
      MySQL account with sufficient administrative privileges, such as
      the
   <code class="literal">
    root
   </code>
   account.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#roles-creating-granting" title="Creating Roles and Granting Privileges to Them">
       Creating Roles and Granting Privileges to Them
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#mandatory-roles" title="Defining Mandatory Roles">
       Defining Mandatory Roles
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#roles-checking" title="Checking Role Privileges">
       Checking Role Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#roles-activating" title="Activating Roles">
       Activating Roles
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#roles-revoking" title="Revoking Roles or Role Privileges">
       Revoking Roles or Role Privileges
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#roles-dropping" title="Dropping Roles">
       Dropping Roles
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="roles.html#role-user-interchangeability" title="User and Role Interchangeability">
       User and Role Interchangeability
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="roles-creating-granting">
       </a>
       Creating Roles and Granting Privileges to Them
      </h4>
     </div>
    </div>
   </div>
   <p>
    Consider this scenario:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       An application uses a database named
       <code class="literal">
        app_db
       </code>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       Associated with the application, there can be accounts for
            developers who create and maintain the application, and for
            users who interact with it.
      </p>
     </li>
     <li class="listitem">
      <p>
       Developers need full access to the database. Some users need
            only read access, others need read/write access.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To avoid granting privileges individually to possibly many user
        accounts, create roles as names for the required privilege sets.
        This makes it easy to grant the required privileges to user
        accounts, by granting the appropriate roles.
   </p>
   <p>
    To create the roles, use the
    <a class="link" href="create-role.html" title="15.7.1.2 CREATE ROLE Statement">
     <code class="literal">
      CREATE
        ROLE
     </code>
    </a>
    statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa16215004"><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> <span class="token string">'app_developer'</span><span class="token punctuation">,</span> <span class="token string">'app_read'</span><span class="token punctuation">,</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Role names are much like user account names and consist of a
        user part and host part in
    <code class="literal">
     '
     <em class="replaceable">
      <code>
       user_name
      </code>
     </em>
     '@'
     <em class="replaceable">
      <code>
       host_name
      </code>
     </em>
     '
    </code>
    format. The host part, if omitted, defaults to
    <code class="literal">
     '%'
    </code>
    . The user and host parts can be unquoted
        unless they contain special characters such as
    <code class="literal">
     -
    </code>
    or
    <code class="literal">
     %
    </code>
    . Unlike account
        names, the user part of role names cannot be blank. For
        additional information, see
    <a class="xref" href="role-names.html" title="8.2.5 Specifying Role Names">
     Section 8.2.5, “Specifying Role Names”
    </a>
    .
   </p>
   <p>
    To assign privileges to the roles, execute
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statements using the same
        syntax as for assigning privileges to user accounts:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17536569"><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> app_db<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'app_developer'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</span> <span class="token keyword">ON</span> app_db<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'app_read'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</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> app_db<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now suppose that initially you require one developer account,
        two user accounts that need read-only access, and one user
        account that needs read/write access. Use
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    to create the
        accounts:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa99590445"><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">'dev1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'dev1pass'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'read_user1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'read_user1pass'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'read_user2'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'read_user2pass'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'rw_user1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'rw_user1pass'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    To assign each user account its required privileges, you could
        use
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    statements of the same
        form as just shown, but that requires enumerating individual
        privileges for each user. Instead, use an alternative
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    syntax that permits
        granting roles rather than privileges:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93950643"><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">'app_developer'</span> <span class="token keyword">TO</span> <span class="token string">'dev1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'app_read'</span> <span class="token keyword">TO</span> <span class="token string">'read_user1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span> <span class="token string">'read_user2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'app_read'</span><span class="token punctuation">,</span> <span class="token string">'app_write'</span> <span class="token keyword">TO</span> <span class="token string">'rw_user1'</span>@<span class="token string">'localhost'</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 for the
    <code class="literal">
     rw_user1
    </code>
    account grants the read and write
        roles, which combine to provide the required read and write
        privileges.
   </p>
   <p>
    The
    <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
     <code class="literal">
      GRANT
     </code>
    </a>
    syntax for granting
        roles to an account differs from the syntax for granting
        privileges: There is an
    <code class="literal">
     ON
    </code>
    clause to assign
        privileges, whereas there is no
    <code class="literal">
     ON
    </code>
    clause to
        assign roles. Because the syntaxes are distinct, you cannot mix
        assigning privileges and roles in the same statement. (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.) Roles cannot be granted to anonymous users.
   </p>
   <p>
    A role when created is locked, has no password, and is assigned
        the default authentication plugin. (These role attributes can be
        changed later with the
    <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
     <code class="literal">
      ALTER USER
     </code>
    </a>
    statement, by users who have the global
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    privilege.)
   </p>
   <p>
    While locked, a role cannot be used to authenticate to the
        server. If unlocked, a role can be used to authenticate. This is
        because roles and users are both authorization identifiers with
        much in common and little to distinguish them. See also
    <a class="xref" href="roles.html#role-user-interchangeability" title="User and Role Interchangeability">
     User and Role Interchangeability
    </a>
    .
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="mandatory-roles">
       </a>
       Defining Mandatory Roles
      </h4>
     </div>
    </div>
   </div>
   <p>
    It is possible to specify roles as mandatory by naming them 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. The server treats a mandatory role as granted to all
        users, so that it need not be granted explicitly to any account.
   </p>
   <p>
    To specify mandatory roles at server startup, define
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    in your server
    <code class="filename">
     my.cnf
    </code>
    file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa57998009"><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-ini"><span class="token selector">[mysqld]</span>
<span class="token constant">mandatory_roles</span><span class="token attr-value"><span class="token punctuation">=</span>'role1,role2@localhost,r3@%.example.com'</span></code></pre>
   </div>
   <p>
    To set and persist
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    at runtime, use
        a statement like this:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa61061251"><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">SET</span> <span class="token keyword">PERSIST</span> mandatory_roles <span class="token operator">=</span> <span class="token string">'role1,role2@localhost,r3@%.example.com'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     <code class="literal">
      SET
        PERSIST
     </code>
    </a>
    sets a value for the running MySQL instance.
        It also saves the value, causing it to carry over to subsequent
        server restarts. To change the value for the running MySQL
        instance without having it carry over to subsequent restarts,
        use the
    <code class="literal">
     GLOBAL
    </code>
    keyword rather than
    <code class="literal">
     PERSIST
    </code>
    . See
    <a class="xref" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
     Section 15.7.6.1, “SET Syntax for Variable Assignment”
    </a>
    .
   </p>
   <p>
    Setting
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    requires the
    <a class="link" href="privileges-provided.html#priv_role-admin">
     <code class="literal">
      ROLE_ADMIN
     </code>
    </a>
    privilege, in addition to the
    <a class="link" href="privileges-provided.html#priv_system-variables-admin">
     <code class="literal">
      SYSTEM_VARIABLES_ADMIN
     </code>
    </a>
    privilege
        (or the deprecated
    <a class="link" href="privileges-provided.html#priv_super">
     <code class="literal">
      SUPER
     </code>
    </a>
    privilege) normally required to set a global system variable.
   </p>
   <p>
    Mandatory roles, like explicitly granted roles, do not take
        effect until activated (see
    <a class="xref" href="roles.html#roles-activating" title="Activating Roles">
     Activating Roles
    </a>
    ).
        At login time, role activation occurs for all granted roles if
        the
    <a class="link" href="server-system-variables.html#sysvar_activate_all_roles_on_login">
     <code class="literal">
      activate_all_roles_on_login
     </code>
    </a>
    system variable is enabled, or for roles that are set as default
        roles otherwise. At runtime,
    <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
     <code class="literal">
      SET
        ROLE
     </code>
    </a>
    activates roles.
   </p>
   <p>
    Roles named in the value of
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    cannot be
        revoked with
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    or dropped
        with
    <a class="link" href="drop-role.html" title="15.7.1.4 DROP ROLE Statement">
     <code class="literal">
      DROP ROLE
     </code>
    </a>
    or
    <a class="link" href="drop-user.html" title="15.7.1.5 DROP USER Statement">
     <code class="literal">
      DROP USER
     </code>
    </a>
    .
   </p>
   <p>
    To prevent sessions from being made system sessions by default,
        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>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
        <code class="literal">
         mandatory_roles
        </code>
       </a>
       is
            assigned a role at startup that has the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, the
            server writes a message to the error log and exits.
      </p>
     </li>
     <li class="listitem">
      <p>
       If
       <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
        <code class="literal">
         mandatory_roles
        </code>
       </a>
       is
            assigned a role at runtime that has the
       <a class="link" href="privileges-provided.html#priv_system-user">
        <code class="literal">
         SYSTEM_USER
        </code>
       </a>
       privilege, an
            error occurs and the
       <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
        <code class="literal">
         mandatory_roles
        </code>
       </a>
       value
            remains unchanged.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Even with this safeguard, it is better to avoid granting the
    <a class="link" href="privileges-provided.html#priv_system-user">
     <code class="literal">
      SYSTEM_USER
     </code>
    </a>
    privilege through a
        role in order to guard against the possibility of privilege
        escalation.
   </p>
   <p>
    If a role named in
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    is not present
        in the
    <code class="literal">
     mysql.user
    </code>
    system table, the role is
        not granted to users. When the server attempts role activation
        for a user, it does not treat the nonexistent role as mandatory
        and writes a warning to the error log. If the role is created
        later and thus becomes valid,
    <a class="link" href="flush.html#flush-privileges">
     <code class="literal">
      FLUSH
        PRIVILEGES
     </code>
    </a>
    may be necessary to cause the server to
        treat it as mandatory.
   </p>
   <p>
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW GRANTS
     </code>
    </a>
    displays mandatory
        roles according to the rules described in
    <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>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="roles-checking">
       </a>
       Checking Role Privileges
      </h4>
     </div>
    </div>
   </div>
   <p>
    To verify the privileges assigned to an account, use
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW GRANTS
     </code>
    </a>
    . For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa51493330"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'dev1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 dev1@localhost                       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 USAGE ON *.* TO `dev1`@`localhost`        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `app_developer`@`%` TO `dev1`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    However, that shows each granted role without
    <span class="quote">
     “
     <span class="quote">
      expanding
     </span>
     ”
    </span>
    it to the privileges the role
        represents. To show role privileges as well, add a
    <code class="literal">
     USING
    </code>
    clause naming the granted roles for
        which to display privileges:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25382679"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'dev1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">USING</span> <span class="token string">'app_developer'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 dev1@localhost                                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 USAGE ON *.* TO `dev1`@`localhost`                 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT ALL PRIVILEGES ON `app_db`.* TO `dev1`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `app_developer`@`%` TO `dev1`@`localhost`          <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    Verify each other type of user similarly:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa95738986"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'read_user1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">USING</span> <span class="token string">'app_read'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 read_user1@localhost                        <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 USAGE ON *.* TO `read_user1`@`localhost`         <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `app_db`.* TO `read_user1`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `app_read`@`%` TO `read_user1`@`localhost`       <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 prompt">mysql&gt;</span> <span class="token keyword">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'rw_user1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">USING</span> <span class="token string">'app_read'</span><span class="token punctuation">,</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 rw_user1@localhost                                                <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 USAGE ON *.* TO `rw_user1`@`localhost`                                 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT, INSERT, UPDATE, DELETE ON `app_db`.* TO `rw_user1`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost`               <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW GRANTS
     </code>
    </a>
    displays mandatory
        roles according to the rules described in
    <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>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="roles-activating">
       </a>
       Activating Roles
      </h4>
     </div>
    </div>
   </div>
   <p>
    Roles granted to a user account can be active or inactive within
        account sessions. If a granted role is active within a session,
        its privileges apply; otherwise, they do not. To determine which
        roles are active within the current session, use the
    <a class="link" href="information-functions.html#function_current-role">
     <code class="literal">
      CURRENT_ROLE()
     </code>
    </a>
    function.
   </p>
   <p>
    By default, granting a role to an account or naming it in the
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    system variable
        value does not automatically cause the role to become active
        within account sessions. For example, because thus far in the
        preceding discussion no
    <code class="literal">
     rw_user1
    </code>
    roles have
        been activated, if you connect to the server as
    <code class="literal">
     rw_user1
    </code>
    and invoke the
    <a class="link" href="information-functions.html#function_current-role">
     <code class="literal">
      CURRENT_ROLE()
     </code>
    </a>
    function, the
        result is
    <code class="literal">
     NONE
    </code>
    (no active roles):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42023574"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_ROLE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_ROLE() <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NONE           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
   </div>
   <p>
    To specify which roles should become active each time a user
        connects to the server and authenticates, use
    <a class="link" href="set-default-role.html" title="15.7.1.9 SET DEFAULT ROLE Statement">
     <code class="literal">
      SET DEFAULT ROLE
     </code>
    </a>
    . To set the
        default to all assigned roles for each account created earlier,
        use this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28944682"><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">SET</span> <span class="token keyword">DEFAULT</span> <span class="token keyword">ROLE</span> <span class="token keyword">ALL</span> <span class="token keyword">TO</span>
  <span class="token string">'dev1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span>
  <span class="token string">'read_user1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span>
  <span class="token string">'read_user2'</span>@<span class="token string">'localhost'</span><span class="token punctuation">,</span>
  <span class="token string">'rw_user1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now if you connect as
    <code class="literal">
     rw_user1
    </code>
    , the initial
        value of
    <a class="link" href="information-functions.html#function_current-role">
     <code class="literal">
      CURRENT_ROLE()
     </code>
    </a>
    reflects
        the new default role assignments:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa79168772"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_ROLE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_ROLE()                 <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>
<span class="token output"><span class="token punctuation">|</span> `app_read`@`%`,`app_write`@`%` <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></code></pre>
   </div>
   <p>
    To cause all explicitly granted and mandatory roles to be
        automatically activated when users connect to the server, enable
        the
    <a class="link" href="server-system-variables.html#sysvar_activate_all_roles_on_login">
     <code class="literal">
      activate_all_roles_on_login
     </code>
    </a>
    system variable. By default, automatic role activation is
        disabled.
   </p>
   <p>
    Within a session, a user can execute
    <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
     <code class="literal">
      SET
        ROLE
     </code>
    </a>
    to change the set of active roles. For example,
        for
    <code class="literal">
     rw_user1
    </code>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa75442055"><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">SET</span> <span class="token keyword">ROLE</span> <span class="token keyword">NONE</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_ROLE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_ROLE() <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> NONE           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">ROLE</span> <span class="token keyword">ALL</span> <span class="token keyword">EXCEPT</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_ROLE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_ROLE() <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> `app_read`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SET</span> <span class="token keyword">ROLE</span> <span class="token keyword">DEFAULT</span><span class="token punctuation">;</span> <span class="token keyword">SELECT</span> <span class="token function">CURRENT_ROLE</span><span class="token punctuation">(</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> CURRENT_ROLE()                 <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>
<span class="token output"><span class="token punctuation">|</span> `app_read`@`%`,`app_write`@`%` <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></code></pre>
   </div>
   <p>
    The first
    <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
     <code class="literal">
      SET ROLE
     </code>
    </a>
    statement
        deactivates all roles. The second makes
    <code class="literal">
     rw_user1
    </code>
    effectively read only. The third
        restores the default roles.
   </p>
   <a class="indexterm" name="idm46045249268832">
   </a>
   <a class="indexterm" name="idm46045249267344">
   </a>
   <a class="indexterm" name="idm46045249265856">
   </a>
   <a class="indexterm" name="idm46045249264368">
   </a>
   <p>
    The effective user for stored program and view objects is
        subject to the
    <code class="literal">
     DEFINER
    </code>
    and
    <code class="literal">
     SQL
        SECURITY
    </code>
    attributes, which determine whether execution
        occurs in invoker or definer context (see
    <a class="xref" href="stored-objects-security.html" title="27.6 Stored Object Access Control">
     Section 27.6, “Stored Object Access Control”
    </a>
    ):
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Stored program and view objects that execute in invoker
            context execute with the roles that are active within the
            current session.
      </p>
     </li>
     <li class="listitem">
      <p>
       Stored program and view objects that execute in definer
            context execute with the default roles of the user named in
            their
       <code class="literal">
        DEFINER
       </code>
       attribute. If
       <a class="link" href="server-system-variables.html#sysvar_activate_all_roles_on_login">
        <code class="literal">
         activate_all_roles_on_login
        </code>
       </a>
       is enabled, such objects execute with all roles granted to
            the
       <code class="literal">
        DEFINER
       </code>
       user, including mandatory
            roles. For stored programs, if execution should occur with
            roles different from the default, the program body can
            execute
       <a class="link" href="set-role.html" title="15.7.1.11 SET ROLE Statement">
        <code class="literal">
         SET
            ROLE
        </code>
       </a>
       to activate the required roles. This must be
            done with caution since the privileges assigned to roles can
            be changed.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="roles-revoking">
       </a>
       Revoking Roles or Role Privileges
      </h4>
     </div>
    </div>
   </div>
   <p>
    Just as roles can be granted to an account, they can be revoked
        from an account:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa67363461"><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">REVOKE</span> <span class="token keyword"><em class="replaceable">role</em></span> <span class="token keyword">FROM</span> <span class="token keyword"><em class="replaceable">user</em></span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Roles named in the
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    system variable
        value cannot be revoked.
   </p>
   <p>
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    can also be applied to a
        role to modify the privileges granted to it. This affects not
        only the role itself, but any account granted that role. Suppose
        that you want to temporarily make all application users read
        only. To do this, use
    <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
     <code class="literal">
      REVOKE
     </code>
    </a>
    to
        revoke the modification privileges from the
    <code class="literal">
     app_write
    </code>
    role:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa71483718"><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">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> app_db<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">FROM</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    As it happens, that leaves the role with no privileges at all,
        as can be seen using
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW GRANTS
     </code>
    </a>
    (which demonstrates that this statement can be used with roles,
        not just users):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa17757335"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 app_write@%                <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>
<span class="token output"><span class="token punctuation">|</span> GRANT USAGE ON *.* TO `app_write`@`%` <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></code></pre>
   </div>
   <p>
    Because revoking privileges from a role affects the privileges
        for any user who is assigned the modified role,
    <code class="literal">
     rw_user1
    </code>
    now has no table modification
        privileges (
    <a class="link" href="privileges-provided.html#priv_insert">
     <code class="literal">
      INSERT
     </code>
    </a>
    ,
    <a class="link" href="privileges-provided.html#priv_update">
     <code class="literal">
      UPDATE
     </code>
    </a>
    , and
    <a class="link" href="privileges-provided.html#priv_delete">
     <code class="literal">
      DELETE
     </code>
    </a>
    are no longer present):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa87858159"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'rw_user1'</span>@<span class="token string">'localhost'</span>
       <span class="token keyword">USING</span> <span class="token string">'app_read'</span><span class="token punctuation">,</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 rw_user1@localhost                                  <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 USAGE ON *.* TO `rw_user1`@`localhost`                   <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `app_db`.* TO `rw_user1`@`localhost`           <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `app_read`@`%`,`app_write`@`%` TO `rw_user1`@`localhost` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
    In effect, the
    <code class="literal">
     rw_user1
    </code>
    read/write user has
        become a read-only user. This also occurs for any other accounts
        that are granted the
    <code class="literal">
     app_write
    </code>
    role,
        illustrating how use of roles makes it unnecessary to modify
        privileges for individual accounts.
   </p>
   <p>
    To restore modification privileges to the role, simply re-grant
        them:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa12527907"><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">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> app_db<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Now
    <code class="literal">
     rw_user1
    </code>
    again has modification
        privileges, as do any other accounts granted the
    <code class="literal">
     app_write
    </code>
    role.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="roles-dropping">
       </a>
       Dropping Roles
      </h4>
     </div>
    </div>
   </div>
   <p>
    To drop roles, use
    <a class="link" href="drop-role.html" title="15.7.1.4 DROP ROLE Statement">
     <code class="literal">
      DROP ROLE
     </code>
    </a>
    :
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa41275390"><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">ROLE</span> <span class="token string">'app_read'</span><span class="token punctuation">,</span> <span class="token string">'app_write'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    Dropping a role revokes it from every account to which it was
        granted.
   </p>
   <p>
    Roles named in the
    <a class="link" href="server-system-variables.html#sysvar_mandatory_roles">
     <code class="literal">
      mandatory_roles
     </code>
    </a>
    system variable
        value cannot be dropped.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="role-user-interchangeability">
       </a>
       User and Role Interchangeability
      </h4>
     </div>
    </div>
   </div>
   <p>
    As has been hinted at earlier for
    <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
     <code class="literal">
      SHOW
        GRANTS
     </code>
    </a>
    , which displays grants for user accounts or
        roles, accounts and roles can be used interchangeably.
   </p>
   <p>
    One difference between roles and users is that
    <a class="link" href="create-role.html" title="15.7.1.2 CREATE ROLE Statement">
     <code class="literal">
      CREATE ROLE
     </code>
    </a>
    creates an
        authorization identifier that is locked by default, whereas
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    creates an
        authorization identifier that is unlocked by default. You should
        keep in mind that this distinction is not immutable; a user with
        appropriate privileges can lock or unlock roles or (other) users
        after they have been created.
   </p>
   <p>
    If a database administrator has a preference that a specific
        authorization identifier must be a role, a name scheme can be
        used to communicate this intention. For example, you could use a
    <code class="literal">
     r_
    </code>
    prefix for all authorization identifiers
        that you intend to be roles and nothing else.
   </p>
   <p>
    Another difference between roles and users lies in the
        privileges available for administering them:
   </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_create-role">
        <code class="literal">
         CREATE ROLE
        </code>
       </a>
       and
       <a class="link" href="privileges-provided.html#priv_drop-role">
        <code class="literal">
         DROP ROLE
        </code>
       </a>
       privileges enable
            only use of the
       <a class="link" href="create-role.html" title="15.7.1.2 CREATE ROLE Statement">
        <code class="literal">
         CREATE ROLE
        </code>
       </a>
       and
       <a class="link" href="drop-role.html" title="15.7.1.4 DROP ROLE Statement">
        <code class="literal">
         DROP ROLE
        </code>
       </a>
       statements,
            respectively.
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="privileges-provided.html#priv_create-user">
        <code class="literal">
         CREATE USER
        </code>
       </a>
       privilege
            enables use of the
       <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
        <code class="literal">
         ALTER
            USER
        </code>
       </a>
       ,
       <a class="link" href="create-role.html" title="15.7.1.2 CREATE ROLE Statement">
        <code class="literal">
         CREATE ROLE
        </code>
       </a>
       ,
       <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-role.html" title="15.7.1.4 DROP ROLE Statement">
        <code class="literal">
         DROP ROLE
        </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>
       statements.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Thus, the
    <a class="link" href="privileges-provided.html#priv_create-role">
     <code class="literal">
      CREATE ROLE
     </code>
    </a>
    and
    <a class="link" href="privileges-provided.html#priv_drop-role">
     <code class="literal">
      DROP ROLE
     </code>
    </a>
    privileges are not as
        powerful as
    <a class="link" href="privileges-provided.html#priv_create-user">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    and may
        be granted to users who should only be permitted to create and
        drop roles, and not perform more general account manipulation.
   </p>
   <p>
    With regard to privileges and interchangeability of users and
        roles, you can treat a user account like a role and grant that
        account to another user or a role. The effect is to grant the
        account's privileges and roles to the other user or role.
   </p>
   <p>
    This set of statements demonstrates that you can grant a user to
        a user, a role to a user, a user to a role, or a role to a role:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60766683"><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 keyword">CREATE</span> <span class="token keyword">ROLE</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">SELECT</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">'u1'</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><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">USER</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">'r2'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'u1'</span><span class="token punctuation">,</span> <span class="token string">'r1'</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">'u1'</span><span class="token punctuation">,</span> <span class="token string">'r1'</span> <span class="token keyword">TO</span> <span class="token string">'r2'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The result in each case is to grant to the grantee object the
        privileges associated with the granted object. After executing
        those statements, each of
    <code class="literal">
     u2
    </code>
    and
    <code class="literal">
     r2
    </code>
    have been granted privileges from a user
        (
    <code class="literal">
     u1
    </code>
    ) and a role (
    <code class="literal">
     r1
    </code>
    ):
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa59390324"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'u2'</span> <span class="token keyword">USING</span> <span class="token string">'u1'</span><span class="token punctuation">,</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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>
<span class="token output"><span class="token punctuation">|</span> GRANT USAGE ON *.* TO `u2`@`%`      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `db1`.* TO `u2`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `db2`.* TO `u2`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `u1`@`%`,`r1`@`%` 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>
<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> <span class="token string">'r2'</span> <span class="token keyword">USING</span> <span class="token string">'u1'</span><span class="token punctuation">,</span> <span class="token string">'r1'</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="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 r2@%                     <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>
<span class="token output"><span class="token punctuation">|</span> GRANT USAGE ON *.* TO `r2`@`%`      <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `db1`.* TO `r2`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT SELECT ON `db2`.* TO `r2`@`%` <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">|</span> GRANT `u1`@`%`,`r1`@`%` TO `r2`@`%` <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></code></pre>
   </div>
   <p>
    The preceding example is illustrative only, but
        interchangeability of user accounts and roles has practical
        application, such as in the following situation: Suppose that a
        legacy application development project began before the advent
        of roles in MySQL, so all user accounts associated with the
        project are granted privileges directly (rather than granted
        privileges by virtue of being granted roles). One of these
        accounts is a developer account that was originally granted
        privileges as follows:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63877322"><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">'old_app_dev'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'old_app_devpass'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token keyword">ALL</span> <span class="token keyword">ON</span> old_app<span class="token punctuation">.</span><span class="token operator">*</span> <span class="token keyword">TO</span> <span class="token string">'old_app_dev'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    If this developer leaves the project, it becomes necessary to
        assign the privileges to another user, or perhaps multiple users
        if development activities have expanded. Here are some ways to
        deal with the issue:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       Without using roles: Change the account password so the
            original developer cannot use it, and have a new developer
            use the account instead:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa46344697"><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">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'old_app_dev'</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">new_password</em>'</span><span class="token punctuation">;</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Using roles: Lock the account to prevent anyone from using
            it to connect to the server:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa37582073"><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">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'old_app_dev'</span>@<span class="token string">'localhost'</span> <span class="token keyword">ACCOUNT</span> <span class="token keyword">LOCK</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       Then treat the account as a role. For each developer new to
            the project, create a new account and grant to it the
            original developer account:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa25140826"><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">'new_app_dev1'</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">new_password</em>'</span><span class="token punctuation">;</span>
<span class="token keyword">GRANT</span> <span class="token string">'old_app_dev'</span>@<span class="token string">'localhost'</span> <span class="token keyword">TO</span> <span class="token string">'new_app_dev1'</span>@<span class="token string">'localhost'</span><span class="token punctuation">;</span></code></pre>
      </div>
      <p>
       The effect is to assign the original developer account
            privileges to the new account.
      </p>
     </li>
    </ul>
   </div>
  </div>
 </div>
 <br/>
</div>
