<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="expired-password-handling">
      </a>
      8.2.16 Server Handling of Expired Passwords
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045248285520">
  </a>
  <a class="indexterm" name="idm46045248284448">
  </a>
  <a class="indexterm" name="idm46045248282960">
  </a>
  <a class="indexterm" name="idm46045248281472">
  </a>
  <p>
   MySQL provides password-expiration capability, which enables
      database administrators to require that users reset their
      password. Passwords can be expired manually, and on the basis of a
      policy for automatic expiration (see
   <a class="xref" href="password-management.html" title="8.2.15 Password Management">
    Section 8.2.15, “Password Management”
   </a>
   ).
  </p>
  <p>
   The
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   statement enables
      account password expiration. For example:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa88101035"><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">'myuser'</span>@<span class="token string">'localhost'</span> <span class="token keyword">PASSWORD</span> <span class="token keyword">EXPIRE</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   For each connection that uses an account with an expired password,
      the server either disconnects the client or restricts the client
      to
   <span class="quote">
    “
    <span class="quote">
     sandbox mode,
    </span>
    ”
   </span>
   in which the server permits the
      client to perform only those operations necessary to reset the
      expired password. Which action is taken by the server depends on
      both client and server settings, as discussed later.
  </p>
  <p>
   If the server disconnects the client, it returns an
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_must_change_password_login" target="_top">
    <code class="literal">
     ER_MUST_CHANGE_PASSWORD_LOGIN
    </code>
   </a>
   error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa62109102"><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-terminal"><span class="token prompt">$&gt; </span><span class="token command">mysql</span> <span class="token property">-u</span> myuser <span class="token property">-p</span>
Password<span class="token punctuation">:</span> ******
<span class="token output">ERROR 1862 (HY000)<span class="token punctuation">:</span> Your password has expired. To log in you must
change it using a client that supports expired passwords.</span></code></pre>
  </div>
  <p>
   If the server restricts the client to sandbox mode, these
      operations are permitted within the client session:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The client can reset the account password with
      <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
       <code class="literal">
        ALTER USER
       </code>
      </a>
      or
      <a class="link" href="set-password.html" title="15.7.1.10 SET PASSWORD Statement">
       <code class="literal">
        SET PASSWORD
       </code>
      </a>
      . After that has
          been done, the server restores normal access for the session,
          as well as for subsequent connections that use the account.
     </p>
     <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
      <div class="admon-title">
       Note
      </div>
      <p>
       Although it is possible to
       <span class="quote">
        “
        <span class="quote">
         reset
        </span>
        ”
       </span>
       an expired
            password by setting it to its current value, it is
            preferable, as a matter of good policy, to choose a
            different password. DBAs can enforce non-reuse by
            establishing an appropriate password-reuse policy. See
       <a class="xref" href="password-management.html#password-reuse-policy" title="Password Reuse Policy">
        Password Reuse Policy
       </a>
       .
      </p>
     </div>
    </li>
   </ul>
  </div>
  <p>
   For any operation not permitted within the session, the server
      returns an
   <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_must_change_password" target="_top">
    <code class="literal">
     ER_MUST_CHANGE_PASSWORD
    </code>
   </a>
   error:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa93984337"><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">USE</span> performance_schema<span class="token punctuation">;</span>
<span class="token output">ERROR 1820 (HY000)<span class="token punctuation">:</span> You must reset your password using ALTER USER
statement before executing this statement.
</span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token output">ERROR 1820 (HY000)<span class="token punctuation">:</span> You must reset your password using ALTER USER
statement before executing this statement.</span></code></pre>
  </div>
  <p>
   That is what normally happens for interactive invocations of the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client because by default such
      invocations are put in sandbox mode. To resume normal functioning,
      select a new password.
  </p>
  <p>
   For noninteractive invocations of the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   client (for example, in batch mode), the server normally
      disconnects the client if the password is expired. To permit
      noninteractive
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   invocations to stay
      connected so that the password can be changed (using the
      statements permitted in sandbox mode), add the
   <a class="link" href="mysql-command-options.html#option_mysql_connect-expired-password">
    <code class="option">
     --connect-expired-password
    </code>
   </a>
   option to
      the
   <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
    <span class="command">
     <strong>
      mysql
     </strong>
    </span>
   </a>
   command.
  </p>
  <p>
   As mentioned previously, whether the server disconnects an
      expired-password client or restricts it to sandbox mode depends on
      a combination of client and server settings. The following
      discussion describes the relevant settings and how they interact.
  </p>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    This discussion applies only for accounts with expired
        passwords. If a client connects using a nonexpired password, the
        server handles the client normally.
   </p>
  </div>
  <p>
   On the client side, a given client indicates whether it can handle
      sandbox mode for expired passwords. For clients that use the C
      client library, there are two ways to do this:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Pass the
      <code class="literal">
       MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
      </code>
      flag
          to
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-options.html" target="_top">
       <code class="literal">
        mysql_options()
       </code>
      </a>
      prior to
          connecting:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-c"><div class="docs-select-all right" id="sa6192308"><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-c">bool arg <span class="token operator">=</span> <span class="token number">1</span><span class="token punctuation">;</span>
