Password policies in MySQL - Deep Dive

Overview - Password policies

What is it?

Password policies are rules that control how users create and manage passwords in a database system. They ensure passwords are strong enough to protect accounts from unauthorized access. These policies can include requirements like minimum length, complexity, expiration, and reuse restrictions. In MySQL, password policies help keep your data safe by enforcing these rules automatically.

Why it matters

Without password policies, users might choose weak or easy-to-guess passwords, making it simple for attackers to break into the database. This can lead to data theft, loss, or corruption. Password policies reduce this risk by forcing stronger passwords and regular updates, protecting sensitive information and maintaining trust. Imagine leaving your house with the door unlocked; password policies are like strong locks that keep intruders out.

Where it fits

Before learning password policies, you should understand basic database user accounts and authentication. After mastering password policies, you can explore advanced security topics like encryption, access control, and auditing. Password policies are a foundational step in securing databases.

Mental Model

Core Idea

Password policies are a set of automatic rules that make sure database passwords are strong, changed regularly, and hard to guess.

Think of it like...

It's like setting rules for a safe combination lock: the lock must have enough numbers, change combinations often, and never reuse old ones to keep valuables secure.

┌───────────────────────────────┐
│        Password Policies       │
├───────────────┬───────────────┤
│ Rule          │ Description   │
├───────────────┼───────────────┤
│ Length        │ Minimum chars │
│ Complexity    │ Mix of chars  │
│ Expiration    │ Change often  │
│ Reuse         │ No repeats    │
└───────────────┴───────────────┘

Build-Up - 7 Steps

1

Foundation

Understanding User Authentication Basics

Concept: Learn how users prove their identity to the database using passwords.

In MySQL, each user has a username and a password. When connecting, the user provides these credentials. The database checks if the password matches the stored one to allow access. Without passwords, anyone could access the data.

Result

Users must provide correct passwords to connect to the database.

Understanding authentication is key because password policies only matter if passwords control access.

2

Foundation

What Are Password Policies?

Concept: Password policies define rules for creating and managing passwords to improve security.

Policies can require passwords to be a certain length, include letters and numbers, expire after some time, and prevent reuse of old passwords. These rules help prevent weak passwords and reduce risks from stolen credentials.

Result

Password policies guide users to create stronger, safer passwords.

Knowing what password policies are helps you see why they protect your database from unauthorized access.

3

Intermediate

MySQL Password Policy Components

🤔Before reading on: do you think MySQL enforces password length and complexity by default? Commit to yes or no.

Concept: MySQL uses a plugin to enforce password policies with configurable rules like length, complexity, and expiration.

MySQL's 'validate_password' plugin checks passwords when users set or change them. It can require minimum length, mixed character types (uppercase, lowercase, digits, special characters), and can enforce password expiration and reuse limits. You can enable and configure this plugin to fit your security needs.

Result

Passwords that don't meet the rules are rejected when set or changed.

Understanding the plugin and its rules lets you control password strength automatically, reducing human error.

4

Intermediate

Configuring Password Policies in MySQL

🤔Before reading on: do you think you can change password policy settings without restarting MySQL? Commit to yes or no.

Concept: MySQL allows dynamic configuration of password policy settings using system variables.

You can set variables like 'validate_password.length', 'validate_password.mixed_case_count', and 'validate_password.policy' at runtime using SQL commands. For example, to require passwords at least 12 characters long, run: SET GLOBAL validate_password.length = 12; These changes take effect immediately for new password changes.

Result

Password rules adjust instantly, affecting new passwords without downtime.

Knowing how to configure policies dynamically helps maintain security without interrupting database service.

5

Intermediate

Password Expiration and Reuse Controls

🤔Before reading on: do you think MySQL automatically forces users to change passwords after a set time? Commit to yes or no.

Concept: MySQL supports password expiration and reuse prevention to keep passwords fresh and unique.

Using the 'default_password_lifetime' variable, you can set how many days a password remains valid. After expiration, users must change their password. MySQL also tracks password history to prevent reuse of recent passwords. These features help reduce risks from stolen or old passwords.

Result

Users must update passwords regularly and cannot reuse old ones.

Understanding expiration and reuse policies helps you enforce ongoing password security, not just at creation.

6

Advanced

Balancing Security and Usability in Policies

🤔Before reading on: do you think making password policies too strict always improves security? Commit to yes or no.

Concept: Strong policies improve security but can frustrate users if too strict, leading to workarounds or help requests.

If policies require very complex passwords or frequent changes, users might write passwords down or use predictable patterns. Finding a balance means setting rules that are strong enough to protect but not so hard that users avoid compliance. Monitoring failed login attempts and user feedback helps adjust policies wisely.

Result

Effective policies that users follow without frustration.

Knowing the tradeoff between security and usability prevents policies from backfiring in real environments.

7

Expert

Internal Mechanics of MySQL Password Validation

🤔Before reading on: do you think MySQL stores passwords in plain text for validation? Commit to yes or no.

Concept: MySQL uses hashing and plugin checks to validate passwords securely without storing them in plain text.

When a password is set, MySQL hashes it using secure algorithms and stores the hash. The 'validate_password' plugin runs checks on the plain password before hashing, like length and character mix. During login, the entered password is hashed and compared to the stored hash. This process ensures passwords are never exposed in plain text, protecting against leaks.

Result

Password validation is secure and efficient, preventing exposure of sensitive data.

