How To Unlock User Accounts in MySQL Server
Summary: in this tutorial, you will learn how to use the ALTER TABLE UNLOCK ACCOUNT to unlock user accounts in MySQL server.
When you create a new user using the CREATE USER statement with the ACCOUNT LOCK clause, the new user has a locked state.
Similarly, if you use the ALTER USER ACCOUNT LOCK statement to change a user account, the user account is also locked.
To unlock a user account, you use the ALTER USER ACCOUNT LOCK statement:
ALTER USER [IF EXISTS] account_name
ACCOUNT UNLOCK;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify the name of the user account that you want to unlock after the
ALTER USERkeywords. - Second, include the
ACCOUNT UNLOCKclause after the account name. - Third, use the
IF EXISTSoption to conditionally unlock the account if it exists only.
To unlock multiple user accounts at the same time, you use the following syntax:
ALTER USER [IF EXISTS]
account_name1
[, account_name2, ...]
ACCOUNT UNLOCK;
Code language: SQL (Structured Query Language) (sql)
In this syntax, you specify a list of comma-separated names of the user accounts that you want to unlock after the ALTER USER keywords.
Unlocking user accounts example
First, create a user named brad@localhost in a locked state:
CREATE USER brad@localhost
IDENTIFIED BY 'Secret!pass1'
ACCOUNT LOCK;
Code language: SQL (Structured Query Language) (sql)
Second, show the status of the user account:
SELECT
user,
host,
account_locked
FROM
mysql.user
WHERE
user = 'brad' AND
host = 'localhost';
Code language: SQL (Structured Query Language) (sql)
Third, use the ALTER USER to unlock the user:
ALTER USER 'brad'@'localhost'
ACCOUNT UNLOCK;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you have learned how to use the ALTER TABLE ACCOUNT UNLOCK statement to unlock a user account.