MariaDB - Change a user password

The MariaDB SET PASSWORD statement is used to change the password of a user in the MariaDB database.

Syntax

The syntax of changing password using SET PASSWORD statement in MariaDB is given below:

SET PASSWORD [FOR user_name] = 
  {
    PASSWORD('password1')
    | OLD_PASSWORD('password2')
    | 'encrypted_password'
  }

Parameters

`FOR user_name`	`Optional.` Specify the user for which the password need to be changed. If not provided, the password will be changed for the current user (see CURRENT_USER() function).
`PASSWORD('password1')`	First method uses the PASSWORD() function to take the plaintext text string found in password1 and generate a hashed password (using newer hashing techniques).
`OLD_PASSWORD('password2')`	Second method uses the OLD_PASSWORD() function to take the plaintext text string found in password2 and generate a hashed password (using older hashing techniques).
`encrypted_password`	Third method takes a password that is already encrypted using the authentication method for the user account that does not need to be modified any further.

Example: Changing password for current user

In the example below, the SET PASSWORD statement is used to change the password for current user to 'password123'.

SET PASSWORD = PASSWORD('password123');

Example: Changing password by account name

In the example below, the password of account named 'john'@'localhost' is changed to 'password123'. It uses the PASSWORD() function to generate a hashed password.

SET PASSWORD 
  FOR 'john'@'localhost' = PASSWORD('password123');

To hash the password using OLD_PASSWORD() function, the following statement can be used:

SET PASSWORD 
  FOR 'john'@'localhost' = OLD_PASSWORD('password123');

If the new password is already encrypted, the SET PASSWORD statement can be used in the following way:

SET PASSWORD 
  FOR 'john'@'localhost' = '*A0F874BC7F54EE086FCE60A37CE7887D8B31086B';