MySQL - Lock User Account

MySQL - Locking and Unlocking User Accounts

MySQL provides a crucial security feature to temporarily or permanently disable a user account by locking it. This is an essential administrative tool for managing database security and access control.

Common reasons to lock a user account include:

• Security Incident Response: If an account is suspected of being compromised, locking it immediately prevents further unauthorized access.
• Temporary Deactivation: When an employee leaves a company or a service account is temporarily not needed, locking is a reversible alternative to dropping the user.
• Compliance: Forcing a manual review of an account before re-enabling it.
• Automated Security: Locking an account automatically after multiple failed login attempts.

Locking a User Account

You can lock a user account either when creating it or by altering an existing account. MySQL uses the ACCOUNT LOCK clause for this purpose.

Locking a New Account on Creation

To create a new user that is locked by default, append ACCOUNT LOCK to the CREATE USER statement.

CREATE USER 'temp_user'@'localhost'
IDENTIFIED BY 'a-secure-password'
ACCOUNT LOCK;

Locking an Existing Account

To lock an existing user, use the ALTER USER statement.

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

After this command is executed, any attempt by 'app_user'@'%' to connect to the server will fail with an ‘Account is locked’ error.

-- Connection attempt by locked user
-- mysql -u app_user -p
-- Enter password: *****
ERROR 3118 (HY000): Access denied for user 'app_user'@'%'. Account is locked.

Unlocking a User Account

To re-enable a locked account, you use the ACCOUNT UNLOCK clause with the ALTER USER statement. This is the direct counterpart to locking.

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

After unlocking, the user can connect again with their valid credentials.

Checking the Lock Status of an Account

The lock status for all users is stored in the account_locked column of the mysql.user system table. You can query this table to see the status of one or all users. A value of ‘Y’ means locked, and ‘N’ means not locked.

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

User	Host	account_locked
app_user	%	Y

Automating Account Locking with a Client Program

In a real application, you might want to programmatically lock a user account, for example, as part of an admin dashboard or an automated security script.

Best Practice: Parameterization

Even though ALTER USER itself is less susceptible to traditional injection, it is a best practice to use parameterized queries or properly escaped inputs whenever user-provided data is used to identify the account to be locked, to prevent any potential security issues.

Python Example

This script locks a specific user provided as an argument.

import mysql.connector

def lock_user(db_config, username, hostname='localhost'):
    # DDL statements like ALTER USER cannot use standard placeholders.
    # We must construct the query string, but first, validate the inputs.
    # Basic validation to prevent trivial injection into the identifiers.
    if not username.isalnum() or not (hostname.isalnum() or hostname in ['%', 'localhost']):
        print("Error: Invalid username or hostname format.")
        return

    # Note: Use a highly privileged account for this script, with strong credentials.
    try:
        with mysql.connector.connect(**db_config) as cnx:
            with cnx.cursor() as cursor:
                # Construct the query safely after validation.
                query = f"ALTER USER '{username}'@'{hostname}' ACCOUNT LOCK"
                cursor.execute(query)
                cnx.commit()
                print(f"Successfully locked user '{username}'@'{hostname}'.")
    except mysql.connector.Error as err:
        print(f"Failed to lock user: {err}")

# Example usage
db_admin_config = {'user': 'admin_user', 'password': 'admin_password', 'host': '127.0.0.1'}
lock_user(db_admin_config, 'compromised_user')

Node.js Example

This async function locks a specified user.

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

async function lockUser(dbConfig, username, hostname = 'localhost') {
    // Similar to Python, validate identifiers before including in a query string.
    if (!/^[a-zA-Z0-9_]+$/.test(username) || !/^[a-zA-Z0-9._%]+$/.test(hostname)) {
        console.error('Error: Invalid username or hostname format.');
        return;
    }

    let connection;
    try {
        connection = await mysql.createConnection(dbConfig);
        const query = `ALTER USER ?@? ACCOUNT LOCK`;
        // mysql2/promise can often handle identifier replacement with `?`
        await connection.execute(query, [username, hostname]);
        console.log(`Successfully locked user '${username}'@'${hostname}'.`);
    } catch (err) {
        console.error(`Failed to lock user: ${err.message}`);
    } finally {
        if (connection) await connection.end();
    }
}

// Example usage
const dbAdminConfig = { host: 'localhost', user: 'admin_user', password: 'admin_password' };
lockUser(dbAdminConfig, 'compromised_user');