<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="caching-sha2-pluggable-authentication">
      </a>
      8.4.1.2 Caching SHA-2 Pluggable Authentication
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045246295760">
  </a>
  <a class="indexterm" name="idm46045246294656">
  </a>
  <p>
   MySQL provides two authentication plugins that implement SHA-256
        hashing for user account passwords:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <code class="literal">
       caching_sha2_password
      </code>
      : Implements SHA-256
            authentication (like
      <code class="literal">
       sha256_password
      </code>
      ),
            but uses caching on the server side for better performance
            and has additional features for wider applicability.
     </p>
    </li>
    <li class="listitem">
     <p>
      <code class="literal">
       sha256_password
      </code>
      (deprecated): Implements
            basic SHA-256 authentication. This is deprecated and subject
            to removal, do not use this authentication plugin.
     </p>
    </li>
   </ul>
  </div>
  <p>
   This section describes the caching SHA-2 authentication plugin.
        For information about the original basic (noncaching) deprecated
        plugin, see
   <a class="xref" href="sha256-pluggable-authentication.html" title="8.4.1.3 SHA-256 Pluggable Authentication">
    Section 8.4.1.3, “SHA-256 Pluggable Authentication”
   </a>
   .
  </p>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    In MySQL 8.4,
    <code class="literal">
     caching_sha2_password
    </code>
    is the default
          authentication plugin rather than
    <code class="literal">
     mysql_native_password
    </code>
    (deprecated). For
          information about the implications of this change for server
          operation and compatibility of the server with clients and
          connectors, see
    <a class="ulink" href="/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password" target="_top">
     caching_sha2_password as the Preferred Authentication Plugin
    </a>
    .
   </p>
  </div>
  <div class="important" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Important
   </div>
   <p>
    To connect to the server using an account that authenticates
          with the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin, you
          must use either a secure connection or an unencrypted
          connection that supports password exchange using an RSA key
          pair, as described later in this section. Either way, the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin uses
          MySQL's encryption capabilities. See
    <a class="xref" href="encrypted-connections.html" title="8.3 Using Encrypted Connections">
     Section 8.3, “Using Encrypted Connections”
    </a>
    .
   </p>
  </div>
  <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Note
   </div>
   <p>
    In the name
    <code class="literal">
     sha256_password
    </code>
    ,
    <span class="quote">
     “
     <span class="quote">
      sha256
     </span>
     ”
    </span>
    refers to the 256-bit digest length the
          plugin uses for encryption. In the name
    <code class="literal">
     caching_sha2_password
    </code>
    ,
    <span class="quote">
     “
     <span class="quote">
      sha2
     </span>
     ”
    </span>
    refers more generally to the SHA-2 class of encryption
          algorithms, of which 256-bit encryption is one instance. The
          latter name choice leaves room for future expansion of
          possible digest lengths without changing the plugin name.
   </p>
  </div>
  <p>
   The
   <code class="literal">
    caching_sha2_password
   </code>
   plugin has these
        advantages, compared to the deprecated
   <code class="literal">
    sha256_password
   </code>
   plugin:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      On the server side, an in-memory cache enables faster
            reauthentication of users who have connected previously when
            they connect again.
     </p>
    </li>
    <li class="listitem">
     <p>
      RSA-based password exchange is available regardless of the
            SSL library against which MySQL is linked.
     </p>
    </li>
    <li class="listitem">
     <p>
      Support is provided for client connections that use the Unix
            socket-file and shared-memory protocols.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The following table shows the plugin names on the server and
        client sides.
  </p>
  <div class="table">
   <a name="idm46045246271328">
   </a>
   <p class="title">
    <b>
     Table 8.15 Plugin and Library Names for SHA-2 Authentication
    </b>
   </p>
   <div class="table-contents">
    <table summary="Names for the plugins and library file used for SHA-2 password authentication.">
     <colgroup>
      <col style="width: 30%"/>
      <col style="width: 70%"/>
     </colgroup>
     <thead>
      <tr>
       <th>
        Plugin or File
       </th>
       <th>
        Plugin or File Name
       </th>
      </tr>
     </thead>
     <tbody>
      <tr>
       <td>
        Server-side plugin
       </td>
       <td>
        <code class="literal">
         caching_sha2_password
        </code>
       </td>
      </tr>
      <tr>
       <td>
        Client-side plugin
       </td>
       <td>
        <code class="literal">
         caching_sha2_password
        </code>
       </td>
      </tr>
      <tr>
       <td>
        Library file
       </td>
       <td>
        None (plugins are built in)
       </td>
      </tr>
     </tbody>
    </table>
   </div>
  </div>
  <br class="table-break"/>
  <p>
   The following sections provide installation and usage
        information specific to caching SHA-2 pluggable authentication:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="caching-sha2-pluggable-authentication.html#caching-sha2-pluggable-authentication-installation" title="Installing SHA-2 Pluggable Authentication">
       Installing SHA-2 Pluggable Authentication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="caching-sha2-pluggable-authentication.html#caching-sha2-pluggable-authentication-usage" title="Using SHA-2 Pluggable Authentication">
       Using SHA-2 Pluggable Authentication
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="caching-sha2-pluggable-authentication.html#caching-sha2-pluggable-authentication-cache-operation" title="Cache Operation for SHA-2 Pluggable Authentication">
       Cache Operation for SHA-2 Pluggable Authentication
      </a>
     </p>
    </li>
   </ul>
  </div>
  <p>
   For general information about pluggable authentication in MySQL,
        see
   <a class="xref" href="pluggable-authentication.html" title="8.2.17 Pluggable Authentication">
    Section 8.2.17, “Pluggable Authentication”
   </a>
   .
  </p>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="caching-sha2-pluggable-authentication-installation">
       </a>
       Installing SHA-2 Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <p>
    The
    <code class="literal">
     caching_sha2_password
    </code>
    plugin exists in
          server and client forms:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The server-side plugin is built into the server, need not
              be loaded explicitly, and cannot be disabled by unloading
              it.
      </p>
     </li>
     <li class="listitem">
      <p>
       The client-side plugin is built into the
       <code class="literal">
        libmysqlclient
       </code>
       client library and is
              available to any program linked against
       <code class="literal">
        libmysqlclient
       </code>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    The server-side plugin uses the
    <code class="literal">
     sha2_cache_cleaner
    </code>
    audit plugin as a helper
          to perform password cache management.
    <code class="literal">
     sha2_cache_cleaner
    </code>
    , like
    <code class="literal">
     caching_sha2_password
    </code>
    , is built in and need
          not be installed.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="caching-sha2-pluggable-authentication-usage">
       </a>
       Using SHA-2 Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <p>
    To set up an account that uses the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin for SHA-256
          password hashing, use the following statement, where
    <em class="replaceable">
     <code>
      password
     </code>
    </em>
    is the desired account
          password:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa97106768"><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">'sha2user'</span>@<span class="token string">'localhost'</span>
