<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h2 class="title">
      <a name="access-control">
      </a>
      8.2 Access Control and Account Management
     </h2>
    </div>
   </div>
  </div>
  <div class="toc">
   <dl class="toc">
    <dt>
     <span class="section">
      <a href="user-names.html">
       8.2.1 Account User Names and Passwords
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="privileges-provided.html">
       8.2.2 Privileges Provided by MySQL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="grant-tables.html">
       8.2.3 Grant Tables
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="account-names.html">
       8.2.4 Specifying Account Names
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="role-names.html">
       8.2.5 Specifying Role Names
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="connection-access.html">
       8.2.6 Access Control, Stage 1: Connection Verification
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="request-access.html">
       8.2.7 Access Control, Stage 2: Request Verification
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="creating-accounts.html">
       8.2.8 Adding Accounts, Assigning Privileges, and Dropping Accounts
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="reserved-accounts.html">
       8.2.9 Reserved Accounts
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="roles.html">
       8.2.10 Using Roles
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="account-categories.html">
       8.2.11 Account Categories
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="partial-revokes.html">
       8.2.12 Privilege Restriction Using Partial Revokes
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="privilege-changes.html">
       8.2.13 When Privilege Changes Take Effect
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="assigning-passwords.html">
       8.2.14 Assigning Account Passwords
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="password-management.html">
       8.2.15 Password Management
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="expired-password-handling.html">
       8.2.16 Server Handling of Expired Passwords
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="pluggable-authentication.html">
       8.2.17 Pluggable Authentication
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="multifactor-authentication.html">
       8.2.18 Multifactor Authentication
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="proxy-users.html">
       8.2.19 Proxy Users
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="account-locking.html">
       8.2.20 Account Locking
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="user-resources.html">
       8.2.21 Setting Account Resource Limits
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="problems-connecting.html">
       8.2.22 Troubleshooting Problems Connecting to MySQL
      </a>
     </span>
    </dt>
    <dt>
     <span class="section">
      <a href="account-activity-auditing.html">
       8.2.23 SQL-Based Account Activity Auditing
      </a>
     </span>
    </dt>
   </dl>
  </div>
  <a class="indexterm" name="idm46045252340944">
  </a>
  <a class="indexterm" name="idm46045252339904">
  </a>
  <a class="indexterm" name="idm46045252338832">
  </a>
  <a class="indexterm" name="idm46045252337760">
  </a>
  <a class="indexterm" name="idm46045252336688">
  </a>
  <a class="indexterm" name="idm46045252335616">
  </a>
  <a class="indexterm" name="idm46045252334128">
  </a>
  <a class="indexterm" name="idm46045252333056">
  </a>
  <a class="indexterm" name="idm46045252331984">
  </a>
  <a class="indexterm" name="idm46045252330496">
  </a>
  <p>
   MySQL enables the creation of accounts that permit client users to
    connect to the server and access data managed by the server. The
    primary function of the MySQL privilege system is to authenticate a
    user who connects from a given host and to associate that user with
    privileges on a database such as
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   ,
   <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
   <a class="link" href="delete.html" title="15.2.2 DELETE Statement">
    <code class="literal">
     DELETE
    </code>
   </a>
   . Additional functionality
    includes the ability to grant privileges for administrative
    operations.
  </p>
  <p>
   To control which users can connect, each account can be assigned
    authentication credentials such as a password. The user interface to
    MySQL accounts consists of SQL statements such as
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   ,
   <a class="link" href="grant.html" title="15.7.1.6 GRANT Statement">
    <code class="literal">
     GRANT
    </code>
   </a>
   , and
   <a class="link" href="revoke.html" title="15.7.1.8 REVOKE Statement">
    <code class="literal">
     REVOKE
    </code>
   </a>
   . See
   <a class="xref" href="account-management-statements.html" title="15.7.1 Account Management Statements">
    Section 15.7.1, “Account Management Statements”
   </a>
   .
  </p>
  <p>
   The MySQL privilege system ensures that all users may perform only
    the operations permitted to them. As a user, when you connect to a
    MySQL server, your identity is determined by
   <span class="emphasis">
    <em>
     the host from
    which you connect
    </em>
   </span>
   and
   <span class="emphasis">
    <em>
     the user name you
    specify
    </em>
   </span>
   . When you issue requests after connecting, the
    system grants privileges according to your identity and
   <span class="emphasis">
    <em>
     what you want to do
    </em>
   </span>
   .
  </p>
  <p>
   MySQL considers both your host name and user name in identifying you
    because there is no reason to assume that a given user name belongs
    to the same person on all hosts. For example, the user
   <code class="literal">
    joe
   </code>
   who connects from
   <code class="literal">
    office.example.com
   </code>
   need not be the same person as
    the user
   <code class="literal">
    joe
   </code>
   who connects from
   <code class="literal">
    home.example.com
   </code>
   . MySQL handles this by enabling
    you to distinguish users on different hosts that happen to have the
    same name: You can grant one set of privileges for connections by
   <code class="literal">
    joe
   </code>
   from
   <code class="literal">
    office.example.com
   </code>
   ,
    and a different set of privileges for connections by
   <code class="literal">
    joe
   </code>
   from
   <code class="literal">
    home.example.com
   </code>
   . To
    see what privileges a given account has, use the
   <a class="link" href="show-grants.html" title="15.7.7.22 SHOW GRANTS Statement">
    <code class="literal">
     SHOW GRANTS
    </code>
   </a>
   statement. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29375362"><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">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'joe'</span>@<span class="token string">'office.example.com'</span><span class="token punctuation">;</span>
