Password policies in MySQL - Step-by-Step Execution

Concept Flow - Password policies

User sets password

↓

Check password length

Yes↓

Check password complexity

Yes↓

Check password history

Yes↓

Password accepted

No↓

Reject password, show error

When a user sets a password, the system checks length, complexity, and history before accepting or rejecting it.

Execution Sample

MySQL

ALTER USER 'user'@'localhost' PASSWORD EXPIRE;
SET GLOBAL validate_password.length = 8;
SET GLOBAL validate_password.policy = 2;

This code sets password expiration and enforces a strong password policy with minimum length 8.

Execution Table

Step	Action	Password	Check	Result	Next Step
1	User sets password	Pass123!	Length >= 8?	Yes	Check complexity
2	Check complexity	Pass123!	Contains uppercase, lowercase, digit?	Yes	Check history
3	Check history	Pass123!	Not used before?	Yes	Accept password
4	Password accepted	Pass123!	-	-	End
5	User sets password	pass	Length >= 8?	No	Reject password
6	Reject password	pass	-	-	End

💡 Execution stops when password is accepted or rejected based on policy checks.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Password	None	Pass123!	Pass123!	Pass123!	Pass123!
Length Check	False	True	True	True	True
Complexity Check	False	False	True	True	True
History Check	False	False	False	True	True
Password Accepted	False	False	False	True	True

Key Moments - 2 Insights

Why does the password 'pass' get rejected immediately?

Because in step 5 of the execution_table, the length check fails (password length less than 8), so the password is rejected without further checks.

What happens if the password fails the complexity check?

The execution_table shows that if complexity check fails, the password is rejected before checking history, stopping the process early.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of the length check for password 'Pass123!' at step 1?

A Not checked

B Yes

C No

D Error

💡 Hint

Refer to row 1, column 'Result' in execution_table.

At which step does the password get accepted?

A Step 4

B Step 3

C Step 5

D Step 6

💡 Hint

Look for the row where 'Password accepted' is the action in execution_table.

If the password fails the complexity check, what is the next step?

A Check history

B Accept password

C Reject password

D Check length again

💡 Hint

Check the flow after complexity check failure in execution_table rows.

Concept Snapshot

Password policies in MySQL:
- Enforce minimum length (e.g., 8 characters)
- Require complexity (uppercase, lowercase, digits)
- Prevent reuse of old passwords
- Use ALTER USER and system variables to set policies
- Passwords failing checks are rejected immediately

Full Transcript

This visual execution shows how MySQL password policies work step-by-step. When a user sets a password, the system first checks if the password length meets the minimum requirement. If it does, it checks the password complexity, ensuring it contains uppercase letters, lowercase letters, and digits. Then it verifies the password is not reused from history. If all checks pass, the password is accepted. If any check fails, the password is rejected immediately. The execution table traces these steps with example passwords. The variable tracker shows how checks update from false to true as the password passes each test. Key moments clarify why short passwords are rejected early and what happens on complexity failure. The quiz tests understanding of these steps. This helps beginners see exactly how password policies enforce security in MySQL.

Practice

1. What is the purpose of MySQL's validate_password_policy variable?

easy

A. To set rules for creating strong passwords

B. To control user access privileges

C. To define database storage engines

D. To manage query cache size

Solution

1. Step 1: Understand the variable name

   The variable validate_password_policy relates to password validation rules.

2. Step 2: Identify its function

   It sets rules to ensure passwords are strong and secure.

3. Final Answer:

   To set rules for creating strong passwords -> Option A

4. Quick Check:

   Password policy = Set rules for strong passwords [OK]

Hint: Policy variables control password strength rules [OK]

Common Mistakes:

• Confusing password policy with user privileges
• Thinking it manages database storage
• Assuming it controls query cache

2. Which of the following is the correct syntax to check the current password policy level in MySQL?

easy

A. SHOW PASSWORD POLICY;

B. SELECT * FROM validate_password_policy;

C. SHOW VARIABLES LIKE 'validate_password_policy';

