<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="request-access">
      </a>
      8.2.7 Access Control, Stage 2: Request Verification
     </h3>
    </div>
   </div>
  </div>
  <p>
   After the server accepts a connection, it enters Stage 2 of access
      control. For each request that you issue through the connection,
      the server determines what operation you want to perform, then
      checks whether your privileges are sufficient. This is where the
      privilege columns in the grant tables come into play. These
      privileges can come from any of the
   <code class="literal">
    user
   </code>
   ,
   <code class="literal">
    global_grants
   </code>
   ,
   <code class="literal">
    db
   </code>
   ,
   <code class="literal">
    tables_priv
   </code>
   ,
   <code class="literal">
    columns_priv
   </code>
   ,
      or
   <code class="literal">
    procs_priv
   </code>
   tables. (You may find it helpful
      to refer to
   <a class="xref" href="grant-tables.html" title="8.2.3 Grant Tables">
    Section 8.2.3, “Grant Tables”
   </a>
   , which lists the
      columns present in each grant table.)
  </p>
  <a class="indexterm" name="idm46045249694096">
  </a>
  <p>
   The
   <code class="literal">
    user
   </code>
   and
   <code class="literal">
    global_grants
   </code>
   tables grant global privileges. The rows in these tables for a
      given account indicate the account privileges that apply on a
      global basis no matter what the default database is. For example,
      if the
   <code class="literal">
    user
   </code>
   table grants you the
   <a class="link" href="privileges-provided.html#priv_delete">
    <code class="literal">
     DELETE
    </code>
   </a>
   privilege, you can delete
      rows from any table in any database on the server host. It is wise
      to grant privileges in the
   <code class="literal">
    user
   </code>
   table only to
      people who need them, such as database administrators. For other
      users, leave all privileges in the
   <code class="literal">
    user
   </code>
   table
      set to
   <code class="literal">
    'N'
   </code>
   and grant privileges at more
      specific levels only (for particular databases, tables, columns,
      or routines). It is also possible to grant database privileges
      globally but use partial revokes to restrict them from being
      exercised on specific databases (see
   <a class="xref" href="partial-revokes.html" title="8.2.12 Privilege Restriction Using Partial Revokes">
    Section 8.2.12, “Privilege Restriction Using Partial Revokes”
   </a>
   ).
  </p>
  <a class="indexterm" name="idm46045249685728">
  </a>
  <a class="indexterm" name="idm46045249684656">
  </a>
  <p>
   The
   <code class="literal">
    db
   </code>
   table grants database-specific
      privileges. Values in the scope columns of this table can take the
      following forms:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      A blank
      <code class="literal">
       User
      </code>
      value matches the anonymous
          user. A nonblank value matches literally; there are no
          wildcards in user names.
     </p>
    </li>
    <li class="listitem">
     <p>
      The wildcard characters
      <code class="literal">
       %
      </code>
      and
      <code class="literal">
       _
      </code>
      can be used in the
      <code class="literal">
       Host
      </code>
      and
      <code class="literal">
       Db
      </code>
      columns.
          These have the same meaning as for pattern-matching operations
          performed with the
      <a class="link" href="string-comparison-functions.html#operator_like">
       <code class="literal">
        LIKE
       </code>
      </a>
      operator.
          If you want to use either character literally when granting
          privileges, you must escape it with a backslash. For example,
          to include the underscore character (
      <code class="literal">
       _
      </code>
      ) as
          part of a database name, specify it as
      <code class="literal">
       \_
      </code>
      in the
      <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
       <code class="literal">
        GRANT
       </code>
      </a>
      statement.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       '%'
      </code>
      or blank
      <code class="literal">
       Host
      </code>
      value means
      <span class="quote">
       “
       <span class="quote">
        any host.
       </span>
       ”
      </span>
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       '%'
      </code>
      or blank
      <code class="literal">
       Db
      </code>
      value
          means
      <span class="quote">
       “
       <span class="quote">
        any database.
       </span>
       ”
      </span>
     </p>
    </li>
   </ul>
  </div>
  <a class="indexterm" name="idm46045249666928">
  </a>
  <a class="indexterm" name="idm46045249665440">
  </a>
  <a class="indexterm" name="idm46045249663952">
  </a>
  <p>
   The server reads the
   <code class="literal">
    db
   </code>
   table into memory and
      sorts it at the same time that it reads the
   <code class="literal">
    user
   </code>
   table. The server sorts the
   <code class="literal">
    db
   </code>
   table based on the
   <code class="literal">
    Host
   </code>
   ,
   <code class="literal">
    Db
   </code>
   , and
   <code class="literal">
    User
   </code>
   scope columns.
      As with the
   <code class="literal">
    user
   </code>
   table, sorting puts the
      most-specific values first and least-specific values last, and
      when the server looks for matching rows, it uses the first match
      that it finds.
  </p>
  <a class="indexterm" name="idm46045249656816">
  </a>
  <a class="indexterm" name="idm46045249655312">
  </a>
  <a class="indexterm" name="idm46045249653808">
  </a>
  <p>
   The
   <code class="literal">
    tables_priv
   </code>
   ,
   <code class="literal">
    columns_priv
   </code>
   , and
   <code class="literal">
    procs_priv
   </code>
   tables grant table-specific, column-specific, and routine-specific
      privileges. Values in the scope columns of these tables can take
      the following forms:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The wildcard characters
      <code class="literal">
       %
      </code>
      and
      <code class="literal">
       _
      </code>
      can be used in the
      <code class="literal">
       Host
      </code>
      column. These have the same meaning as
          for pattern-matching operations performed with the
      <a class="link" href="string-comparison-functions.html#operator_like">
       <code class="literal">
        LIKE
       </code>
      </a>
      operator.
     </p>
    </li>
    <li class="listitem">
     <p>
      A
      <code class="literal">
       '%'
      </code>
      or blank
      <code class="literal">
       Host
      </code>
      value means
      <span class="quote">
       “
       <span class="quote">
        any host.
       </span>
       ”
      </span>
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       Db
      </code>
      ,
      <code class="literal">
       Table_name
      </code>
      ,
      <code class="literal">
       Column_name
      </code>
      , and
      <code class="literal">
       Routine_name
      </code>
      columns cannot contain
          wildcards or be blank.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The server sorts the
   <code class="literal">
    tables_priv
   </code>
   ,
   <code class="literal">
    columns_priv
   </code>
   , and
   <code class="literal">
    procs_priv
   </code>
   tables based on the
   <code class="literal">
    Host
   </code>
   ,
   <code class="literal">
    Db
   </code>
   , and
   <code class="literal">
    User
   </code>
   columns. This
      is similar to
   <code class="literal">
    db
   </code>
   table sorting, but simpler
      because only the
   <code class="literal">
    Host
   </code>
   column can contain
      wildcards.
  </p>
  <p>
   The server uses the sorted tables to verify each request that it
      receives. For requests that require administrative privileges such
      as
   <a class="link" href="privileges-provided.html#priv_shutdown">
    <code class="literal">
     SHUTDOWN
    </code>
   </a>
   or
   <a class="link" href="privileges-provided.html#priv_reload">
    <code class="literal">
     RELOAD
    </code>
   </a>
   , the server checks only the
   <code class="literal">
    user
   </code>
   and
   <code class="literal">
    global_privilege
   </code>
   tables because those are the only tables that specify
      administrative privileges. The server grants access if a row for
      the account in those tables permits the requested operation and
      denies access otherwise. For example, if you want to execute
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin shutdown
     </strong>
    </span>
   </a>
   but your
   <code class="literal">
    user
   </code>
   table row does not grant the
   <a class="link" href="privileges-provided.html#priv_shutdown">
    <code class="literal">
     SHUTDOWN
    </code>
   </a>
   privilege to you, the
      server denies access without even checking the
   <code class="literal">
    db
   </code>
   table. (The latter table contains no
   <code class="literal">
    Shutdown_priv
   </code>
   column, so there is no need to
      check it.)
  </p>
  <p>
   For database-related requests
      (
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   ,
   <a class="link" href="update.html" title="15.2.17 UPDATE Statement">
    <code class="literal">
     UPDATE
    </code>
   </a>
   , and so on), the server
      first checks the user's global privileges in the
   <code class="literal">
    user
   </code>
   table row (less any privilege restrictions
      imposed by partial revokes). If the row permits the requested
      operation, access is granted. If the global privileges in the
   <code class="literal">
    user
   </code>
   table are insufficient, the server
      determines the user's database-specific privileges from the
   <code class="literal">
    db
   </code>
   table:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The server looks in the
      <code class="literal">
       db
      </code>
      table for a
          match on the
      <code class="literal">
       Host
      </code>
      ,
      <code class="literal">
       Db
      </code>
      ,
          and
      <code class="literal">
       User
      </code>
      columns.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       Host
      </code>
      and
      <code class="literal">
       User
      </code>
      columns are matched to the connecting user's host name and
          MySQL user name.
     </p>
    </li>
    <li class="listitem">
     <p>
      The
      <code class="literal">
       Db
      </code>
      column is matched to the database
          that the user wants to access.
     </p>
    </li>
    <li class="listitem">
     <p>
      If there is no row for the
      <code class="literal">
       Host
      </code>
      and
      <code class="literal">
       User
      </code>
      , access is denied.
     </p>
    </li>
   </ul>
  </div>
  <p>
   After determining the database-specific privileges granted by the
   <code class="literal">
    db
   </code>
   table rows, the server adds them to the
      global privileges granted by the
   <code class="literal">
    user
   </code>
   table. If
      the result permits the requested operation, access is granted.
      Otherwise, the server successively checks the user's table and
      column privileges in the
   <code class="literal">
    tables_priv
   </code>
   and
   <code class="literal">
    columns_priv
   </code>
   tables, adds those to the user's
      privileges, and permits or denies access based on the result. For
      stored-routine operations, the server uses the
   <code class="literal">
    procs_priv
   </code>
   table rather than
   <code class="literal">
    tables_priv
   </code>
   and
   <code class="literal">
    columns_priv
   </code>
   .
  </p>
  <p>
   Expressed in boolean terms, the preceding description of how a
      user's privileges are calculated may be summarized like this:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa72483520"><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">global</span> <span class="token keyword">privileges</span>