<span class="token keyword">IDENTIFIED</span> <span class="token keyword">WITH</span> caching_sha2_password <span class="token keyword">BY</span> <span class="token string">'<em class="replaceable">password</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The server assigns the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin to the account
          and uses it to encrypt the password using SHA-256, storing
          those values in the
    <code class="literal">
     plugin
    </code>
    and
    <code class="literal">
     authentication_string
    </code>
    columns of the
    <code class="literal">
     mysql.user
    </code>
    system table.
   </p>
   <p>
    The preceding instructions do not assume that
    <code class="literal">
     caching_sha2_password
    </code>
    is the default
          authentication plugin. If
    <code class="literal">
     caching_sha2_password
    </code>
    is the default
          authentication plugin, a simpler
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE
          USER
     </code>
    </a>
    syntax can be used:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa92350584"><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">'sha2user'</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">password</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The default plugin is determined by the value of the
    <a class="link" href="server-system-variables.html#sysvar_authentication_policy">
     <code class="literal">
      authentication_policy
     </code>
    </a>
    system
          variable; the default is to use
    <code class="literal">
     caching_sha2_password
    </code>
    .
   </p>
   <p>
    To use a different plugin, you must specify it using
    <code class="literal">
     IDENTIFIED WITH
    </code>
    . For example, to specify
          the deprecated
    <code class="literal">
     mysql_native_password
    </code>
    plugin, use this statement:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa52499528"><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">'nativeuser'</span>@<span class="token string">'localhost'</span>
