Password Management & Account Locking0%

Project 1: Multi-Table E-Commerce Platform Database

Password Management & Account Locking

Intermediate12 min read•Updated: 2026-09-12

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:

1-- Lock an account (blocks all authentication attempts)
2ALTER USER 'contractor'@'%' ACCOUNT LOCK;
3 
4-- Unlock the account when approved
5ALTER USER 'contractor'@'%' ACCOUNT UNLOCK;
6 
7-- Create an account in an initially locked state
8CREATE USER 'staging_app'@'%' 
9IDENTIFIED BY 'TempPass#2026' 
10ACCOUNT LOCK;

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:

1-- Require password rotation every 90 days
2ALTER USER 'finance_user'@'%' 
3PASSWORD EXPIRE INTERVAL 90 DAY;
4 
5-- Force the user to change password immediately on their very next login
6ALTER USER 'finance_user'@'%' 
7PASSWORD EXPIRE;
8 
9-- Exempt service accounts (microservices) from password expiration
10ALTER USER 'orders_microservice'@'%' 
11PASSWORD EXPIRE NEVER;

Preventing Password Reuse (Password History)

To prevent users from alternating between two passwords:

1-- Forbid reuse of the last 5 passwords
2ALTER USER 'finance_user'@'%' 
3PASSWORD HISTORY 5;
4 
5-- Forbid reuse of any password used within the last 365 days
6ALTER USER 'finance_user'@'%' 
7PASSWORD REUSE INTERVAL 365 DAY;

Automated Account Locking for Failed Logins

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

1ALTER USER 'admin_user'@'%'
2FAILED_LOGIN_ATTEMPTS 4
3PASSWORD_LOCK_TIME 1;

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

Directive	Purpose	Example
ACCOUNT LOCK / UNLOCK	Administratively suspend or activate an account	`ALTER USER 'u'@'%' ACCOUNT LOCK;`
PASSWORD EXPIRE	Force password change on next login	`ALTER USER 'u'@'%' PASSWORD EXPIRE;`
PASSWORD EXPIRE INTERVAL N DAY	Set automatic expiration timeframe	`... PASSWORD EXPIRE INTERVAL 60 DAY;`
PASSWORD HISTORY N	Disallow reusing previous N passwords	`... PASSWORD HISTORY 6;`
FAILED_LOGIN_ATTEMPTS N	Lockout threshold for bad passwords	`... FAILED_LOGIN_ATTEMPTS 3;`

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

What does FAILED_LOGIN_ATTEMPTS 3 combined with PASSWORD_LOCK_TIME 1 accomplish?

Question 5

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

Next Lesson

Project 1: Multi-Table E-Commerce Platform Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Revoking Privileges & FLUSH PRIVILEGES	Project 1: Multi-Table E-Commerce Platform Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Project 1: Multi-Table E-Commerce Platform Database

Password Management & Account Locking

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

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

1-- Lock an account (blocks all authentication attempts)
2ALTER USER 'contractor'@'%' ACCOUNT LOCK;
3 
4-- Unlock the account when approved
5ALTER USER 'contractor'@'%' ACCOUNT UNLOCK;
6 
7-- Create an account in an initially locked state
8CREATE USER 'staging_app'@'%' 
9IDENTIFIED BY 'TempPass#2026' 
10ACCOUNT LOCK;

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:

1-- Require password rotation every 90 days
2ALTER USER 'finance_user'@'%' 
3PASSWORD EXPIRE INTERVAL 90 DAY;
4 
5-- Force the user to change password immediately on their very next login
6ALTER USER 'finance_user'@'%' 
7PASSWORD EXPIRE;
8 
9-- Exempt service accounts (microservices) from password expiration
10ALTER USER 'orders_microservice'@'%' 
11PASSWORD EXPIRE NEVER;

Preventing Password Reuse (Password History)

To prevent users from alternating between two passwords:

1-- Forbid reuse of the last 5 passwords
2ALTER USER 'finance_user'@'%' 
3PASSWORD HISTORY 5;
4 
5-- Forbid reuse of any password used within the last 365 days
6ALTER USER 'finance_user'@'%' 
7PASSWORD REUSE INTERVAL 365 DAY;

Automated Account Locking for Failed Logins

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

1ALTER USER 'admin_user'@'%'
2FAILED_LOGIN_ATTEMPTS 4
3PASSWORD_LOCK_TIME 1;

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

Directive	Purpose	Example
ACCOUNT LOCK / UNLOCK	Administratively suspend or activate an account	`ALTER USER 'u'@'%' ACCOUNT LOCK;`
PASSWORD EXPIRE	Force password change on next login	`ALTER USER 'u'@'%' PASSWORD EXPIRE;`
PASSWORD EXPIRE INTERVAL N DAY	Set automatic expiration timeframe	`... PASSWORD EXPIRE INTERVAL 60 DAY;`
PASSWORD HISTORY N	Disallow reusing previous N passwords	`... PASSWORD HISTORY 6;`
FAILED_LOGIN_ATTEMPTS N	Lockout threshold for bad passwords	`... FAILED_LOGIN_ATTEMPTS 3;`

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

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

Question 2

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

Question 3

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

Question 4

What does FAILED_LOGIN_ATTEMPTS 3 combined with PASSWORD_LOCK_TIME 1 accomplish?

Question 5

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

Next Lesson

Project 1: Multi-Table E-Commerce Platform Database

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Revoking Privileges & FLUSH PRIVILEGES	Project 1: Multi-Table E-Commerce Platform Database

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next