MySQL 8.4 Security Best Practices: Advanced Password Management & Protection

Security is a crucial aspect of database management and MySQL 8.4 introduces several enhancements to password management, ensuring better protection against unauthorized access. MySQL provides various built-in mechanisms to enforce strong password policies, prevent unauthorized logins and ensure compliance with security standards.

This blog explores the password management capabilities of MySQL 8.4, including expiration policies, password reuse restrictions, dual password support, random password generation and account locking after failed login attempts.

Password Management Capabilities in MySQL 8.4

MySQL 8.4 supports various password-management features that enhance security and simplify administrative tasks.

1. Password Expiration Policy

To ensure users update their passwords periodically, MySQL allows administrators to configure expiration policies. This prevents users from keeping the same password indefinitely, reducing the risk of compromised credentials.

MySQL allows manual and automatic password expiration to enforce security.

Manual Expiration:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE;

This forces the user to reset their password before performing any actions.

Automatic Expiration:

Passwords can be set to expire based on age. The default_password_lifetime variable controls this globally:

Set passwords to expire after 90 days:

[mysqld]
default_password_lifetime=90

Or dynamically:

SET PERSIST default_password_lifetime = 90;

Disable automatic expiration:

[mysqld]
default_password_lifetime=0

Per-User Expiration Settings:

Override global expiration policy for specific users:

CREATE USER 'testuser'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'testuser@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;

Disable expiration for a user:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE NEVER;

Defer to global expiration policy:

ALTER USER 'testuser'@'localhost' PASSWORD EXPIRE DEFAULT;

Handling Expired Passwords:

If a user tries to log in with an expired password, they will see an error:

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

To fix expire password for that user

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'new_password';

2. MySQL Password Reuse Policy 

MySQL allows setting password reuse restrictions based on the number of past password changes, time elapsed or both. These policies can be applied globally or per user.

Key Features:

Password History – Prevents users from reusing a set number of recent passwords (e.g., last 6 passwords).
Reuse Interval – Restricts reuse of passwords used within a defined period (e.g., 365 days).

Global Policy Setup:

To enforce password reuse restrictions globally, add these lines in my.cnf:

[mysqld]
password_history=6
password_reuse_interval=365

Or set them dynamically:

SET PERSIST password_history = 6;
SET PERSIST password_reuse_interval = 365;

Per-User Policy:

Define user-specific restrictions using CREATE USER or ALTER USER:

CREATE USER 'testuser'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'testuser'@'localhost' PASSWORD HISTORY 5 PASSWORD REUSE INTERVAL 365 DAY;

In global policy for both types of resue restrictions : 

ALTER USER 'testuser'@'localhost' PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT;

This ensures better security by enforcing unique passwords over time.

3. MySQL Password Verification-Required Policy

MySQL provides an optional security feature requiring users to verify their current password before changing it. This prevents unauthorized password changes if a session is left unattended.

Why is Password Verification Important.

Without verification, an attacker using an unattended session could change a user’s password, causing:
The original user being locked out.
The attacker gaining prolonged access.

Global Password Verification Policy

By default, password verification is optional (password_require_current = OFF).

To enforce verification globally:

[mysqld]
password_require_current=ON

Or dynamically:

SET PERSIST password_require_current = ON;

Per-User Verification Policy

Individual accounts can override the global policy:

Require current password for changes:

ALTER USER 'testuser'@'localhost' PASSWORD REQUIRE CURRENT;

Make current password optional for changes:

ALTER USER 'testuser'@'localhost' PASSWORD REQUIRE CURRENT OPTIONAL;

Defer to the global policy:

ALTER USER 'testuser'@'localhost' PASSWORD REQUIRE CURRENT DEFAULT;

Changing Passwords with Verification

When required, users must specify their current password:

Change own password (with verification):

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'new_password' REPLACE 'current_password';

Change another user’s password (must be logged in as that user):

ALTER USER 'testuser'@'localhost' IDENTIFIED BY 'new_password' REPLACE 'current_password';

Change password & authentication plugin:

ALTER USER 'testuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'new_password' REPLACE 'current_password';

4. Dual Password Support

Dual password support in MySQL allows an account to have two valid passwords simultaneously a primary and a secondary password. This feature makes it easier to update credentials without downtime in systems with multiple MySQL servers and applications.

Why Use Dual Passwords.

• Seamless password changes: Applications can continue using the old password while transitioning to the new one.
• No downtime: You don’t need to update all servers and applications at the exact same time.
• Easier credential management: Helps with staged rollouts in large, complex environments.

How Dual Passwords Work

1. Set a new primary password but retain the old one as a secondary password

ALTER USER 'appuser1'@'localhost' IDENTIFIED BY 'new_password' RETAIN CURRENT PASSWORD;

• new_password becomes the primary password.
• The old password is still valid as the secondary password.

2. Update applications to use the new password

• Once applications are using new_password, the old password is no longer needed.

3.Discard the old password

ALTER USER 'appuser1'@'localhost' DISCARD OLD PASSWORD;

• This removes the secondary password, so only the primary password remains.

5. Account Locking & Failed Login Tracking

MySQL can temporarily lock user accounts after too many failed login attempts.

How to Enable Account Locking

1. Set a user to lock after 3 failed logins, for 3 days

CREATE USER 'user1'@'localhost'IDENTIFIED BY 'securepass'

FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 3;

2. Modify an existing user to lock after 4 failed attempts (permanently)

ALTER USER 'user1'@'localhost'FAILED_LOGIN_ATTEMPTS 4 PASSWORD_LOCK_TIME UNBOUNDED;

“UNBOUNDED” means the account stays locked until manually unlocked.

Unlocking a Locked Account

1. Unlock a user manually

ALTER USER 'user1'@'localhost' ACCOUNT UNLOCK;

2. Reset failed login attempts (e.g., by modifying login settings)

ALTER USER 'user1'@'localhost' FAILED_LOGIN_ATTEMPTS 5;

Conclusion

MySQL 8.4 introduces significant improvements in password management, providing better control over user authentication. Features like password expiration, reuse restrictions, dual passwords, random password generation and account locking make MySQL more secure than ever.

By leveraging these capabilities, database administrators can ensure strong security policies, prevent unauthorized access and enhance compliance with organizational security requirements.