SET PASSWORD
Syntax
SET PASSWORD [FOR user] = { PASSWORD('some password') | OLD_PASSWORD('some password') | 'encrypted password' }
Contents
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 (or view in MariaDB-10.4 onwards) entry.
The argument to PASSWORD()
and the password given to MariaDB clients can be of arbitrary length.
Authentication Plugin Support
SET PASSWORD
(with or without PASSWORD()
) works for accounts authenticated via any authentication plugin that supports passwords stored in the mysql.global_priv
table.
The ed25519
, mysql_native_password
, and mysql_old_password
authentication plugins store passwords in the mysql.global_priv
table.
If you run SET PASSWORD
on an account that authenticates with one of these authentication plugins that stores passwords in the mysql.global_priv
table, then the PASSWORD()
function is evaluated by the specific authentication plugin used by the account. The authentication plugin hashes the password with a method that is compatible with that specific authentication plugin.
The unix_socket
, named_pipe
, gssapi
, and pam
authentication plugins do not store passwords in the mysql.global_priv
table. These authentication plugins rely on other methods to authenticate the user.
If you attempt to run SET PASSWORD
on an account that authenticates with one of these authentication plugins that doesn't store a password in the mysql.global_priv
table, then MariaDB Server will issue an error like the following:
SET PASSWORD is ignored for users authenticating via unix_socket plugin
See Authentication from MariaDB 10.4 for an overview of authentication changes in MariaDB 10.4.
MariaDB until 10.3
In MariaDB 10.3 and before, SET PASSWORD
(with or without PASSWORD()
) only works for accounts authenticated via mysql_native_password
or mysql_old_password
authentication plugins
Passwordless User Accounts
User accounts do not always require passwords to login.
The unix_socket
, named_pipe
and gssapi
authentication plugins do not require a password to authenticate the user.
The pam
authentication plugin may or may not require a password to authenticate the user, depending on the specific configuration.
The mysql_native_password
and mysql_old_password
authentication plugins require passwords for authentication, but the password can be blank. In that case, no password is required.
If you provide a password while attempting to log into the server as an account that doesn't require a password, then MariaDB server will simply ignore the password.
A user account can be defined to use multiple authentication plugins in a specific order of preference. This specific scenario may be more noticeable in these versions, since an account could be associated with some authentication plugins that require a password, and some that do not.
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');
If you want to delete a password for a user, you would do:
SET PASSWORD FOR 'bob'@localhost = PASSWORD("");
See Also
- Password Validation Plugins - permits the setting of basic criteria for passwords
- ALTER USER