Understanding MySQL 8.0 Password Policy Options and Common Misunderstandings

1. Introduction

MySQL 8.0 has been released up to version 8.0.34, and a series of updates have strengthened password management. The article does not repeat the new password‑policy features of MySQL 8.0; readers can refer to the earlier posts linked in the text.

2. Article Background

The article focuses on the usage details of several password‑related parameters in the CREATE USER syntax, specifically the password_option part.

-- MySQL 8.0 (adds various password controls)
password_option: {
  PASSWORD EXPIRE [DEFAULT | NEVER | INTERVAL N DAY]
  | PASSWORD HISTORY {DEFAULT | N}
  | PASSWORD REUSE INTERVAL {DEFAULT | N DAY}
  | PASSWORD REQUIRE CURRENT [DEFAULT | OPTIONAL]
  | FAILED_LOGIN_ATTEMPTS N
  | PASSWORD_LOCK_TIME {N | UNBOUNDED}
}

-- MySQL 5.7 (only password‑expire attribute)
password_option: {
  PASSWORD EXPIRE
  | PASSWORD EXPIRE DEFAULT
  | PASSWORD EXPIRE NEVER
  | PASSWORD EXPIRE INTERVAL N DAY
}

The first four password attributes of CREATE USER in MySQL 8.0 correspond to the following global variables:

Parameter Name

Default Value

Corresponding mysql.user Column

Meaning

default_password_lifetime

0

password_lifetime

Global password validity period

password_history

0

password_reuse_history

Number of previous passwords that cannot be reused

password_reuse_interval

0

password_reuse_time

Time that must pass before a previous password can be reused

password_require_current

OFF

password_require_current

Whether the current password is required when changing the password

3. Misunderstanding 1

When the password‑related fields in the mysql.user table are NULL, many users mistakenly think the global configuration has not taken effect.

Verification Process

We set specific values for the four password attributes, create a new user without explicitly specifying any password options, and then inspect the mysql.user table. The fields appear as NULL.

Do the four global settings not take effect?

One might think that the global settings should automatically apply to newly created users, leading to the false belief that existing users need an ALTER USER statement. However, the official documentation clarifies that a NULL value means the user inherits the global password‑policy configuration.

Summary

Misreading the documentation caused the misunderstanding of NULL values. The following reference table shows how global parameters and per‑user attributes take effect.

4. Misunderstanding 2

The documentation describes two parameters that control password reuse: one based on count ( password_history ) and one based on time ( password_reuse_interval ). They can be used together, forming an OR logic.

Actual Test Scenarios

Scenario 1

password_history > 0 and password_reuse_interval = 0

Conclusion: The count‑based control works as expected.

Scenario 2

password_history = 0 and password_reuse_interval > 0

Conclusion: The time‑based control works as expected.

Scenario 3

password_history > 0 and password_reuse_interval > 0

Conclusion: The time‑based control takes precedence, the count‑based control does not take effect, and mysql.password_history records all passwords within the specified time, preventing their reuse.

Summary

The time‑based password_reuse_interval has higher priority than the count‑based password_history .

When both are configured, the stricter rule wins; they are not applied simultaneously.

The strategy does not affect the actual usage of the password feature.

Reference

[1] Grant‑tables: https://dev.mysql.com/doc/refman/8.0/en/grant-tables.html