Password Management & Account Locking

Modern compliance frameworks (such as SOC2, PCI-DSS, and ISO 27001) enforce strict identity governance policies on database infrastructure:

• Periodic password expiration
• Prohibition against password reuse (history tracking)
• Automatic account locking after consecutive failed login attempts
• Temporary suspension of inactive accounts without deleting their grants

MySQL 8.0 introduces enterprise-grade Password Management and Account Locking controls built directly into the server core.

Account Locking and Unlocking

If an employee departs or a service credential is suspected of compromise, deleting the account immediately might break audit logs or foreign keys. Instead, administrators can lock the account, immediately rejecting all connection attempts while preserving privileges:

If a client attempts to connect using a locked account, MySQL returns: ERROR 3118 (HY000): Access denied for user 'contractor'@'%'. Account is locked.

Password Expiration Policies

You can enforce password rotation policies globally or on a per-user basis:

Preventing Password Reuse (Password History)

To prevent users from alternating between two passwords:

Automated Account Locking for Failed Logins

MySQL 8.0 includes built-in protection against brute-force password guessing attacks:

• FAILED_LOGIN_ATTEMPTS 4: If 4 consecutive incorrect passwords are provided, the account locks.
• PASSWORD_LOCK_TIME 1: The account remains locked for 1 day (or use UNBOUNDED to require manual DBA unlocking).

Summary Table of Account Governance Directives

Multiple Choice Questions

1. Which SQL command immediately prevents a user from logging in without deleting their account or privileges?

A. ALTER USER 'john'@'%' ACCOUNT LOCK; B. DROP USER 'john'@'%'; C. SHUTDOWN USER 'john'@'%'; D. REVOKE CONNECT FROM 'john'@'%'; Answer: A Explanation: ACCOUNT LOCK suspends an account from logging in while retaining its grants, permissions, and metadata intact.

2. What does ALTER USER 'dev'@'%' PASSWORD EXPIRE; force the user to do?

A. Deletes their account after 24 hours B. Forces the user to change their password on their very next connection C. Encrypts all their tables D. Reverts password to 'root' Answer: B Explanation: PASSWORD EXPIRE marks the current credential expired, requiring the user to change their password immediately upon their next login.

3. Which clause prevents a user from alternating between the same two passwords?

A. PASSWORD UNIQUE 2 B. PASSWORD HISTORY 5 C. PREVENT REPEAT PASSWORDS D. STRICT PASSWORD CHECK Answer: B Explanation: The PASSWORD HISTORY clause enforces that new passwords cannot match any of the preceding N recorded passwords.

4. What does FAILED_LOGIN_ATTEMPTS 3 combined with PASSWORD_LOCK_TIME 1 accomplish?

A. Deletes the user after 3 mistakes B. Locks the account for 1 day after 3 consecutive failed password attempts C. Sends an SMS alert after 1 minute D. Restarts MySQL server Answer: B Explanation: This configuration locks the account automatically for 1 day if 3 consecutive incorrect passwords are submitted.

5. Why is ACCOUNT LOCK preferred over DROP USER when an employee takes extended medical leave?

A. DROP USER permanently deletes all data created by that user B. ACCOUNT LOCK retains all existing grants and privileges so access can be instantly restored upon return C. MySQL prohibits dropping accounts that have existed for more than 1 year D. ACCOUNT LOCK requires less disk space Answer: B Explanation: Locking temporarily disables access without requiring administrators to recreate accounts and reassign complex privilege matrices later.