<span class="token keyword">SHOW</span> <span class="token keyword">GRANTS</span> <span class="token keyword">FOR</span> <span class="token string">'joe'</span>@<span class="token string">'home.example.com'</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Internally, the server stores privilege information in the grant
    tables of the
   <code class="literal">
    mysql
   </code>
   system database. The MySQL
    server reads the contents of these tables into memory when it starts
    and bases access-control decisions on the in-memory copies of the
    grant tables.
  </p>
  <p>
   MySQL access control involves two stages when you run a client
    program that connects to the server:
  </p>
  <p>
   <span class="bold">
    <strong>
     Stage 1:
    </strong>
   </span>
   The server accepts or
    rejects the connection based on your identity and whether you can
    verify your identity by supplying the correct password.
  </p>
  <p>
   <span class="bold">
    <strong>
     Stage 2:
    </strong>
   </span>
   Assuming that you can
    connect, the server checks each statement you issue to determine
    whether you have sufficient privileges to perform it. For example,
    if you try to select rows from a table in a database or drop a table
    from the database, the server verifies that you have the
   <a class="link" href="privileges-provided.html#priv_select">
    <code class="literal">
     SELECT
    </code>
   </a>
   privilege for the table or the
   <a class="link" href="privileges-provided.html#priv_drop">
    <code class="literal">
     DROP
    </code>
   </a>
   privilege for the database.
  </p>
  <p>
   For a more detailed description of what happens during each stage,
    see
   <a class="xref" href="connection-access.html" title="8.2.6 Access Control, Stage 1: Connection Verification">
    Section 8.2.6, “Access Control, Stage 1: Connection Verification”
   </a>
   , and
   <a class="xref" href="request-access.html" title="8.2.7 Access Control, Stage 2: Request Verification">
    Section 8.2.7, “Access Control, Stage 2: Request Verification”
   </a>
   . For help in diagnosing
    privilege-related problems, see
   <a class="xref" href="problems-connecting.html" title="8.2.22 Troubleshooting Problems Connecting to MySQL">
    Section 8.2.22, “Troubleshooting Problems Connecting to MySQL”
   </a>
   .
  </p>
  <p>
   If your privileges are changed (either by yourself or someone else)
    while you are connected, those changes do not necessarily take
    effect immediately for the next statement that you issue. For
    details about the conditions under which the server reloads the
    grant tables, see
   <a class="xref" href="privilege-changes.html" title="8.2.13 When Privilege Changes Take Effect">
    Section 8.2.13, “When Privilege Changes Take Effect”
   </a>
   .
  </p>
  <p>
   There are some things that you cannot do with the MySQL privilege
    system:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      You cannot explicitly specify that a given user should be denied
        access. That is, you cannot explicitly match a user and then
        refuse the connection.
     </p>
    </li>
    <li class="listitem">
     <p>
      You cannot specify that a user has privileges to create or drop
        tables in a database but not to create or drop the database
        itself.
     </p>
    </li>
    <li class="listitem">
     <p>
      A password applies globally to an account. You cannot associate
        a password with a specific object such as a database, table, or
        routine.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
