<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="user-resources">
      </a>
      8.2.21 Setting Account Resource Limits
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045247567584">
  </a>
  <a class="indexterm" name="idm46045247566128">
  </a>
  <a class="indexterm" name="idm46045247564640">
  </a>
  <a class="indexterm" name="idm46045247563568">
  </a>
  <a class="indexterm" name="idm46045247562496">
  </a>
  <a class="indexterm" name="idm46045247561408">
  </a>
  <p>
   One means of restricting client use of MySQL server resources is
      to set the global
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   system
      variable to a nonzero value. This limits the number of
      simultaneous connections that can be made by any given account,
      but places no limits on what a client can do once connected. In
      addition, setting
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   does not
      enable management of individual accounts. Both types of control
      are of interest to MySQL administrators.
  </p>
  <p>
   To address such concerns, MySQL permits limits for individual
      accounts on use of these server resources:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The number of queries an account can issue per hour
     </p>
    </li>
    <li class="listitem">
     <p>
      The number of updates an account can issue per hour
     </p>
    </li>
    <li class="listitem">
     <p>
      The number of times an account can connect to the server per
          hour
     </p>
    </li>
    <li class="listitem">
     <p>
      The number of simultaneous connections to the server by an
          account
     </p>
    </li>
   </ul>
  </div>
  <p>
   Any statement that a client can issue counts against the query
      limit. Only statements that modify databases or tables count
      against the update limit.
  </p>
  <p>
   An
   <span class="quote">
    “
    <span class="quote">
     account
    </span>
    ”
   </span>
   in this context corresponds to a row in
      the
   <code class="literal">
    mysql.user
   </code>
   system table. That is, a
      connection is assessed against the
   <code class="literal">
    User
   </code>
   and
   <code class="literal">
    Host
   </code>
   values in the
   <code class="literal">
    user
   </code>
   table row that applies to the connection. For example, an account
   <code class="literal">
    'usera'@'%.example.com'
   </code>
   corresponds to a row in
      the
   <code class="literal">
    user
   </code>
   table that has
   <code class="literal">
    User
   </code>
   and
   <code class="literal">
    Host
   </code>
   values of
   <code class="literal">
    usera
   </code>
   and
   <code class="literal">
    %.example.com
   </code>
   , to permit
   <code class="literal">
    usera
   </code>
   to connect from any host in the
   <code class="literal">
    example.com
   </code>
   domain. In this case, the server
      applies resource limits in this row collectively to all
      connections by
   <code class="literal">
    usera
   </code>
   from any host in the
   <code class="literal">
    example.com
   </code>
   domain because all such connections
      use the same account.
  </p>
  <p>
   To establish resource limits for an account at account-creation
      time, use the
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   statement. To modify the limits for an existing account, use
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   . Provide a
   <code class="literal">
    WITH
   </code>
   clause that names each resource to be
      limited. The default value for each limit is zero (no limit). For
      example, to create a new account that can access the
   <code class="literal">
    customer
   </code>
   database, but only in a limited
      fashion, issue these statements:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa13477606"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">CREATE</span> <span class="token keyword">USER</span> <span class="token string">'francis'</span>@<span class="token string">'localhost'</span> <span class="token keyword">IDENTIFIED</span> <span class="token keyword">BY</span> <span class="token string">'frank'</span>
<span class="token prompt">    -&gt;</span>     <span class="token keyword">WITH</span> <span class="token keyword">MAX_QUERIES_PER_HOUR</span> <span class="token number">20</span>
<span class="token prompt">    -&gt;</span>          <span class="token keyword">MAX_UPDATES_PER_HOUR</span> <span class="token number">10</span>
<span class="token prompt">    -&gt;</span>          <span class="token keyword">MAX_CONNECTIONS_PER_HOUR</span> <span class="token number">5</span>
<span class="token prompt">    -&gt;</span>          <span class="token keyword">MAX_USER_CONNECTIONS</span> <span class="token number">2</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The limit types need not all be named in the
   <code class="literal">
    WITH
   </code>
   clause, but those named can be present in
      any order. The value for each per-hour limit should be an integer
      representing a count per hour. For
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   , the limit is an integer
      representing the maximum number of simultaneous connections by the
      account. If this limit is set to zero, the global
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   system
      variable value determines the number of simultaneous connections.
      If
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   is also
      zero, there is no limit for the account.
  </p>
  <p>
   To modify limits for an existing account, use an
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   statement. The following
      statement changes the query limit for
   <code class="literal">
    francis
   </code>
   to 100:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa33754116"><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">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'francis'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_QUERIES_PER_HOUR</span> <span class="token number">100</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   The statement modifies only the limit value specified and leaves
      the account otherwise unchanged.
  </p>
  <p>
   To remove a limit, set its value to zero. For example, to remove
      the limit on how many times per hour
   <code class="literal">
    francis
   </code>
   can connect, use this statement:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa75778326"><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">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'francis'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_CONNECTIONS_PER_HOUR</span> <span class="token number">0</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   As mentioned previously, the simultaneous-connection limit for an
      account is determined from the
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limit and the
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   system
      variable. Suppose that the global
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   value is 10
      and three accounts have individual resource limits specified as
      follows:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa28048466"><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">'user1'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_USER_CONNECTIONS</span> <span class="token number">0</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'user2'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_USER_CONNECTIONS</span> <span class="token number">5</span><span class="token punctuation">;</span>
