A lock is a mechanism used to prevent unauthorized modifications into our database. It is essential to the security of our database. In this article, we are going to learn how to use the CREATE USER… ACCOUNT LOCK and ALTER TABLE… ACCOUNT LOCK statements for locking the user accounts in the MySQL server.
We can lock the user accounts by using the CREATE USER… ACCOUNT LOCK statement as follows:
CREATE USER account_name IDENTIFIED BY 'password' ACCOUNT LOCK; The ACCOUNT LOCK clause specifies the initial locking state for a new user account. If we do not specify this clause with the statement, then a newly created user is stored in an unlocked state by default. If we have enabled the validate_password plugin during user creation, we cannot create an account without a password, even if it is locked.
MySQL also allows us to provide the lock on an existing user account by using the ALTER USER… ACCOUNT LOCK statement as follows:
ALTER USER account_name IDENTIFIED BY 'password' ACCOUNT LOCK; The account locking state remains unchanged if we do not specify the ACCOUNT LOCK clause with the statement.
MySQL uses the account_locked column of the mysql.user system table to store the account locking state. We can use the SHOW CREATE USER statement to validate whether the account is unlocked or locked. If this column value is Y, it means the account is locked. If it contains N, it means the account is unlocked.
If we will try to access the locked account, the attempt fails, and MySQL issues an error that writes the below message to the error log:
Access denied for user 'user_name'@'host_name'.
An account is locked.MySQL User Account Locking Examples
Let us understand the working of locking user accounts through examples:
1. Using ACCOUNT LOCK clause for locking a new user account
First, we will create a new user account named javatpoint@localhost in the locked state using the below statement:
mysql> CREATE USER IF NOT EXISTS javatpoint@localhost
IDENTIFIED BY 'jtp123456'
ACCOUNT LOCK;Next, we will execute the below statement to show the user account and its status:
mysql> SELECT user, host, account_locked
FROM mysql.user
WHERE user = 'javatpoint' AND host = 'localhost';We should get the below output:
IN this output, we can see that the account_locked column in the mysql.user system table indicates Y. It means the username javatpoint is locked on the server.
If we try to access the user account javatpoint to connect to the MySQL Server, the attempt fails, and we will receive an error:
mysql -u javatpoint -p
Enter password: *********Here is the error message:
2. MySQL account locking for an existing user account
We can understand it by creating a new user account named markus@localhost using the below statement:
mysql> CREATE USER IF NOT EXISTS markus@localhost
IDENTIFIED BY 'mark12345';
Next, we will log in to the MySQL server with a newly created user account markus@localhost as follows:
mysql -u markus -p
Enter password: ********We will get the below output that means the user account markus@localhost is login successfully.
Now, we will use the ALTER TABLE LOCK ACCOUNT statement to lock this user account as follows:
mysql> ALTER USER markus@localhost ACCOUNT LOCK; Again, we will execute the below statement to show the user status:
mysql> SELECT user, host, account_locked
FROM mysql.user
WHERE user = 'markus' AND host = 'localhost';We can see the below output that indicates user account markus was locked successfully:
If we want to show the number of attempts to connect to the MySQL Server of locked accounts, we need the locked_connects variables. Each time we try to connect the locked user account, MySQL increases this variable’s status by 1. See the below command:
mysql> SHOW GLOBAL STATUS LIKE 'Locked_connects'; After execution, we will get this output that shows we have tried three times to connect the locked user account:
Leave a Reply