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.

MySQL Unlock User Account

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.
Was this tutorial helpful?