MySQL Account Manipulation

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 - Y indicates the account is locked, and N 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!