MySQL - SET PASSWORD Statement

---

---

MySQL SET PASSWORD Statement

You can password to an existing user using the MySQL SET PASSWORD Statement. Using this you can

• Set a specific password to a user account.
• Set a random password.
• Replace the current password with a new one.
• Retain the current password as its secondary password.

Syntax

Following is the syntax of the MySQL SET PASSWORD Statement −

SET PASSWORD [FOR user_name] new_password
   [REPLACE 'current_password_string']
   [RETAIN CURRENT PASSWORD]

Where, user_name is the name of the user for which you need to change the password.

Example

Assume we have created a user named sample (without any password) using the CREATE USER Statement as shown below −

CREATE USER 'tp'@'localhost';

To login as the above created user. Open command prompt, browse through the bin folder of MySQL installation folder and execute the command mysql -u user_name -p as −

MySQL_Installation_Directorybin>mysql -u tp
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 35
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. 
All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the 
current input statement. MySQL SET PASSWORD Statement

Now you can assign a password of the current user account as shown below −

SET PASSWORD ='mypassword123';

Setting password for a specific user

You set/assign password to a specific user using the FOR clause in the SET PASSWORD Statement −

SET PASSWORD FOR user_name password_string

Example

Assume we have created a user named test using the CREATE USER statement.

CREATE USER 'test'@'localhost';

Following query assigns password for the above created user −

SET PASSWORD FOR 'test'@'localhost' = 'mypassword';

Replacing existing password

You can replace the existing password using the REPLACE clause along with the SET PASSWORD statement.

SET PASSWORD = new_password REPLACE old_password;

Example

Assume we have created a user named test along with a password, using the CREATE USER statement.

CREATE USER test@localhost IDENTIFIED BY 'test12345';

Login as the above created user. (Open command prompt, browse through the bin folder of MySQL installation folder and execute the following query) −

MySQL_Installation_Directorybin>mysql -u tp -p
*********

Following query replaces the password of the above created user −

SET PASSWORD = 'newpassword' REPLACE 'test12345';

You can use REPLACE only for the current user

Assigning a random password

You can assign random password using the RANDOM clause along with the SET PASSWORD Statement.

SET PASSWORD TO RANDOM;

Example

Assume we have created a user named sample (without any password) using the CREATE USER Statement as shown below −

CREATE USER 'tp'@'localhost';

Following query sets a random password of the above created user −

SET PASSWORD FOR 'tp'@'localhost' TO RANDOM;

Output

Following is the output of the above program −

user	host	generated password
tp	localhost	hTWnSP:h0eAe(ywVJArm