<span class="token keyword">IDENTIFIED</span> <span class="token keyword">WITH</span> mysql_native_password <span class="token keyword">BY</span> <span class="token string">'<em class="replaceable">password</em>'</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    <code class="literal">
     caching_sha2_password
    </code>
    supports connections
          over secure transport. If you follow the RSA configuration
          procedure given later in this section, it also supports
          encrypted password exchange using RSA over unencrypted
          connections. RSA support has these characteristics:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       On the server side, two system variables name the RSA
              private and public key-pair files:
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_private_key_path">
        <code class="literal">
         caching_sha2_password_private_key_path
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_public_key_path">
        <code class="literal">
         caching_sha2_password_public_key_path
        </code>
       </a>
       .
              The database administrator must set these variables at
              server startup if the key files to use have names that
              differ from the system variable default values.
      </p>
     </li>
     <li class="listitem">
      <p>
       The server uses the
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_auto_generate_rsa_keys">
        <code class="literal">
         caching_sha2_password_auto_generate_rsa_keys
        </code>
       </a>
       system variable to determine whether to automatically
              generate the RSA key-pair files. See
       <a class="xref" href="creating-ssl-rsa-files.html" title="8.3.3 Creating SSL and RSA Certificates and Keys">
        Section 8.3.3, “Creating SSL and RSA Certificates and Keys”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       The
       <a class="link" href="server-status-variables.html#statvar_Caching_sha2_password_rsa_public_key">
        <code class="literal">
         Caching_sha2_password_rsa_public_key
        </code>
       </a>
       status variable displays the RSA public key value used by
              the
       <code class="literal">
        caching_sha2_password
       </code>
       authentication plugin.
      </p>
     </li>
     <li class="listitem">
      <p>
       Clients that are in possession of the RSA public key can
              perform RSA key pair-based password exchange with the
              server during the connection process, as described later.
      </p>
     </li>
     <li class="listitem">
      <p>
       For connections by accounts that authenticate with
       <code class="literal">
        caching_sha2_password
       </code>
       and RSA key
              pair-based password exchange, the server does not send the
              RSA public key to clients by default. Clients can use a
              client-side copy of the required public key, or request
              the public key from the server.
      </p>
      <p>
       Use of a trusted local copy of the public key enables the
              client to avoid a round trip in the client/server
              protocol, and is more secure than requesting the public
              key from the server. On the other hand, requesting the
              public key from the server is more convenient (it requires
              no management of a client-side file) and may be acceptable
              in secure network environments.
      </p>
      <div class="itemizedlist">
       <ul class="itemizedlist" style="list-style-type: circle; ">
        <li class="listitem">
         <p>
          For command-line clients, use the
          <a class="link" href="mysql-command-options.html#option_mysql_server-public-key-path">
           <code class="option">
            --server-public-key-path
           </code>
          </a>
          option to specify the RSA public key file. Use the
          <a class="link" href="mysql-command-options.html#option_mysql_get-server-public-key">
           <code class="option">
            --get-server-public-key
           </code>
          </a>
          option to request the public key from the server. The
                  following programs support the two options:
          <a class="link" href="mysql.html" title="6.5.1 mysql — The MySQL Command-Line Client">
           <span class="command">
            <strong>
             mysql
            </strong>
           </span>
          </a>
          ,
          <span class="command">
           <strong>
            mysqlsh
           </strong>
          </span>
          ,
          <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
           <span class="command">
            <strong>
             mysqladmin
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqlbinlog.html" title="6.6.9 mysqlbinlog — Utility for Processing Binary Log Files">
           <span class="command">
            <strong>
             mysqlbinlog
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqlcheck.html" title="6.5.3 mysqlcheck — A Table Maintenance Program">
           <span class="command">
            <strong>
             mysqlcheck
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqldump.html" title="6.5.4 mysqldump — A Database Backup Program">
           <span class="command">
            <strong>
             mysqldump
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqlimport.html" title="6.5.5 mysqlimport — A Data Import Program">
           <span class="command">
            <strong>
             mysqlimport
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqlshow.html" title="6.5.6 mysqlshow — Display Database, Table, and Column Information">
           <span class="command">
            <strong>
             mysqlshow
            </strong>
           </span>
          </a>
          ,
          <a class="link" href="mysqlslap.html" title="6.5.7 mysqlslap — A Load Emulation Client">
           <span class="command">
            <strong>
             mysqlslap
            </strong>
           </span>
          </a>
          ,
          <span class="command">
           <strong>
            mysqltest
           </strong>
          </span>
          .
         </p>
        </li>
        <li class="listitem">
         <p>
          For programs that use the C API, call
          <a class="ulink" href="/doc/c-api/8.4/en/mysql-options.html" target="_top">
           <code class="literal">
            mysql_options()
           </code>
          </a>
          to
                  specify the RSA public key file by passing the
          <code class="literal">
           MYSQL_SERVER_PUBLIC_KEY
          </code>
          option and
                  the name of the file, or request the public key from
                  the server by passing the
          <code class="literal">
           MYSQL_OPT_GET_SERVER_PUBLIC_KEY
          </code>
          option.
         </p>
        </li>
        <li class="listitem">
         <p>
          For replicas, use the
          <a class="link" href="change-replication-source-to.html" title="15.4.2.2 CHANGE REPLICATION SOURCE TO Statement">
           <code class="literal">
            CHANGE
                  REPLICATION SOURCE TO
           </code>
          </a>
          statement with the
          <code class="literal">
           SOURCE_PUBLIC_KEY_PATH
          </code>
          option to
                  specify the RSA public key file, or the
          <code class="literal">
           GET_SOURCE_PUBLIC_KEY
          </code>
          option to
                  request the public key from the source. For Group
                  Replication, the
          <a class="link" href="group-replication-system-variables.html#sysvar_group_replication_recovery_public_key_path">
           <code class="literal">
            group_replication_recovery_public_key_path
           </code>
          </a>
          and
          <a class="link" href="group-replication-system-variables.html#sysvar_group_replication_recovery_get_public_key">
           <code class="literal">
            group_replication_recovery_get_public_key
           </code>
          </a>
          system variables serve the same purpose.
         </p>
        </li>
       </ul>
      </div>
      <p>
       In all cases, if the option is given to specify a valid
              public key file, it takes precedence over the option to
              request the public key from the server.
      </p>
     </li>
    </ul>
   </div>
   <p>
    For clients that use the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin, passwords are
          never exposed as cleartext when connecting to the server. How
          password transmission occurs depends on whether a secure
          connection or RSA encryption is used:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       If the connection is secure, an RSA key pair is
              unnecessary and is not used. This applies to TCP
              connections encrypted using TLS, as well as Unix
              socket-file and shared-memory connections. The password is
              sent as cleartext but cannot be snooped because the
              connection is secure.
      </p>
     </li>
     <li class="listitem">
      <p>
       If the connection is not secure, an RSA key pair is used.
              This applies to TCP connections not encrypted using TLS
              and named-pipe connections. RSA is used only for password
              exchange between client and server, to prevent password
              snooping. When the server receives the encrypted password,
              it decrypts it. A scramble is used in the encryption to
              prevent repeat attacks.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To enable use of an RSA key pair for password exchange during
          the client connection process, use the following procedure:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       Create the RSA private and public key-pair files using the
              instructions in
       <a class="xref" href="creating-ssl-rsa-files.html" title="8.3.3 Creating SSL and RSA Certificates and Keys">
        Section 8.3.3, “Creating SSL and RSA Certificates and Keys”
       </a>
       .
      </p>
     </li>
     <li class="listitem">
      <p>
       If the private and public key files are located in the
              data directory and are named
       <code class="filename">
        private_key.pem
       </code>
       and
       <code class="filename">
        public_key.pem
       </code>
       (the default values of
              the
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_private_key_path">
        <code class="literal">
         caching_sha2_password_private_key_path
        </code>
       </a>
       and
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_public_key_path">
        <code class="literal">
         caching_sha2_password_public_key_path
        </code>
       </a>
       system variables), the server uses them automatically at
              startup.
      </p>
      <p>
       Otherwise, to name the key files explicitly, set the
              system variables to the key file names in the server
              option file. If the files are located in the server data
              directory, you need not specify their full path names:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa34410336"><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">caching_sha2_password_private_key_path</span><span class="token attr-value"><span class="token punctuation">=</span>myprivkey.pem</span>