Understanding hashing and plugin validation clarifies how MySQL protects passwords internally, beyond just policy rules.

Under the Hood

MySQL stores passwords as hashes, not plain text, using secure algorithms like SHA-256. The 'validate_password' plugin intercepts password changes and applies configured rules by analyzing the plain password before hashing. It checks length, character types, and complexity. Password expiration is tracked by storing timestamps of last changes. When users log in, MySQL hashes the entered password and compares it to the stored hash to authenticate without revealing the actual password.

Why designed this way?

Storing hashed passwords prevents attackers from reading passwords even if they access the database. The plugin approach allows flexible, modular enforcement of password rules without changing core authentication code. This design balances security, performance, and ease of configuration. Alternatives like storing plain passwords or hardcoding rules were rejected due to security risks and inflexibility.

┌───────────────┐       ┌─────────────────────┐       ┌───────────────┐
│ User sets     │──────▶│ validate_password    │──────▶│ Password rules │
│ new password  │       │ plugin checks rules  │       │ enforced       │
└───────────────┘       └─────────────────────┘       └───────────────┘
        │                                                  │
        │                                                  ▼
        │                                       ┌───────────────────┐
        │                                       │ Password hashed   │
        │                                       │ and stored securely│
        │                                       └───────────────────┘
        │
        ▼
┌───────────────┐
│ User login    │
│ enters pwd    │
└───────────────┘
        │
        ▼
┌───────────────┐
│ Password hashed│
│ and compared  │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Do you think setting a minimum password length alone guarantees strong security? Commit to yes or no.

Common Belief: Just having a minimum password length is enough to keep passwords secure.

Reality: Length alone is not enough; passwords also need complexity like mixed characters to resist guessing and attacks.

Why it matters: Relying only on length can lead to weak passwords like 'aaaaaaaaaaaa' that are easy to crack.

Quick: Do you think MySQL automatically forces all users to change passwords regularly by default? Commit to yes or no.

Common Belief: MySQL forces password expiration and change for all users by default.

Reality: Password expiration is disabled by default and must be configured explicitly.

Why it matters: Assuming expiration is automatic can leave accounts vulnerable if passwords are never updated.

Quick: Do you think MySQL stores user passwords in plain text for easy validation? Commit to yes or no.

Common Belief: MySQL stores passwords as plain text to quickly check them during login.

Reality: MySQL stores only hashed passwords, never plain text, to protect against leaks.

Why it matters: Misunderstanding this can lead to insecure practices like trying to retrieve or display passwords.

Quick: Do you think making password policies too strict always improves security? Commit to yes or no.

Common Belief: Stricter password policies always make the system more secure.

Reality: Overly strict policies can frustrate users, causing them to write down passwords or find workarounds, reducing security.

Why it matters: Ignoring usability can backfire, weakening overall security.

Expert Zone

1

The 'validate_password' plugin's complexity checks count character classes, not just presence, so multiple uppercase letters matter.

2

Password expiration enforcement depends on the 'default_password_lifetime' variable and requires users to change passwords on next login after expiry.

3

MySQL allows different password policies per user by enabling or disabling plugins per account, offering fine-grained control.

When NOT to use

Password policies alone are not enough for high-security environments; use them alongside multi-factor authentication, encryption, and network security. For very sensitive data, consider hardware security modules or external authentication systems instead of relying solely on MySQL password policies.

Production Patterns

In production, teams often customize password policies to balance security and user convenience, monitor failed login attempts to detect attacks, and integrate MySQL password policies with centralized identity management systems for consistent security across services.

Connections

Access Control

Password policies build on access control by securing user credentials that grant permissions.

Understanding password policies helps secure the first step of access control—authentication—before permissions are checked.

Cryptography

Password hashing in MySQL uses cryptographic algorithms to protect stored passwords.

Knowing cryptography basics clarifies why passwords are hashed and how this prevents exposure even if the database is compromised.

Human Factors in Security

Password policies must consider human behavior to be effective and not cause risky workarounds.

Recognizing human factors helps design policies that users follow willingly, improving real-world security.

Common Pitfalls

#1 Setting password length too short, allowing weak passwords.

Wrong approach:

SET GLOBAL validate_password.length = 4;

Correct approach:

SET GLOBAL validate_password.length = 12;

Root cause:

Misunderstanding that short passwords are easy to guess and do not provide enough security.

#2 Assuming password expiration is active without configuring it.

Wrong approach:

No configuration; passwords never expire by default.

Correct approach:

SET GLOBAL default_password_lifetime = 90;

Root cause:

Not knowing that expiration must be explicitly enabled to force password changes.

#3 Disabling the validate_password plugin to avoid complexity requirements.

Wrong approach:

UNINSTALL PLUGIN validate_password;

Correct approach:

Configure plugin settings to balance complexity instead of disabling it.

Root cause:

Avoiding complexity due to user complaints without considering security risks.

Key Takeaways

Password policies are essential rules that ensure database passwords are strong, complex, and regularly updated to protect data.

MySQL enforces password policies using the 'validate_password' plugin, which checks password length, complexity, expiration, and reuse.

Password hashes, not plain text, are stored to keep passwords secure even if the database is accessed by attackers.

Balancing strictness and usability in password policies prevents users from creating insecure workarounds.

Password policies are one part of database security and should be combined with other measures like access control and encryption.

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