<span class="token keyword">ALTER</span> <span class="token keyword">USER</span> <span class="token string">'user3'</span>@<span class="token string">'localhost'</span> <span class="token keyword">WITH</span> <span class="token keyword">MAX_USER_CONNECTIONS</span> <span class="token number">20</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   <code class="literal">
    user1
   </code>
   has a connection limit of 10 (the global
   <a class="link" href="server-system-variables.html#sysvar_max_user_connections">
    <code class="literal">
     max_user_connections
    </code>
   </a>
   value)
      because it has a
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limit of
      zero.
   <code class="literal">
    user2
   </code>
   and
   <code class="literal">
    user3
   </code>
   have
      connection limits of 5 and 20, respectively, because they have
      nonzero
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limits.
  </p>
  <p>
   The server stores resource limits for an account in the
   <code class="literal">
    user
   </code>
   table row corresponding to the account.
      The
   <code class="literal">
    max_questions
   </code>
   ,
   <code class="literal">
    max_updates
   </code>
   , and
   <code class="literal">
    max_connections
   </code>
   columns store the per-hour
      limits, and the
   <code class="literal">
    max_user_connections
   </code>
   column
      stores the
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limit. (See
   <a class="xref" href="grant-tables.html" title="8.2.3 Grant Tables">
    Section 8.2.3, “Grant Tables”
   </a>
   .)
  </p>
  <p>
   Resource-use counting takes place when any account has a nonzero
      limit placed on its use of any of the resources.
  </p>
  <p>
   As the server runs, it counts the number of times each account
      uses resources. If an account reaches its limit on number of
      connections within the last hour, the server rejects further
      connections for the account until that hour is up. Similarly, if
      the account reaches its limit on the number of queries or updates,
      the server rejects further queries or updates until the hour is
      up. In all such cases, the server issues appropriate error
      messages.
  </p>
  <p>
   Resource counting occurs per account, not per client. For example,
      if your account has a query limit of 50, you cannot increase your
      limit to 100 by making two simultaneous client connections to the
      server. Queries issued on both connections are counted together.
  </p>
  <p>
   The current per-hour resource-use counts can be reset globally for
      all accounts, or individually for a given account:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      To reset the current counts to zero for all accounts, issue a
      <a class="link" href="flush.html#flush-user-resources">
       <code class="literal">
        FLUSH USER_RESOURCES
       </code>
      </a>
      statement.
          The counts also can be reset by reloading the grant tables
          (for example, with a
      <a class="link" href="flush.html#flush-privileges">
       <code class="literal">
        FLUSH
          PRIVILEGES
       </code>
      </a>
      statement or a
      <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
       <span class="command">
        <strong>
         mysqladmin
          reload
        </strong>
       </span>
      </a>
      command).
     </p>
    </li>
    <li class="listitem">
     <p>
      The counts for an individual account can be reset to zero by
          setting any of its limits again. Specify a limit value equal
          to the value currently assigned to the account.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Per-hour counter resets do not affect the
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limit.
  </p>
  <p>
   All counts begin at zero when the server starts. Counts do not
      carry over through server restarts.
  </p>
  <p>
   For the
   <code class="literal">
    MAX_USER_CONNECTIONS
   </code>
   limit, an edge
      case can occur if the account currently has open the maximum
      number of connections permitted to it: A disconnect followed
      quickly by a connect can result in an error
      (
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_too_many_user_connections" target="_top">
    <code class="literal">
     ER_TOO_MANY_USER_CONNECTIONS
    </code>
   </a>
   or
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_user_limit_reached" target="_top">
    <code class="literal">
     ER_USER_LIMIT_REACHED
    </code>
   </a>
   ) if the
      server has not fully processed the disconnect by the time the
      connect occurs. When the server finishes disconnect processing,
      another connection is once more permitted.
  </p>
 </div>
 <br/>
</div>
