<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="assigning-passwords">
      </a>
      8.2.14 Assigning Account Passwords
     </h3>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045248743216">
  </a>
  <a class="indexterm" name="idm46045248742176">
  </a>
  <a class="indexterm" name="idm46045248741104">
  </a>
  <a class="indexterm" name="idm46045248739616">
  </a>
  <p>
   Required credentials for clients that connect to the MySQL server
      can include a password. This section describes how to assign
      passwords for MySQL accounts.
  </p>
  <p>
   MySQL stores credentials in the
   <code class="literal">
    user
   </code>
   table in
      the
   <code class="literal">
    mysql
   </code>
   system database. Operations that
      assign or modify passwords are permitted only to users with the
   <a class="link" href="privileges-provided.html#priv_create-user">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   privilege, or,
      alternatively, privileges for the
   <code class="literal">
    mysql
   </code>
   database (
   <a class="link" href="privileges-provided.html#priv_insert">
    <code class="literal">
     INSERT
    </code>
   </a>
   privilege to
      create new accounts,
   <a class="link" href="privileges-provided.html#priv_update">
    <code class="literal">
     UPDATE
    </code>
   </a>
   privilege to modify existing accounts). If the
   <a class="link" href="server-system-variables.html#sysvar_read_only">
    <code class="literal">
     read_only
    </code>
   </a>
   system variable is
      enabled, use of account-modification 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>
   or
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   additionally requires
      the
   <a class="link" href="privileges-provided.html#priv_connection-admin">
    <code class="literal">
     CONNECTION_ADMIN
    </code>
   </a>
   privilege (or
      the deprecated
   <a class="link" href="privileges-provided.html#priv_super">
    <code class="literal">
     SUPER
    </code>
   </a>
   privilege).
  </p>
  <p>
   The discussion here summarizes syntax only for the most common
      password-assignment statements. For complete details on other
      possibilities, see
   <a class="xref" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    Section 15.7.1.3, “CREATE USER Statement”
   </a>
   ,
   <a class="xref" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    Section 15.7.1.1, “ALTER USER Statement”
   </a>
   , and
   <a class="xref" href="set-password.html" title="15.7.1.10 SET PASSWORD Statement">
    Section 15.7.1.10, “SET PASSWORD Statement”
   </a>
   .
  </p>
  <p>
   MySQL uses plugins to perform client authentication; see
   <a class="xref" href="pluggable-authentication.html" title="8.2.17 Pluggable Authentication">
    Section 8.2.17, “Pluggable Authentication”
   </a>
   . In password-assigning
      statements, the authentication plugin associated with an account
      performs any hashing required of a cleartext password specified.
      This enables MySQL to obfuscate passwords prior to storing them in
      the
   <code class="literal">
    mysql.user
   </code>
   system table. For the statements
      described here, MySQL automatically hashes the password specified.
      There are also syntax for
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE
      USER
    </code>
   </a>
   and
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   that
      permits hashed values to be specified literally. For details, see
      the descriptions of those statements.
  </p>
  <p>
   To assign a password when you create a new account, use
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   and include an
   <code class="literal">
    IDENTIFIED BY
   </code>
   clause:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa78048932"><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">'jeffrey'</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>
   <a class="link" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    <code class="literal">
     CREATE USER
    </code>
   </a>
   also supports syntax
      for specifying the account authentication plugin. See
   <a class="xref" href="create-user.html" title="15.7.1.3 CREATE USER Statement">
    Section 15.7.1.3, “CREATE USER Statement”
   </a>
   .
  </p>
  <p>
   To assign or change a password for an existing account, use the
   <a class="link" href="alter-user.html" title="15.7.1.1 ALTER USER Statement">
    <code class="literal">
     ALTER USER
    </code>
   </a>
   statement with an
   <code class="literal">
    IDENTIFIED BY
   </code>
   clause:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa30278216"><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">'jeffrey'</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>
   If you are not connected as an anonymous user, you can change your
      own password without naming your own account literally:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa6909271"><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 function">USER</span><span class="token punctuation">(</span><span class="token punctuation">)</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>
   To change an account password from the command line, use the
   <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
    <span class="command">
     <strong>
      mysqladmin
     </strong>
    </span>
   </a>
   command:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting one-line language-terminal"><div class="docs-select-all right" id="sa27971734"><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">mysqladmin <span class="token property">-u</span> <em class="replaceable">user_name</em> <span class="token property">-h</span> <em class="replaceable">host_name</em> password <span class="token atrule">"<em class="replaceable">password</em>"</span></code></pre>
  </div>
  <p>
   The account for which this command sets the password is the one
      with a row in the
   <code class="literal">
    mysql.user
   </code>
   system table that
      matches
   <em class="replaceable">
    <code>
     user_name
    </code>
   </em>
   in the
   <code class="literal">
    User
   </code>
   column and the client host
   <span class="emphasis">
    <em>
     from
      which you connect
    </em>
   </span>
   in the
   <code class="literal">
    Host
   </code>
   column.
  </p>
  <div class="warning" style="margin-left: 0.5in; margin-right: 0.5in;">
   <div class="admon-title">
    Warning
   </div>
   <p>
    Setting a password using
    <a class="link" href="mysqladmin.html" title="6.5.2 mysqladmin — A MySQL Server Administration Program">
     <span class="command">
      <strong>
       mysqladmin
      </strong>
     </span>
    </a>
    should be
        considered
    <span class="emphasis">
     <em>
      insecure
     </em>
    </span>
    . On some systems, your
        password becomes visible to system status programs such as
    <span class="command">
     <strong>
      ps
     </strong>
    </span>
    that may be invoked by other users to
        display command lines. MySQL clients typically overwrite the
        command-line password argument with zeros during their
        initialization sequence. However, there is still a brief
        interval during which the value is visible. Also, on some
        systems this overwriting strategy is ineffective and the
        password remains visible to
    <span class="command">
     <strong>
      ps
     </strong>
    </span>
    . (SystemV Unix
        systems and perhaps others are subject to this problem.)
   </p>
  </div>
  <p>
   If you are using MySQL Replication, be aware that a password used
      by a replica as part of
   <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>
   is effectively limited to 32 characters in
      length; if the password is longer, any excess characters are
      truncated. This is not due to any limit imposed by MySQL Server
      generally, but rather is an issue specific to MySQL Replication.
  </p>
 </div>
 <br/>
</div>