<span class="token operator">OR</span> <span class="token keyword">database</span> <span class="token keyword">privileges</span>
<span class="token operator">OR</span> <span class="token keyword">table</span> <span class="token keyword">privileges</span>
<span class="token operator">OR</span> <span class="token keyword">column</span> <span class="token keyword">privileges</span>
<span class="token operator">OR</span> <span class="token keyword">routine</span> <span class="token keyword">privileges</span></code></pre>
  </div>
  <p>
   It may not be apparent why, if the global privileges are initially
      found to be insufficient for the requested operation, the server
      adds those privileges to the database, table, and column
      privileges later. The reason is that a request might require more
      than one type of privilege. For example, if you execute an
   <a class="link" href="insert-select.html" title="15.2.7.1 INSERT ... SELECT Statement">
    <code class="literal">
     INSERT INTO ...
      SELECT
    </code>
   </a>
   statement, you need both the
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   and the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privileges. Your privileges
      might be such that the
   <code class="literal">
    user
   </code>
   table row grants
      one privilege global and the
   <code class="literal">
    db
   </code>
   table row
      grants the other specifically for the relevant database. In this
      case, you have the necessary privileges to perform the request,
      but the server cannot tell that from either your global or
      database privileges alone. It must make an access-control decision
      based on the combined privileges.
  </p>
 </div>
 <br/>
</div>
