The Ultimate Guide to High Availability with MariaDB

SET PASSWORD

You are viewing an old version of this article. View the current version here.

Syntax

SET PASSWORD [FOR user] =
    {
        PASSWORD('some password')
      | OLD_PASSWORD('some password')
      | 'encrypted password'
    }

Contents

  1. Syntax
  2. Description
  3. Example
  4. See Also

Description

The SET PASSWORD statement assigns a password to an existing MariaDB user account.

If the password is specified using the PASSWORD() or OLD_PASSWORD() function, the literal text of the password should be given. If the password is specified without using either function, the password should be the already-encrypted password value as returned by PASSWORD().

OLD_PASSWORD() should only be used if your MariaDB/MySQL clients are very old (< 4.0.0).

With no FOR clause, this statement sets the password for the current user. Any client that has connected to the server using a non-anonymous account can change the password for that account.

With a FOR clause, this statement sets the password for a specific account on the current server host. Only clients that have the UPDATE privilege for the mysql database can do this. The user value should be given in user_name@host_name format, where user_name and host_name are exactly as they are listed in the User and Host columns of the mysql.user table entry.

The argument to PASSWORD() and the password given to MariaDB clients can be of arbitrary length.

MariaDB until 10.4

SET PASSWORD (with or without PASSWORD()) only works for accounts authenticated via mysql_native_password or mysql_old_password authentication plugins

The PASSWORD() function returns a 41 character hash.

MariaDB starting with 10.4

SET PASSWORD (with or without PASSWORD()) works for accounts authenticated via any authentication plugin that supports passwords stored in the mysql.user table. In 10.4.0, this includes the ed25519, mysql_native_password, and mysql_old_password authentication plugins.

The PASSWORD() function (in the context of SET PASSWORD) returns a password hash, calculated according to the corresponding authentication plugin requirements.

See Authentication from MariaDB 10.4 for an overview of authentication changes in MariaDB 10.4.

MariaDB starting with 10.4

MariaDB 10.4 and later supports the use of multiple authentication plugins for a given user account. Not all of these authentication plugins require a password, some like the unix_socket plugin rely on other methods.

If you attempt to run SET PASSWORD on an account that uses an authentication plugin that doesn't utilize a password, MariaDB Server raises a warning. If in logging into MariaDB Server you authenticate using one of these plugins and give it your password, the Server ignores the password.

Example

For example, if you had an entry with User and Host column values of 'bob' and '%.loc.gov', you would write the statement like this:

SET PASSWORD FOR 'bob'@'%.loc.gov' = PASSWORD('newpass');

See Also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.