Home » Learn – MySQL Account Unlock

Learn – MySQL Account Unlock

Learn – MySQL Account Unlock

Unlock is a feature that allows the user to unlock all of the account’s locks or a single lock. We’ll learn how to unlock user accounts on the MySQL server in this tutorial.
When you use the Establish USER… UNLOCK command to create a new user account, it is locked.
If we wish to unlock an existing user account, we must use the ALTER USER… ACCOUNT UNLOCK statement, which looks like this:
ALTER USER [IF ONE EXISTS] user account name UNLOCK ACCOUNT;
After the ALTER USER keyword, we must first specify the user account name for which we want to release a lock. We must then provide the ACCOUNT UNLOCK clause.

ALTER USER [IF EXISTS] user_account_name ACCOUNT UNLOCK;

MySQL also allows us to unlock multiple user accounts at the same time by using the below statement:

ALTER USER [IF EXISTS] user_account_name1, user_account_name2, ...ACCOUNT UNLOCK;

We must give a list of comma-separated user names in this format to unlock multiple accounts in a single query. The account unlocking state remains unaltered if the ACCOUNT UNLOCK clause is not included in the statement. MySQL stores the account locking state in the account locked column of the mysql.user system database.

The SHOW CREATE USER statement can be used to check whether the account is locked or unlocked. If the value of this column is Y, the account is locked. If N appears, the account has been unlocked. If we try to connect to the account without first unlocking it, MySQL throws an error and logs the following message:
User ‘user name’@’host name’ is refused access.
The account has been locked.

mysql > CREATE USER IF NOT EXISTS [email protected] IDENTIFIED BY '123456' 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 = 'VRB' AND host = 'localhost';

For SQL Server related user creation and lockout visit https://sqlserver-dba.co.uk site