MySQL Lock User Account

In this article, we will explore how we can lock an account during creation or how to use the alter command to lock an already existing account.

Let’s jump in.

Lock User Account During Creation

There are two main methods of locking an account in MySQL. The first is during account creation as shown in the following command syntax:

Once an account is locked, MySQL stores the entry in the mysql.user system’s table. The account_locked column stores the locked state with Y representing the locked account and N representing an unlocked account.

Lock an Already Existing Account

The second method to lock an account in MySQL is using the ALTER command. This allows you to lock an already existing account as shown in the following syntax:

Example 1: Create a Locked Account

The following example shows how to create a locked account using the MySQL create user statement.

We can then verify the account locked status as shown in the following:

The previous query should return the available users and their locked state as shown in the following output:

We can see from the account_locked column that the “linuxhint” user is locked.

Keep in mind that attempting to login into a locked account results in an error as shown in the following:

Example 2: Lock an Existing Account

The following example illustrates how to lock an existing account using the ALTER USER command.

Start by creating an account as follows:

Next, lock the account with the following command:

The previous command should lock the specified account.

You can also check the number of login attempts of a locked account with the following command:

Example output:

To unlock an account, run the following command:

The command changes the locked state of a specified account to unlock.

Conclusion

In this post, we discussed how to lock and unlock the accounts in MySQL during the user creation or in an existing account.

Thanks for reading!