Password policies in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Check password expiration setting

What is the output of the following query that checks the global password expiration policy in MySQL?

MySQL

SHOW VARIABLES LIKE 'default_password_lifetime';

A [{"Variable_name": "default_password_lifetime", "Value": "90"}]

B [{"Variable_name": "default_password_lifetime", "Value": "0"}]

C [{"Variable_name": "default_password_lifetime", "Value": "180"}]

D [{"Variable_name": "default_password_lifetime", "Value": "30"}]

💡 Hint

The default value 0 means passwords never expire unless changed.

The default_password_lifetime variable controls how many days a password is valid before expiration. A value of 0 means no expiration.

📝 Syntax

intermediate

Identify the correct syntax to enforce password expiration

Which option correctly sets the password expiration policy to 60 days for all users in MySQL?

A ALTER USER ALL PASSWORD EXPIRE INTERVAL 60 DAY;

B SET PASSWORD EXPIRE INTERVAL 60 DAY FOR ALL;

C SET GLOBAL default_password_lifetime = 60;

D ALTER GLOBAL PASSWORD LIFETIME 60 DAYS;

💡 Hint

The setting is a global variable, not a user-level command.

The correct way to set password expiration globally is by setting default_password_lifetime variable.

🧠 Conceptual

advanced

Understanding password validation plugin

Which statement best describes the role of the MySQL validate_password plugin?

A It manages user account lockout after failed login attempts.

B It automatically expires passwords after a set number of days.

C It encrypts passwords stored in the database.

D It enforces password strength rules like length and character types during password changes.

💡 Hint

Think about what controls password complexity requirements.

The validate_password plugin checks password strength requirements such as minimum length, mixed case, digits, and special characters.

🔧 Debug

advanced

Why does this password expiration command fail?

A DBA runs this command to expire a user's password immediately but gets an error:

ALTER USER 'alice'@'localhost' PASSWORD EXPIRE INTERVAL 0 DAY;

What is the reason for the failure?

A The INTERVAL value cannot be zero; use PASSWORD EXPIRE without INTERVAL to expire immediately.

B The user 'alice' does not exist in the database.

C The syntax requires the keyword 'SET' before PASSWORD EXPIRE.

D Password expiration cannot be set per user in MySQL.

💡 Hint

Check the correct syntax for immediate password expiration.

To expire a password immediately, use ALTER USER 'alice'@'localhost' PASSWORD EXPIRE; without INTERVAL. Using INTERVAL 0 DAY is invalid syntax.

❓ optimization

expert

Optimize password policy enforcement for many users

You want to enforce a strong password policy and expiration for thousands of users efficiently. Which approach is best?

A Set global variables <code>default_password_lifetime</code> and enable <code>validate_password</code> plugin once, so all users inherit policies automatically.

B Manually run <code>ALTER USER ... PASSWORD EXPIRE</code> and password validation for each user individually.

C Create a trigger on the user table to check password strength and expiration on every login.

D Disable password expiration and rely on users to change passwords voluntarily.

💡 Hint

Think about scalability and central management.

Setting global variables and enabling the validation plugin applies policies automatically to all users, which is efficient and scalable.

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