MySQL Lock Account
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:
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:
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:
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 [email protected] in the locked state using the below statement:
Next, we will execute the below statement to show the user account and its status:
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 tutoraspire is locked on the server.
If we try to access the user account tutoraspire to connect to the MySQL Server, the attempt fails, and we will receive an error:
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 [email protected] using the below statement:
Next, we will log in to the MySQL server with a newly created user account [email protected] as follows:
We will get the below output that means the user account [email protected] is login successfully.
Now, we will use the ALTER TABLE LOCK ACCOUNT statement to lock this user account as follows:
Again, we will execute the below statement to show the user status:
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:
After execution, we will get this output that shows we have tried three times to connect the locked user account:
