MySQL User Security Hardening: Password Policy, Connection Control, and Password Change Strategies

Background

Based on security requirements, customers request a series of MySQL user security enhancements, including password length, character composition, expiration, history, and connection control.

User Password Requirements

Password must be at least 25 characters.

Contain at least 2 uppercase letters.

Contain at least 2 lowercase letters.

Contain at least 2 digits.

Contain at least 2 special characters.

Must not contain the username.

Must not be simple repeated characters (e.g., AAA, wuwuwuwu, dsadsadsa, 111).

Expire after 365 days and lock the user.

Disallow reuse of the last 5 passwords.

Allow only one password change within 24 hours.

Must not contain specified strings such as company or business names.

User Connection Requirements

After 10 consecutive login failures, enforce a 10‑minute wait, with incremental back‑off for each subsequent failure.

Requirement Analysis

The requirements can be grouped into three major blocks:

Password complexity strategy.

Connection control strategy.

Password change strategy.

MySQL provides the following components/plugins to satisfy these needs:

Password validation component.

Connection control plugin.

User password attribute configuration.

Environment Information

MySQL versions: 8.0.33, 5.7.41

Installation & Configuration

1. Password Validation Component Configuration

MySQL 5.7 uses a password validation plugin; the syntax differs slightly from 8.0, but functionality is essentially the same. The following example uses MySQL 8.0.

## 8.0 版本安装密码校验组件
INSTALL COMPONENT 'file://component_validate_password';

## 查看插件默认配置
show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count  | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count  | 1      |
+--------------------------------------+--------+

## 修改配置以便符合背景需求
set global validate_password.length=25;
set global validate_password.mixed_case_count=2;
set global validate_password.number_count=2;
set global validate_password.special_char_count=2;

## 查看调整后的配置（动态生效）
show variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 25     |
| validate_password.mixed_case_count  | 2      |
| validate_password.number_count       | 2      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count  | 2      |
+--------------------------------------+--------+

## 持久化配置到 my.cnf（永久生效）
vim /data/mysql/3306/my.cnf.3306
[mysqld]
validate_password.check_user_name      = ON
validate_password.policy               = MEDIUM
validate_password.length               = 25
validate_password.mixed_case_count    = 2
validate_password.number_count         = 2
validate_password.special_char_count  = 2

2. Connection Control Plugin Configuration

The connection control plugin is provided as a plugin for both MySQL 5.7 and 8.0. The example below uses MySQL 8.0.

## 连接控制插件安装
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';

## 查看插件默认配置
show variables like 'connection_control%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 3          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 1000       |
+-------------------------------------------------+------------+

## 修改配置以符合背景需求（最大等待时间设为 1 天）
set global connection_control_max_connection_delay=24*60*60*1000;

## 查看调整后的配置（动态生效）
show variables like 'connection_control%';
+-------------------------------------------------+-------+
| Variable_name                                   | Value |
+-------------------------------------------------+-------+
| connection_control_failed_connections_threshold | 3     |
| connection_control_max_connection_delay         | 86400000 |
| connection_control_min_connection_delay         | 1000  |
+-------------------------------------------------+-------+

## 持久化配置到 my.cnf（永久生效）
vim /data/mysql/3306/my.cnf.3306
[mysqld]
connection-control                     = FORCE
connection-control-failed-login-attempts = FORCE
connection_control_min_connection_delay = 1000
connection_control_max_connection_delay = 86400000
connection_control_failed_connections_threshold = 3

3. Password Change Strategy Configuration

Password change policies are stored in the mysql.user table. The syntax differs slightly between 5.7 and 8.0.

default_password_lifetime : password validity period (0 or NULL means never expires).

password_history : number of previous passwords that cannot be reused.

password_reuse_interval : days before a previous password can be reused.

Both versions support these parameters, but 8.0 adds finer‑grained controls.

## 5.7 version example
set global default_password_lifetime=365;
select user,host,password_lifetime from mysql.user where user not in ('mysql.session','mysql.sys');

## Persist to my.cnf
vim /data/mysql/3306/my.cnf.3306
[mysqld]
default_password_lifetime = 365

## 8.0 version example
set global default_password_lifetime=365;
set global password_history=5;
set global password_reuse_interval=1;
select user,host,password_lifetime,Password_reuse_history,Password_reuse_time from mysql.user where user not in ('mysql.infoschema','mysql.session','mysql.sys');

## Persist to my.cnf
vim /data/mysql/3306/my.cnf.3306
[mysqld]
default_password_lifetime = 365
password_history = 5
password_reuse_interval = 1

Functional Verification

1. Password Validation Component

Tests on MySQL 5.7 and 8.0 verify that passwords not meeting the configured policy are rejected.

# MySQL 5.7 test examples
show variables like 'validate%';
-- Attempt to create a user with insufficient uppercase letters
create user test33@'%' identified with 'mysql_native_password' by '1234567890@#$tyuiopasdfg';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
-- Similar tests for number count, special character count, length, etc.

2. Connection Control Plugin

Creates a test user and attempts multiple failed logins to observe exponential back‑off.

# MySQL 5.7 test examples
show variables like 'connection_control%';
create user test33@'%' identified with 'mysql_native_password' by '1qaz@WSX#EDC4rfv5tgb6yhnZVAF';
-- Repeated login attempts result in increasing wait times as configured.

3. Password Change Policy

Demonstrates password expiration, history enforcement, and interval checks for both versions.

# 5.7 example
CREATE USER 'test33'@'%' IDENTIFIED WITH 'mysql_native_password' BY '1qaz@WSX#EDC4rfv5tgb6yhnZVAF' PASSWORD EXPIRE INTERVAL 1 DAY;
-- After advancing system time, login fails with password expired error.

# 8.0 example
create user test33@'%' identified with 'mysql_native_password' by '1qaz@WSX#EDC4rfv5tgb6yhnZVAF';
alter user test33@'%' identified with 'mysql_native_password' by 'newPassword';
ERROR 3638 (HY000): Cannot use these credentials because they contradict the password history policy

Knowledge Supplement

The validate_password.dictionary_file parameter allows specifying a password dictionary to forbid passwords containing certain substrings (e.g., company names). The file must be ≤1 MiB, each line 4‑100 characters, lowercase, and matches are case‑insensitive.

# Example dictionary file content
zhenxing

# Example user creation that is rejected because it contains the forbidden substring
create user demo identified by 'aaBB11__zhenxing';

Summary

Verify version‑specific support for plugins/components.

MySQL 5.7 plugins become components in 8.0; syntax and variable names may change.

MySQL 8.0 introduces additional parameters such as password_history and password_reuse_interval .

When configuring default_password_lifetime , consider business impact to avoid service disruption.

Connection control should be enabled judiciously to prevent excessive login delays.

Unimplemented Requirements

Password can be changed at most once within 24 hours.

Disallow simple repeated characters (e.g., AAA, wuwuwuwu, dsadsadsa, 111).

References

[1] Password validation plugin: https://dev.mysql.com/doc/refman/8.0/en/validate-password.html

[2] Connection control plugin: https://dev.mysql.com/doc/refman/8.0/en/connection-control.html

[3] password_option attribute: https://dev.mysql.com/doc/refman/8.0/en/create-user.html

[4] Bug 112128: https://bugs.mysql.com/bug.php?id=112128

[5] Bug 112132: https://bugs.mysql.com/bug.php?id=112132