<span class="token constant">caching_sha2_password_public_key_path</span><span class="token attr-value"><span class="token punctuation">=</span>mypubkey.pem</span></code></pre>
      </div>
      <p>
       If the key files are not located in the data directory, or
              to make their locations explicit in the system variable
              values, use full path names:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa1238810"><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">caching_sha2_password_private_key_path</span><span class="token attr-value"><span class="token punctuation">=</span>/usr/local/mysql/myprivkey.pem</span>
<span class="token constant">caching_sha2_password_public_key_path</span><span class="token attr-value"><span class="token punctuation">=</span>/usr/local/mysql/mypubkey.pem</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       If you want to change the number of hash rounds used by
       <code class="literal">
        caching_sha2_password
       </code>
       during password
              generation, set the
       <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_digest_rounds">
        <code class="literal">
         caching_sha2_password_digest_rounds
        </code>
       </a>
       system variable. For example:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-ini"><div class="docs-select-all right" id="sa4681520"><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">caching_sha2_password_digest_rounds</span><span class="token attr-value"><span class="token punctuation">=</span>10000</span></code></pre>
      </div>
     </li>
     <li class="listitem">
      <p>
       Restart the server, then connect to it and check the
       <a class="link" href="server-status-variables.html#statvar_Caching_sha2_password_rsa_public_key">
        <code class="literal">
         Caching_sha2_password_rsa_public_key
        </code>
       </a>
       status variable value. The value actually displayed
              differs from that shown here, but should be nonempty:
      </p>
      <div class="copytoclipboard-wrapper">
       <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa14112582"><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">STATUS</span> <span class="token operator">LIKE</span> <span class="token string">'Caching_sha2_password_rsa_public_key'</span>\G