D. GET VARIABLES 'validate_password_policy';

Solution

1. Step 1: Recall how to view system variables

   MySQL uses SHOW VARIABLES LIKE 'variable_name'; to display variable values.

2. Step 2: Match the variable name

   The variable for password policy is validate_password_policy, so the correct syntax is SHOW VARIABLES LIKE 'validate_password_policy';

3. Final Answer:

   SHOW VARIABLES LIKE 'validate_password_policy'; -> Option C

4. Quick Check:

   SHOW VARIABLES LIKE 'validate_password_policy' [OK]

Hint: Use SHOW VARIABLES LIKE for system settings [OK]

Common Mistakes:

• Using SELECT on a variable name
• Trying non-existent commands like SHOW PASSWORD POLICY
• Using GET VARIABLES which is invalid syntax

3. Given the following commands executed in MySQL:

SET GLOBAL validate_password_length = 10;
SHOW VARIABLES LIKE 'validate_password_length';

What will be the output of the SHOW VARIABLES command?

medium

A. Variable_name: validate_password_length, Value: 10

B. Variable_name: validate_password_length, Value: 8

C. Syntax error

D. Variable_name: validate_password_policy, Value: 10

Solution

1. Step 1: Understand the SET GLOBAL command

   The command sets the global password length requirement to 10 characters.

2. Step 2: SHOW VARIABLES output

   The SHOW VARIABLES LIKE 'validate_password_length'; will display the current value, which is now 10.

3. Final Answer:

   Variable_name: validate_password_length, Value: 10 -> Option A

4. Quick Check:

   SET GLOBAL changes variable, SHOW displays updated value [OK]

Hint: SET GLOBAL changes variable, SHOW displays current value [OK]

Common Mistakes:

• Expecting default value instead of updated
• Confusing variable names
• Assuming syntax error from SET GLOBAL

4. You tried to set the password policy level with this command:

SET GLOBAL validate_password_policy = 'STRONG';

But MySQL returned an error. What is the likely cause?

medium

A. The command requires quotes around the variable name

B. The variable name is incorrect

C. You must use SET SESSION instead of SET GLOBAL

D. The value should be an integer, not a string

Solution

1. Step 1: Check variable type for validate_password_policy

   This variable expects an integer value (0,1,2) representing policy levels, not strings.

2. Step 2: Understand error cause

   Using a string like 'STRONG' causes a type mismatch error.

3. Final Answer:

   The value should be an integer, not a string -> Option D

4. Quick Check:

   Policy level = integer, not string [OK]

Hint: Policy levels use numbers, not words [OK]

Common Mistakes:

• Using string values instead of integers
• Mistyping variable name
• Confusing SET GLOBAL with SET SESSION

5. You want to enforce a password policy that requires passwords to be at least 12 characters long, include mixed case letters, numbers, and special characters. Which combination of MySQL variables should you set to achieve this?

hard

A. SET GLOBAL validate_password_length = 10; SET GLOBAL validate_password_policy = 0;

B. SET GLOBAL validate_password_length = 12; SET GLOBAL validate_password_policy = 2;

C. SET GLOBAL validate_password_length = 12; SET GLOBAL validate_password_policy = 1;

D. SET GLOBAL validate_password_length = 8; SET GLOBAL validate_password_policy = 0;

Solution

1. Step 1: Understand password length requirement

   You want at least 12 characters, so validate_password_length must be set to 12.

2. Step 2: Understand policy levels

   Policy level 2 (STRONG) requires mixed case, numbers, and special characters.

3. Step 3: Match variables to requirements

   Setting validate_password_policy = 2 and length to 12 enforces all requirements.

4. Final Answer:

   SET GLOBAL validate_password_length = 12; SET GLOBAL validate_password_policy = 2; -> Option B

5. Quick Check:

   Length 12 + policy 2 = strong password rules [OK]

Hint: Policy 2 = strong; length sets minimum characters [OK]

Common Mistakes:

• Using policy 0 or 1 which are weaker
• Setting length less than required
• Confusing policy levels with string values