How to Lock User Account in MySQL

Sometimes you may need to lock user account in MySQL, for security purposes. These are typically required by database administrators who want to block access to certain users, to secure their data. There are two ways to do this. Either you can lock user account when they are newly created or lock an existing account. In both cases, once a database user account is locked, they cannot log into database. In this article, we will learn how to lock user account in MySQL.

How to Lock User Account in MySQL

If you need to lock a user account, you can add ACCOUNT LOCK clause to your CREATE USER statement as shown below.

CREATE USER username
IDENTIFIED BY 'password'
ACCOUNT LOCK;

For example, if you want to lock a user test_user which has password test_password, use the following command.

CREATE USER test_user
IDENTIFIED BY 'test_password'
ACCOUNT LOCK;

The above statement will create new user which is locked by default. If you want to lock an existing user, you need to add ACCOUNT LOCK clause to alter user statement.

ALTER USER username
IDENTIFIED BY 'password'
ACCOUNT LOCK;

Here is an example to lock existing user account test_user.

ALTER USER test_user
IDENTIFIED BY 'test_password'
ACCOUNT LOCK;

Once the account is locked, the user will not be able to log into MySQL.

$ mysql -u test_user
Enter password: *****
ERROR 3118 (HY000): Access denied for user 'test_user'@'localhost'. Account is locked.

Every MySQL database system has a mysql.user table with column account_locked, which can have Y or N value to indicate the account is locked or not.

If you want to view the account status of a given user test_user, you need to log into MySQL and run the following query.

SELECT 
    user, host, account_locked
FROM
    mysql.user
WHERE
    user = 'test_user' AND 
    host='localhost';

You will see the following kind of output.

user      | host      | account_locked
test_user | localhost | Y

The Locked_connects status variable shows the number of attempts to connect to MySQL server. You can view this information using the following query.

SHOW GLOBAL STATUS 
LIKE 'Locked_connects';

In this article, we have learnt how to lock user account in MySQL. You can run these commands from MySQL console, or from within your applications, via database connectors, to lock user accounts.

Also read:

How to Create Cartesian Product of Lists in Python
How to Disable Triggers in PostgreSQL
Convert Text File to Excel Using Shell Script
How to Move Directory to Another Git Repository
How to Move Directory to Another Partition in Linux