Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
Changing a User Password
2.1.
UPDATE Command
2.2.
SET PASSWORD Command
2.3.
ALTER USER Command
3.
Locking a User Account
3.1.
Locking a New Account
3.2.
Locking an Existing Account
4.
Unlocking a User Account
5.
FAQs
6.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Account Manipulation

Author Pakhi Garg
0 upvote
Leveraging ChatGPT - GenAI as a Microsoft Data Expert
Speaker
Prerita Agarwal
Data Specialist @
23 Jul, 2024 @ 01:30 PM

Introduction

You must now be familiar with MySQL, its features and its functionality. In this blog, we will be exploring a new component of MySQL, which is, manipulating a user account. A user account in MySQL contains many things like the username, password, information of the host and user privileges. MySQL offers many ways for manipulating a user account like-

  1. Changing a user password.
  2. Locking a user account.
  3. Unlocking a user account.

This blog will discuss all these ways one by one.

So, let’s get started.

Changing a User Password

Sometimes, a situation arises where we need to change a user password. For changing a user's password, we must have some information about the user, like

  • The user account's username.
  • The name of the application which that user account is using.
    The application’s name is required because we must connect to that application for changing the user password. If not connected, then the application will not be able to connect with the database server.

In MySQL, there are three ways of changing a user password. These are-

  1. UPDATE Command.
  2. SET PASSWORD Command.
  3. ALTER USER Command.

Let’s discuss them all one by one.

UPDATE Command

The first way to change a user password is to use the UPDATE command. This command updates the user table of the MySQL database.  

Before executing the UPDATE command, we must connect to the user's application (in this case, MySQL). After executing the UPDATE command, we must execute another command, i.e., the FLUSH PRIVILEGES command to reload the user's privileges from the grant table of the MySQL database.

Below is the syntax to use the UPDATE command-

USE application_name;
UPDATE user SET authentication_string = PASSWORD(‘new password’) WHERE user = ‘username’ AND host = ‘hostname’;
FLUSH PRIVILEGES;

where,

application_name is the name of the application the user is using.

new password is the new password that we want to set for a user.

username is the username of the user account.

hostname is the name of the host.

Let’s take a look at an example of the UPDATE command.

Suppose we have a user ‘ninja’ and host as ‘localhost’ using the MySQL application. We want to change the user ninja’s password to ‘iamninja’. We will execute the following query to do so-

mysql> USE mysql;
mysql> UPDATE user SET authentication_string = PASSWORD('iamninja') WHERE user = 'ninja' AND host = 'localhost';
mysql> FLUSH PRIVILEGES;

The above query will change the user ninja’s password.

SET PASSWORD Command

The second way to change a user password is to use the SET PASSWORD command. For using this command, we must have an UPDATE privilege. We don’t need to connect to the application the user is using or execute the FLUSH PRIVILEGES command in this command.

Below is the syntax to use the SET PASSWORD command-

SET PASSWORD FOR username@hostname = PASSWORD(‘new password’);

where,

username is the username of the user account.

hostname is the name of the host.

new password is the new password that we want to set for a user.

Let’s take a look at an example of the SET PASSWORD command.

Suppose we have a user ‘ninja’ and a host as ‘localhost’. We want to change the user ninja’s password to ‘codingninjas’. We will execute the following query to do so-

mysql> SET PASSWORD FOR ninja@localhost = PASSWORD('codingninjas');

MySQL deprecated this syntax in version 5.7.6 and may remove it in future releases. Instead, it employs the following plaintext password:

mysql> SET PASSWORD FOR ninja@localhost = codingninjas;

The above query will change the user ninja’s password.

ALTER USER Command

The third way to change a user password is to use the ALTER USER command along with the IDENTIFIED BY clause.

Below is the syntax to use the  ALTER USER command-

ALTER USER username@hostname IDENTIFIED BY ‘new password’;

where,

username is the user account's username.

hostname is the name of the host.

new password is the new password that we want to set for a user.

Let’s take a look at an example of the ALTER USER command.

Suppose we have a user ‘ninja’ and a host as ‘localhost’. We want to change the user ninja’s password to ‘ilovecoding’. We will execute the following query to do so-

mysql> ALTER USER ninja@localhost IDENTIFIED BY 'ilovecoding';

The above query will change the user ninja’s password.

Note: If we want to reset the password for the MySQL root account, we need to force the MySQL database server to stop and restart without using grant table validation.

Next, we will study how to lock a user account.

Get the tech career you deserve, faster!
Connect with our expert counsellors to understand how to hack your way to success
User rating 4.7/5
1:1 doubt support
95% placement record
Akash Pal
Senior Software Engineer
326% Hike After Job Bootcamp
Himanshu Gusain
Programmer Analyst
32 LPA After Job Bootcamp
After Job
Bootcamp

