MySQL - Unlock User Account

MySQL - Managing User Account Locks

Locking a user account is a critical security feature in MySQL. It allows administrators to temporarily disable an account without deleting it, preventing logins and access. This is useful for responding to suspicious activity, handling departed employees, or enforcing security policies.

Checking an Account’s Lock Status

The lock status of a user is stored in the account_locked column of the mysql.user system table. A value of ‘Y’ means the account is locked, and ‘N’ means it is unlocked.

SELECT
    User,
    Host,
    account_locked
FROM
    mysql.user
WHERE
    User = 'app_user';

By default, all new user accounts created in MySQL are in an unlocked state.

Unlocking an Existing User Account

If an account is locked, you can unlock it using the ALTER USER statement with the ACCOUNT UNLOCK clause. You must have the CREATE USER privilege to execute this command.

Syntax

ALTER USER 'username'@'hostname' ACCOUNT UNLOCK;

Example

Let’s assume the user app_user@'localhost' is currently locked. We can unlock it as follows:

ALTER USER 'app_user'@'localhost' ACCOUNT UNLOCK;

After running the command, you can verify that the status has changed.

SELECT User, Host, account_locked FROM mysql.user WHERE User = 'app_user';

-- Expected Output:
-- +----------+-----------+----------------+
-- | User     | Host      | account_locked |
-- +----------+-----------+----------------+
-- | app_user | localhost | N              |
-- +----------+-----------+----------------+

Locking a User Account

Similarly, you can explicitly lock an account using the ACCOUNT LOCK clause.

ALTER USER 'app_user'@'localhost' ACCOUNT LOCK;

If a locked user attempts to connect, they will receive an Access denied error (specifically, error 3118).

Automating with Client Programs

Here are modern, robust examples of how to unlock a user account using various programming languages. These examples include proper error handling and resource management.

Node.js (using mysql2/promise)

This example uses async/await for clean, modern asynchronous code.

const mysql = require('mysql2/promise');

async function unlockUser(username, host) {
  let connection;
  try {
    connection = await mysql.createConnection({
      host: 'localhost',
      user: 'root',
      password: 'your_root_password'
    });

    const query = 'ALTER USER ?@? ACCOUNT UNLOCK';
    await connection.execute(query, [username, host]);

    console.log(`User '${username}@${host}' has been unlocked successfully.`);
  } catch (error) {
    console.error(`Failed to unlock user: ${error.message}`);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

unlockUser('app_user', 'localhost');

Python (using mysql-connector-python)

This example uses with statements for automatic handling of connections and cursors.

import mysql.connector
from mysql.connector import errorcode

def unlock_user(username, host):
    config = {
        'user': 'root',
        'password': 'your_root_password',
        'host': 'localhost',
    }
    try:
        with mysql.connector.connect(**config) as connection:
            with connection.cursor() as cursor:
                query = f"ALTER USER '{username}'@'{host}' ACCOUNT UNLOCK"
                cursor.execute(query)
                connection.commit()
                print(f"User '{username}@{host}' has been unlocked successfully.")
    except mysql.connector.Error as err:
        print(f"Failed to unlock user: {err}")

unlock_user('app_user', 'localhost')

Java (using JDBC and try-with-resources)

This modern Java example uses try-with-resources to ensure database resources are closed automatically.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class UserUnlocker {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/?user=root&password=your_root_password";

    public void unlockUser(String username, String host) {
        String query = String.format("ALTER USER '%s'@'%s' ACCOUNT UNLOCK", username, host);
        try (Connection conn = DriverManager.getConnection(DB_URL);
             Statement stmt = conn.createStatement()) {

            stmt.executeUpdate(query);
            System.out.printf("User '%s@%s' has been unlocked successfully.%n", username, host);

        } catch (SQLException e) {
            System.err.println("Failed to unlock user: " + e.getMessage());
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        new UserUnlocker().unlockUser("app_user", "localhost");
    }
}