Blog

In this chapter, we will explain how to change a user password in MySQL with syntax and examples.

Description

The SET PASSWORD statement is used to change a user’s password in the MySQL database.

Syntax

The syntax for changing a password using the SET PASSWORD statement in MySQL is:

SET PASSWORD [ FOR user_name ] = 
 { 
     PASSWORD('plaintext_password1')
   | OLD_PASSWORD('plaintext_password2')
   | 'encrypted_password'
 };

Parameters or Arguments

FOR user_nameOptional. It is the user whose password you wish to change. If user_name is not specified, the password will be changed for the current user (see CURRENT_USER function).PASSWORD(‘plaintext_password1’)First method to set password. Uses the PASSWORD function to take the plaintext text string found in plaintext_password1 and generate a hashed password (using hashing techniques MySQL 4.1+).OLD_PASSWORD(‘plaintext_password2’)Second method to set password. Uses the OLD_PASSWORD function to take the plaintext text string found in plaintext_password2 and generate a hashed password (using hashing techniques prior to MySQL 4.1).encrypted_passwordThird method to set password. A password that is already encrypted using the authentication method for the user account that does not need to be modified any further.

Note

• The FOR user_name parameter must be specified in the format of user_name@host_name, such as ‘jane’@’localhost’.

Example

Let’s look at an example that shows how to use the SET PASSWORD statement in MySQL.

For example, if you wanted to update the user named smithj with the password autumn, you would run the following SET PASSWORD statement in MySQL:

SET PASSWORD FOR 'smithj'@'localhost' = PASSWORD('autumn');

If you wanted to reset the password using the hashing techniques prior to MySQL 4.1, you would modify the SET PASSWORD statement as follows:

SET PASSWORD FOR 'smithj'@'localhost' = OLD_PASSWORD('autumn');

If the new password was already encrypted, you could use the SET PASSWORD statement as follows:

SET PASSWORD FOR 'smithj'@'localhost' = '*0886644237EED5C45BE221093802B5AB0C06D2D0';

Next Topic : Click Here