Locking a User Account

If we want to protect a user account from other users, we can put a lock on a user account. By default, when a user is created, his account is in unlock state. For locking an account, we use the ACCOUNT LOCK command.

There are two methods of locking a user account. These are-

  1. Locking a new account.
  2. Locking an existing account.

We will discuss both these methods.

Locking a New Account

If we create a new user account, we can lock it handheld. First, we need to create a new user using the CREATE USER command and execute the ACCOUNT LOCK command. 

Below is the syntax of locking a new account-

CREATE USER username@hostname IDENTIFIED BY ‘password’ ACCOUNT LOCK;

where,

username is the username of the user account.

hostname is the name of the host.

password is the password for this account.

Let’s take an example to understand this.

We will create a new user ‘ninja1’, hostname ‘localhost’ and password ‘iamninja’. Then we will lock this account.

mysql> CREATE USER ninja1@localhost IDENTIFIED BY 'iamninja' ACCOUNT LOCK;

The above query will lock the user ninja1’s account. 

We can also check whether an account is locked or not. There is an account_locked column in the user table of the MySQL database. This column can contain two values - indicates the account is locked, and indicates the account is not locked.

Now, we will check whether the user ninja1’s account is locked or not using the following query-

mysql> SELECT user, host, account_locked FROM mysql.user WHERE user = 'ninja1' and host = 'localhost';

The output of the above query will be-

Now, we will see how to lock an already created user account.

Locking an Existing Account

We can also lock an already existing user account using the ALTER USER and ACCOUNT LOCK command.

Below is the syntax of locking an existing account-

ALTER USER username@hostname ACCOUNT LOCK;

where,

username is the user account's username.

hostname is the name of the host.

Let’s take an example to understand this.

First, we will create a new user with username ‘ninja2’, hostname ‘localhost’ and password ‘iamninja’ using the following command.

mysql> CREATE USER ninja2@localhost IDENTIFIED BY 'iamninja';

 Now, we will lock the user ninja2’s account using the following query-

mysql> ALTER USER ninja2@localhost ACCOUNT LOCK;

The user ninja2’s account is locked. We can check this by checking the value of the account_locked column of the user table in the MySQL database.

mysql> SELECT user, host, account_locked FROM mysql.user WHERE user = 'ninja2' and host = 'localhost';

The output of the above query will be-

Note: If someone tries to log into a locked account, we can check the number of attempts made by that person to log in using the following command-

mysql> SHOW GLOBAL STATUS LIKE 'Locked_connects';

Now, we will see how to unlock a user account.

Unlocking a User Account

Just like we have locked a user account, we can also unlock it in MySQL. We can unlock an existing user's account using the ALTER USER and the ACCOUNT UNLOCK command.

Below is the syntax of unlocking a user account-

ALTER USER username@hostname ACCOUNT UNLOCK;

where,

username is the user account's username.

hostname is the name of the host.

Let’s take an example to understand this.

Since we have created a user ninja1 and locked his account, now we will unlock his account using the following query-

mysql> ALTER USER ninja1@localhost ACCOUNT UNLOCK;

We can check whether the account is locked or not by checking the value of the account_locked column of the user table of the MySQL database.

mysql> SELECT user, host, account_locked FROM mysql.user WHERE user = 'ninja1' and host = 'localhost';

The output of the above query will be-

FAQs

  1. What are the different ways of changing a user’s password?
    The different ways of changing a user’s password are using the UPDATE command, the SET PASSWORD command, and the ALTER USER command.
     
  2. Why do we need to use the FLUSH PRIVILEGES command while using the UPDATE command to change a user’s password?
    We need to use the FLUSH PRIVILEGES command while using the UPDATE command to change a user’s password to reload the user's privileges from the grant table of the MySQL database.
     
  3. What is the difference between the ACCOUNT LOCK and the ACCOUNT UNLOCK command?
    The ACCOUNT LOCK command is used to lock a user’s account. It can be used for a new user and an already existing user, while the ACCOUNT UNLOCK command is used to unlock a user’s account and can only be used for existing users.
     
  4. How can we know whether a user account is locked?
    We can know whether an account is locked by checking the value of the account_locked column present in the user table of the MySQL database.
     
  5. How can we check if a user has logged in to a locked account or not?
    We can check the number of attempts some users make to log into a locked account using the following query-
mysql> SHOW GLOBAL STATUS LIKE 'Locked_connects';

Key Takeaways

In this blog, we talked about account manipulation in MySQL. There are three account manipulation techniques: changing a user password and locking and unlocking an account.

Attention Ninja!!!!! Don’t stop here. Start your DBMS journey with the DBMS course on Coding Ninjas. To master SQL, solve our Top 100 SQL Problems asked in various company interviews.

Happy Learning!

Live masterclass