Introduction to MariaDB change user password
MariaDB has a secure installation shell script that is available on the UNIX system. MariaDB change user password is a most important concept, in which we can set a new password to the specified user, we are also able to update password of specified user. If we need to login with a secure environment in MariaDB at that time we must have remote access as an authorized user on MariaDB server. By default a MariaDB has a anonymous user that means it allowing everyone to login into the MariaDB without having any account, but this is only for testing purpose, but when we see security point of view then it is not to good, so we can set password to the specified user and update it time to time for security reason.
Syntax
SET PASSWORD [specified user name] = {
PASSWORD(‘specified_password in plaintext’) OLD PASSWORD(‘specified_password in plaintext’) ‘’
} ;
Explanation
In the above syntax we used the set password command to set the new password to the MariaDB user. In which specified user name means the user whose password we want to change, if we don’t mention the username name then password will be changed for current active user that is specified user name is optional part of this syntax. After that we mentioned the specified password in plaintext means new password that we need to set and old password is used to check user is authorized or not and last part of syntax contains the encrypted password that means password is already encrypted by using authentication method for the user account.
How to change user password in MariaDB?
Let’s see how we can change user passwords in MariaDB as follows.
Sometimes we forget the password or lose the password. At that time we need to reset the user password to gain access to MariaDB database and server.
There are different ways to reset the user password as follows.
First we will see how we can recover the forgotten password, steps as follows.
1. First we need to identify the database version. Most of the version of MariaDB is compatible with MySQL but depending on the database version we need the different commands to identify the database version, as below.
mysql --version
Explanation
With the help of the above command we see the current installed version of MariaDB.
2. In the second step we need to stop the database server. When we need to change the user password at that time we must need to stop database server by using following command as follows.
systemctl stop mariadb
Explanation
After execution of the above command database server will be stopped and we are able to reset password manually for users.
3. Restart Database server without any permission. When we run MariaDB without any permission or privileges, then it will allow us to access the database with root privileges without any password or without any permission. So that reason we need to stop the database, which stores the user information that is in the grant table.
4. Change user Password. After restarting the database server we are able to change the user password by using set password command, sometimes this command is not working due to grant table loading problem, so we can load grant table by using FLUSH PRIVILEGES command.
We can also use the Alter user command to change user password in which we can directly change the user password by following the above mentioned step.
In the second method we can use a set password command to change the user password.
Examples
Now let’s see a different example of changing the user password as follows.
SET PASSWORD FOR 'Jeny'@'localhost' = PASSWORD('king123');
Explanation
In the above example we use the set password command to change the password of the user, in this example the username is Jeny and here we set new password king123 to the Jeny user as shown in above statement. Final out of above statement we illustrate by using the following screenshot.
Example
Suppose the user needs to reset password by using the old password method. At that time we can use the following statement as follows.
SET PASSWORD FOR 'Jeny'@'localhost' = OLD_PASSWORD('Jeny123');
Explanation
In the above example we use the set password command with the old_password hashing method, here Jeny is the user that we need to update and Jeny123 is the new password that we need to reset. See in this example we use the old_password hashing function. It is used by authentication systems to generate the hash password from plaintext using hashing technique. Final out of the above statement we illustrate by using the following screenshot.
Now let’s see another method to change password of user as follows.
In this method we can use the ALTER USER command to change the user password. In this type we don’t need extra privileges to change the user password means we can directly change the password without any permission.
Syntax
alter user ‘specified user name’@localhost’ identified by ‘new specific password’;
Explanation
In above syntax we use alter user command to change the user password, here we mentioned the specified user name with local environment within a single quote followed by identified keyword and the new password that we need to set, as shown in above syntax.
ALTER USER 'Jeny'@'localhost' IDENTIFIED BY 'Jeny12345';
Explanation
In the above example we use the alter user command to reset the password of the user, here we mentioned the user name is Jeny with local environment name, after that we use identified by key to set a new password to the Jeny user as shown in above statement. Final out of the above statement we illustrate by using the following screenshot.
After that we need to flush all privileges by using the following command.
FLUSH PRIVILEGES;
Conclusion
We hope from this article you have understood about the MariaDB change user password. From this article we have learned the basic syntax of changing user passwords and we also see different examples of changing user passwords. From this article we learned how and when we use MariaDB to change user passwords.
Recommended Articles
This is a guide to MariaDB change user password. Here we discuss the basic syntax of changing user passwords and we also see different examples. You may also have a look at the following articles to learn more –
