In MySQL, the user account password can be changed using 3 different statements:
- UPDATE statement
- SET PASSWORD statement
- ALTER USER statement.
But before changing the password of an account, two very important things should be kept in mind:
- The user account details for which you want to change the password.
- The application is being used by the user whose password you are going to change because if the password is changed without changing the connection string of the application then the application will not be able to connect to the database server.
Let us now learn about how to change the user password in SQL using the three above mentioned SQL statements:
Changing MySQL User Password Using The SET PASSWORD Statement: To change the user password using the SET PASSWORD statement the first requirement is that the account needs to have at least UPDATE privilege.
The user account should be in “[email protected]” format whose password you want to update.
To change the password of a user account “gfguser1” to “newpass” using the SET PASSWORD statement, the following code statement should be executed:
Changing MySQL User Password Using The ALTER USER statement: The second way to change the password for a user account is to use the ALTER USER statement.
The ALTER USER statement is used along with the “IDENTIFIED BY” clause.
To change the password for the user account “gfguser1” to “newpass” using the Alter User statement, syntax is as shown below:
Changing MySQL User Password Using UPDATE Statement: The third way to change the password of a user account is by using the UPDATE statement.
The Update statement updates the user table of the mysql database.
The FLUSH PRIVILEGES statement needs to be executed after executing the UPDATE Statement.
The FLUSH PRIVILEGES statement is used to reload privileges from the grant table in the mysql database.
To change the password for the user account “gfguser1” to “newpass” that connects from the localhost server, syntax is as shown below: