MySQL User Password

Definition of MySQL User Password

MySQL User Password command in MySQL is responsible to modify the user password by applying several query statements like SET PASSWORD, ALTER USER, GRANT USAGE, and UPDATE. But we need to consider a few significant factors before we perform the password change of a user account in MySQL server: For which user account you require to change the password in MySQL?

What application the user account is using whose password will be changed? When the password is changed without modifying the application’s connection string that is used for the user account then the application may not be capable to establish a connection to the database server. Only after confirming all these factors, you can proceed further to change the password of the user account in MySQL.

How does Password Work in MySQL?

• MySQL database is provided as open-source server software that is essential to manage the data records in the form of tables. A user can easily organize, insert, update, delete, store, and retrieve data later. A user need to have specific access and privileges to make any changes in the database and its associated tables. To make our server restricted for others we use passwords during login mode to protect out data records.
• We use this query whenever we forget or want to change the previous password or even when we have never set the password for root user in our database. In this case, MySQL will be running anywhere else in the data center.We can follow some steps through which we will never get loss for the password used in the database root user.
• Suppose, when the user is checking for any security game and we do not remember the password for root because the original password might be something complex or far than simple.
• By default, the username provided in MySQL is the root and no password is applied, its left empty for access to database server. If in the process of installation, you have added a password unintentionally and cannot find now, then we need to change or reset the password.
• On the basis of MariaDB or MySQL server version that is running on your system, we can use many commands to modify user passwords.You can do so by logging in to the MySQL shell command console as root. If you have not provided a password then, by default leave it empty, and if you have got a password then login in with it.

How to Create a Password for the User in MySQL?

Let us illustrate some methodologies to create a password for the user account with some examples demonstrated below:

1. with UPDATE Query

We will discuss here the initial step to change the user password using the UPDATE statement that updates the table of the MySQL database user. But remember that after we have executed the UPDATE command we also need to run the FLUSH PRIVILEGES command that will be required to reload privileges from the MySQL database table that is granted.

Suppose for a user named mysqladmin that is connected from the localhost,you want to change the password to MyAdmin, then we will use the following query:

USE mysql;
UPDATE user
SET PASSWORD = PASSWORD(‘myadmin’)
WHERE user = ‘mysqladmin’ AND host = ‘localhost’;
FLUSH PRIVILEGES;

We should note that from MySQL 5.7.6, only the authentication_string column is used by the user table to save the password. Additionally, it has uninvolved the password column then. Hence,if MySQL 5.7.6+ is used then in the UPDATE statement we should apply the authentication_stringcolumn instead shown as follows:

USE mysql;
UPDATE user
SET authentication_string= PASSWORD(‘myadmin’)
WHERE user = ‘mysqladmin’ AND host = ‘localhost’;
FLUSH PRIVILEGES;

We need to know that the function PASSWORD() calculates the hash value from a natural text value.

2. with SET PASSWORD Query

Secondly, we can change the user password in MySQL with SET PASSWORD methodology.To modify a password,we need to implement a user account in user@hostlayout. The user account should hold at least UPDATE privilege if we want to change the password for another account. Using SET PASSWORD query statement, we do not need to run the FLUSH PRIVILEGES command to reload rights from the grant tables.For this we have an example below as previous to change the user password:

SET PASSWORD FOR ‘mysqladmin’@’localhost’ = PASSWORD (‘myadmin’);

But from MySQL 5.7.6 version, MySQl has devalued this syntax and it may not includein the future releases. Thus, it implements the plaintext password rather as follows:

SET PASSWORD FOR ‘mysqladmin’@’localhost’ = myadmin;

3. Change with ALTER USER Query

This is the third method to modify a user password using the statement ALTER USER in MySQL. Here, we will write the ALTER USER command along with the clause as IDENTIFIED BY. Let us view the succeeding query using ALTER USER that will change the user password from old to new:

ALTER USER mysqladmin@localhostIDENTIFIED BY ‘myadmin’;

Here the user mysqladmin has changed its password to myadmin on query execution.

How to Delete a Password for the User?

• In case, if the user need to reset or delete the password related to the MySQL user root account then the user requires to force the database MYSQL server to discontinue and without applying the grant table authentication you need to restart localhost.
• This means if you will login to your user account using SSH(command prompt), you need to stop MySQL using the proper command, next restart the server with skipping the grant table options.
• Afterwards you can again set your user account password for the MySQL database host using the UPDATE command statement.

Conclusion

• It supports various query commands which can be implemented in the server to execute the user password changes including the MySQL statements likeSET PASSWORD, UPDATE, or GRANT USAGE.
• This helps in server security implementation and maintenance process to keep our databases secure, updated, and storing the backups. Also, for user privileges safety it may be necessary to apply this User password command to a user account in an application.

Recommended Articles

This is a guide to MySQL User Password. Here we also discuss the introduction to MySQL User Password, how does password work, how to delete a password for the user. You may also have a look at the following articles to learn more –

1. MySQL Offset
2. MySQL Average
3. ALTER Column in MySQL 
4. MySQL ALL