<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> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
Variable_name<span class="token punctuation">:</span> Caching_sha2_password_rsa_public_key
        Value<span class="token punctuation">:</span> -----BEGIN PUBLIC KEY-----
MIGfMA0GCSqGSIb3DQEBAQUAA4GNADCBiQKBgQDO9nRUDd+KvSZgY7cNBZMNpwX6
MvE1PbJFXO7u18nJ9lwc99Du/E7lw6CVXw7VKrXPeHbVQUzGyUNkf45Nz/ckaaJa
aLgJOBCIDmNVnyU54OT/1lcs2xiyfaDMe8fCJ64ZwTnKbY2gkt1IMjUAB5Ogd5kJ
g8aV7EtKwyhHb0c30QIDAQAB
-----END PUBLIC KEY-----</span></code></pre>
      </div>
      <p>
       If the value is empty, the server found some problem with
              the key files. Check the error log for diagnostic
              information.
      </p>
     </li>
    </ol>
   </div>
   <p>
    After the server has been configured with the RSA key files,
          accounts that authenticate with the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin have the
          option of using those key files to connect to the server. As
          mentioned previously, such accounts can use either a secure
          connection (in which case RSA is not used) or an unencrypted
          connection that performs password exchange using RSA. Suppose
          that an unencrypted connection is used. For example:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa50423237"><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 constant">--ssl-mode</span><span class="token attr-value"><span class="token punctuation">=</span>DISABLED</span> <span class="token property">-u</span> sha2user <span class="token property">-p</span>
Enter password<span class="token punctuation">:</span> <em class="replaceable">password</em></code></pre>
   </div>
   <p>
    For this connection attempt by
    <code class="literal">
     sha2user
    </code>
    ,
          the server determines that
    <code class="literal">
     caching_sha2_password
    </code>
    is the appropriate
          authentication plugin and invokes it (because that was the
          plugin specified at
    <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
     <code class="literal">
      CREATE USER
     </code>
    </a>
    time). The plugin finds that the connection is not encrypted
          and thus requires the password to be transmitted using RSA
          encryption. However, the server does not send the public key
          to the client, and the client provided no public key, so it
          cannot encrypt the password and the connection fails:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-simple"><div class="docs-select-all right" id="sa88229052"><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-simple">ERROR 2061 <span class="token punctuation">(</span>HY000<span class="token punctuation">)</span><span class="token operator">:</span> Authentication plugin 'caching_sha2_password'
reported error<span class="token operator">:</span> Authentication requires secure connection<span class="token punctuation">.</span></code></pre>
   </div>
   <p>
    To request the RSA public key from the server, specify the
    <a class="link" href="mysql-command-options.html#option_mysql_get-server-public-key">
     <code class="option">
      --get-server-public-key
     </code>
    </a>
    option:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa72844158"><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 constant">--ssl-mode</span><span class="token attr-value"><span class="token punctuation">=</span>DISABLED</span> <span class="token property">-u</span> sha2user <span class="token property">-p</span> <span class="token property">--get-server-public-key</span>
Enter password<span class="token punctuation">:</span> <em class="replaceable">password</em></code></pre>
   </div>
   <p>
    In this case, the server sends the RSA public key to the
          client, which uses it to encrypt the password and returns the
          result to the server. The plugin uses the RSA private key on
          the server side to decrypt the password and accepts or rejects
          the connection based on whether the password is correct.
   </p>
   <p>
    Alternatively, if the client has a file containing a local
          copy of the RSA public key required by the server, it can
          specify the file using the
    <a class="link" href="mysql-command-options.html#option_mysql_server-public-key-path">
     <code class="option">
      --server-public-key-path
     </code>
    </a>
    option:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-terminal"><div class="docs-select-all right" id="sa79796620"><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 constant">--ssl-mode</span><span class="token attr-value"><span class="token punctuation">=</span>DISABLED</span> <span class="token property">-u</span> sha2user <span class="token property">-p</span> <span class="token constant">--server-public-key-path</span><span class="token attr-value"><span class="token punctuation">=</span><em class="replaceable">file_name</em></span>
