How to Unlock User Accounts in MySQL Server

Summary: in this tutorial, you will learn how to use the MySQL UNLOCK ACCOUNT to unlock user accounts in the 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 USER keywords.
• Second, include the ACCOUNT UNLOCK clause after the account name.
• Third, use the IF EXISTS option 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)

Summary

• Use the ALTER USER ACCOUNT UNLOCK statement to unlock a user account in the MySQL database server.