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.