<span class="token function">mysql_options</span><span class="token punctuation">(</span>mysql<span class="token punctuation">,</span>
              MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS<span class="token punctuation">,</span>
              <span class="token operator">&amp;</span>arg<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      This is the technique used within the
      <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
       <span class="command">
        <strong>
         mysql
        </strong>
       </span>
      </a>
      client, which enables
      <code class="literal">
       MYSQL_OPT_CAN_HANDLE_EXPIRED_PASSWORDS
      </code>
      if
          invoked interactively or with the
      <a class="link" href="mysql-command-options.html#option_mysql_connect-expired-password">
       <code class="option">
        --connect-expired-password
       </code>
      </a>
      option.
     </p>
    </li>
    <li class="listitem">
     <p>
      Pass the
      <code class="literal">
       CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS
      </code>
      flag to
      <a class="ulink" href="/doc/c-api/8.4/en/mysql-real-connect.html" target="_top">
       <code class="literal">
        mysql_real_connect()
       </code>
      </a>
      at
          connect time:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting language-c"><div class="docs-select-all right" id="sa33213958"><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-c">MYSQL mysql<span class="token punctuation">;</span>
<span class="token function">mysql_init</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>mysql<span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">if</span> <span class="token punctuation">(</span><span class="token operator">!</span><span class="token function">mysql_real_connect</span><span class="token punctuation">(</span><span class="token operator">&amp;</span>mysql<span class="token punctuation">,</span>
                        host<span class="token punctuation">,</span> user<span class="token punctuation">,</span> password<span class="token punctuation">,</span> db<span class="token punctuation">,</span>
                        port<span class="token punctuation">,</span> unix_socket<span class="token punctuation">,</span>
                        CLIENT_CAN_HANDLE_EXPIRED_PASSWORDS<span class="token punctuation">)</span><span class="token punctuation">)</span>
<span class="token punctuation">{</span>
  <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> handle error <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span>
<span class="token punctuation">}</span></code></pre>
     </div>
    </li>
   </ul>
  </div>
  <p>
   Other MySQL Connectors have their own conventions for indicating
      readiness to handle sandbox mode. See the documentation for the
      Connector in which you are interested.
  </p>
  <p>
   On the server side, if a client indicates that it can handle
      expired passwords, the server puts it in sandbox mode.
  </p>
  <p>
   If a client does not indicate that it can handle expired passwords
      (or uses an older version of the client library that cannot so
      indicate), the server action depends on the value of the
   <a class="link" href="server-system-variables.html#sysvar_disconnect_on_expired_password">
    <code class="literal">
     disconnect_on_expired_password
    </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_disconnect_on_expired_password">
       <code class="literal">
        disconnect_on_expired_password
       </code>
      </a>
      is enabled (the default), the server disconnects the client
          with an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_must_change_password_login" target="_top">
       <code class="literal">
        ER_MUST_CHANGE_PASSWORD_LOGIN
       </code>
      </a>
      error.
     </p>
    </li>
    <li class="listitem">
     <p>
      If
      <a class="link" href="server-system-variables.html#sysvar_disconnect_on_expired_password">
       <code class="literal">
        disconnect_on_expired_password
       </code>
      </a>
      is disabled, the server puts the client in sandbox mode.
     </p>
    </li>
   </ul>
  </div>
 </div>
 <br/>
</div>