Enter password<span class="token punctuation">:</span> <em class="replaceable">password</em></code></pre>
   </div>
   <p>
    In this case, the client uses the public key to encrypt the
          password and returns the result to the server. The plugin uses
          the RSA private key on the server side to decrypt the password
          and accepts or rejects the connection based on whether the
          password is correct.
   </p>
   <p>
    The public key value in the file named by the
    <a class="link" href="mysql-command-options.html#option_mysql_server-public-key-path">
     <code class="option">
      --server-public-key-path
     </code>
    </a>
    option
          should be the same as the key value in the server-side file
          named by the
    <a class="link" href="server-system-variables.html#sysvar_caching_sha2_password_public_key_path">
     <code class="literal">
      caching_sha2_password_public_key_path
     </code>
    </a>
    system variable. If the key file contains a valid public key
          value but the value is incorrect, an access-denied error
          occurs. If the key file does not contain a valid public key,
          the client program cannot use it.
   </p>
   <p>
    Client users can obtain the RSA public key two ways:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       The database administrator can provide a copy of the
              public key file.
      </p>
     </li>
     <li class="listitem">
      <p>
       A client user who can connect to the server some other way
              can use a
       <code class="literal">
        SHOW STATUS LIKE
              'Caching_sha2_password_rsa_public_key'
       </code>
       statement
              and save the returned key value in a file.
      </p>
     </li>
    </ul>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h5 class="title">
       <a name="caching-sha2-pluggable-authentication-cache-operation">
       </a>
       Cache Operation for SHA-2 Pluggable Authentication
      </h5>
     </div>
    </div>
   </div>
   <a class="indexterm" name="idm46045246135040">
   </a>
   <a class="indexterm" name="idm46045246133952">
   </a>
   <p>
    On the server side, the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin uses an
          in-memory cache for faster authentication of clients who have
          connected previously. Entries consist of
          account-name/password-hash pairs. The cache works like this:
   </p>
   <div class="orderedlist">
    <ol class="orderedlist" type="1">
     <li class="listitem">
      <p>
       When a client connects,
       <code class="literal">
        caching_sha2_password
       </code>
       checks whether
              the client and password match some cache entry. If so,
              authentication succeeds.
      </p>
     </li>
     <li class="listitem">
      <p>
       If there is no matching cache entry, the plugin attempts
              to verify the client against the credentials in the
       <code class="literal">
        mysql.user
       </code>
       system table. If this
              succeeds,
       <code class="literal">
        caching_sha2_password
       </code>
       adds an
              entry for the client to the hash. Otherwise,
              authentication fails and the connection is rejected.
      </p>
     </li>
    </ol>
   </div>
   <p>
    In this way, when a client first connects, authentication
          against the
    <code class="literal">
     mysql.user
    </code>
    system table occurs.
          When the client connects subsequently, faster authentication
          against the cache occurs.
   </p>
   <p>
    Password cache operations other than adding entries are
          handled by the
    <code class="literal">
     sha2_cache_cleaner
    </code>
    audit
          plugin, which performs these actions on behalf of
    <code class="literal">
     caching_sha2_password
    </code>
    :
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       It clears the cache entry for any account that is renamed
              or dropped, or any account for which the credentials or
              authentication plugin are changed.
      </p>
     </li>
     <li class="listitem">
      <p>
       It empties the cache when the
       <a class="link" href="flush.html#flush-privileges">
        <code class="literal">
         FLUSH
              PRIVILEGES
        </code>
       </a>
       statement is executed.
      </p>
     </li>
     <li class="listitem">
      <p>
       It empties the cache at server shutdown. (This means the
              cache is not persistent across server restarts.)
      </p>
     </li>
    </ul>
   </div>
   <p>
    Cache clearing operations affect the authentication
          requirements for subsequent client connections. For each user
          account, the first client connection for the user after any of
          the following operations must use a secure connection (made
          using TCP using TLS credentials, a Unix socket file, or shared
          memory) or RSA key pair-based password exchange:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       After account creation.
      </p>
     </li>
     <li class="listitem">
      <p>
       After a password change for the account.
      </p>
     </li>
     <li class="listitem">
      <p>
       After
       <a class="link" href="rename-user.html" title="15.7.1.7 RENAME USER Statement">
        <code class="literal">
         RENAME USER
        </code>
       </a>
       for the
              account.
      </p>
     </li>
     <li class="listitem">
      <p>
       After
       <a class="link" href="flush.html#flush-privileges">
        <code class="literal">
         FLUSH PRIVILEGES
        </code>
       </a>
       .
      </p>
     </li>
    </ul>
   </div>
   <p>
    <a class="link" href="flush.html#flush-privileges">
     <code class="literal">
      FLUSH PRIVILEGES
     </code>
    </a>
    clears the
          entire cache and affects all accounts that use the
    <code class="literal">
     caching_sha2_password
    </code>
    plugin. The other
          operations clear specific cache entries and affect only
          accounts that are part of the operation.
   </p>
   <p>
    Once the user authenticates successfully, the account is
          entered into the cache and subsequent connections do not
          require a secure connection or the RSA key pair, until another
          cache clearing event occurs that affects the account. (When
          the cache can be used, the server uses a challenge-response
          mechanism that does not use cleartext password transmission
          and does not require a secure connection.)
   </p>
  </div>
 </div>
 <br/>
</div>
