等级保护MYSQL数据库测评指南

在日常数据库运维工作中，为了满足等保三要求，需要完成数据库层面的调整。本篇为MySQL5.7.41和MySQL8.0.31版本等保三调整第一篇，主要涉及以下方面：制定用户密码定期更换策略，用户密码复杂度复杂度配置，用户登录失败处理，重命名数据库的默认用户。

---

内容大纲

• 设置口令定期更换策略
• 设置密码复杂度
• 启用登录失败处理功能
• 重命名数据库默认账户

一、设置口令定期更换策略

要求：
建议配置密码定期更换策略，时间为90天以内更换一次为合格。
实施步骤：
更新配置文件及动态调整参数default_password_lifetime。
default_password_lifetime默认值为0，禁用用户密码自动过期，为全局动态整数类型参数，最大值为65535，设置为n即为从当前日起开始，用户密码在n天后过期。
请注意：添加完密码过期策略后，要及时更新用户密码，避免链接失效，也可以在完成等保三后，还原为禁用自动密码过期。

1、更新配置文件my.cnf及动态调整参数

通过在数据库中动态设置set global default_password_lifetime=90;和更新my.cnf中[mysqld]标签下添加default_password_lifetime=90避免重启数据库失效，完成用户密码过期配置。

# 在my.cnf文件的[mysqld]下设置密码有效期时间改为90天
default_password_lifetime=90

# 数据库中动态调整参数default_password_lifetime
MySQL> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 0     |
+---------------------------+-------+
1 row in set (0.00 sec)

MySQL> set global default_password_lifetime=90;
Query OK, 0 rows affected (0.00 sec)

MySQL> show global variables like 'default_password_lifetime';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime | 90    |
+---------------------------+-------+
1 row in set (0.00 sec)

# 创建用户及查看密码有效期及是否过期
MySQL5741> create user 'user1'@'%' identified by 'pwd';flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

MySQL8031> create user 'user1'@'%' identified with mysql_native_password by 'pwd';flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

MySQL> select user,host,password_lifetime,password_expired from mysql.user where user='user1';
+---------------+-----------+-------------------+------------------+
| user          | host      | password_lifetime | password_expired |
+---------------+-----------+-------------------+------------------+
| user1         | %         |              NULL | N                |
+---------------+-----------+-------------------+------------------+
1 rows in set (0.00 sec)
当password_lifetime为null时，代表该用户的密码有效期是使用的全局变量，而default_password_lifetime改为90，代表用户密码在90天后过期。password_expired值为Y/N，显示该用户密码是否过期。

# 在90天内可通过下面脚本更新密码重新开始计算用户密码过期时间
MySQL5741> alter user 'user'@'host' identified by 'newpwd';flush privileges;
MySQL8031> alter user 'user'@'host' identified with mysql_native_password by 'newpwd';flush privileges;

2、自定义密码过期策略

除了定义默认的密码过期策略，也可以为指定账号定义自定义过期策略。

# 设置密码永不过期即password_lifetime为0
MySQL> alter user 'user1'@'%' password expire never;flush privileges;select user,host,password_lifetime,password_expired from mysql.user where user='user1';
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+-------+------+-------------------+------------------+
| user  | host | password_lifetime | password_expired |
+-------+------+-------------------+------------------+
| user1 | %    |                 0 | N                |
+-------+------+-------------------+------------------+
1 row in set (0.00 sec)

# 自定义密码过期天数
MySQL> alter user 'user1'@'%' password expire interval 30 day;flush privileges;select user,host,password_lifetime,password_expired from mysql.user where user='user1';
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

+-------+------+-------------------+------------------+
| user  | host | password_lifetime | password_expired |
+-------+------+-------------------+------------------+
| user1 | %    |                30 | N                |
+-------+------+-------------------+------------------+
1 row in set (0.00 sec)

# 使用全局默认的密码过期策略
MySQL> alter user 'user1'@'%' password expire default;flush privileges;select user,host,password_lifetime,password_expired from mysql.user where user='user1';
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

+-------+------+-------------------+------------------+
| user  | host | password_lifetime | password_expired |
+-------+------+-------------------+------------------+
| user1 | %    |              NULL | N                |
+-------+------+-------------------+------------------+
1 row in set (0.00 sec)

# 设置密码立即过期
## 密码过期会遇到如下报错:ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
MySQL> alter user 'user1'@'%' password expire;flush privileges;select user,host,password_lifetime,password_expired from mysql.user where user='user1';
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

+-------+------+-------------------+------------------+
| user  | host | password_lifetime | password_expired |
+-------+------+-------------------+------------------+
| user1 | %    |              NULL | Y                |
+-------+------+-------------------+------------------+
1 row in set (0.00 sec)
结果password_expired=Y表示用户user1@'%'已经过期。

## 可以通过测试验证，允许连接到数据库，不允许做任何操作
mysql -uuser1 -ppwd -hip -Pport -e"select 1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
Please use --connect-expired-password option or invoke mysql in interactive mode.

mysql -uuser1 -ppwd -hip -Pport -e"select 1;" --connect-expired-password
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1820 (HY000) at line 1: You must reset your password using ALTER USER statement before executing this statement.

## 密码过期处理，从8.0.27开始不允许set password方式修改用户密码
MySQL5741> alter user 'user1'@'%' identified by 'newpwd';flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL8031> alter user 'user1'@'%' identified with mysql_native_password by 'newpwd';flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL> select user,host,password_lifetime,password_expired from mysql.user where user='user1';
+-------+------+-------------------+------------------+
| user  | host | password_lifetime | password_expired |
+-------+------+-------------------+------------------+
| user1 | %    |              NULL | N                |
+-------+------+-------------------+------------------+
1 row in set (0.00 sec)

## 密码过期处理后验证
mysql -uuser1 -pnewpwd -hip -Pport -e"select 1;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---+
| 1 |
+---+
| 1 |
+---+

二、设置密码复杂度

要求：
建议设置用户的密码包含大小写，数字，特殊符号，至少8位。
实施步骤：
新建用户的密码包含大小写，数字，特殊符号，至少8位。
旧用户按上述规则更新密码。

1、安装密码复杂度插件

数据库中安装validate_password.so插件，就不用在my.cnf配置需要加载的插件。

# 查看是否安装密码复杂度插件，在MySQL 8.0.15中默认没有安装这个插件
MySQL> select plugin_name,plugin_status from information_schema.plugins where plugin_name='validate_password';
Empty set (0.00 sec)

MySQL> show global variables like '%validate_password%';
Empty set (0.00 sec)

MySQL> show global status like '%validate_password%';
Empty set (0.00 sec)

# 安装密码复杂度插件
MySQL> install plugin validate_password soname 'validate_password.so';
Query OK, 0 rows affected (0.00 sec)

MySQL> select plugin_name,plugin_status from information_schema.plugins where plugin_name='validate_password';
+-------------------+---------------+
| plugin_name       | plugin_status |
+-------------------+---------------+
| validate_password | ACTIVE        |
+-------------------+---------------+
1 row in set (0.00 sec)

# 参数解读
MySQL> show global variables like 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password_check_user_name    | OFF    |
| 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      |
+--------------------------------------+--------+
7 rows in set (0.00 sec)

validate_password_check_user_name：5.7.41默认关闭，8.0.31默认开启，设置为ON时可以将密码设置成当前用户名。
validate_password_dictionary_file：插件用于验证密码强度的字典文件路径。
validate_password_length：密码最小长度。
validate_password_mixed_case_count：密码至少要包含的小写字母个数和大写字母个数。
validate_password_number_count：密码至少要包含的数字个数。
validate_password_special_char_count：密码至少要包含的特殊字符数。
validate_password_policy：validate_password强制执行的密码策略。
validate_password_policy的值可以使用数值0、1、2或相应的符号值LOW、MEDIUM、STRONG来指定。
0/LOW：只检查长度。对于长度测试，所需的长度是validate_password_length系统变量的值
1/MEDIUM：检查长度、数字、大小写、特殊字符。
2/STRONG：检查长度、数字、大小写、特殊字符字典文件。

# 验证规则，安装插件前的用户不更新密码不受密码复杂度规则限制，更新则受限制，后续新建用户受密码复杂度规则限制
MySQL5741> alter user 'user1'@'%' identified by 'test';
MySQL8031> alter user 'user1'@'%' identified with mysql_native_password by 'test';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL5741> alter user 'user1'@'%' identified by 'Test#1234';
MySQL8031> alter user 'user1'@'%' identified with mysql_native_password by 'Test#1234';
Query OK, 0 rows affected (0.00 sec)

MySQL5741> create user 'test'@'%' identified by 'test';
MySQL8031> create user 'test'@'%' identified with mysql_native_password by 'test';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL5741> create user 'test'@'%' identified by 'test1234';
MySQL8031> create user 'test'@'%' identified with mysql_native_password by 'test1234';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

MySQL5741> create user 'test'@'%' identified by 'Test#1234';
MySQL8031> create user 'test'@'%' identified with mysql_native_password by 'Test#1234';
Query OK, 0 rows affected (0.00 sec)

2、更新弱密码用户密码

可通过mysqladmin命令行和sql语句更新弱密码用户密码，建议使用sql命令方式更新用户密码，从8.0.27开始不允许set password方式修改用户密码。

# mysqladmin更新用户弱密码
mysqladmin -uuser1 -p'Test#1234' password PwD\@2023\!\#\.
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

# sql命令更新弱密码用户密码
MySQL> alter user 'user1'@'%' IDENTIFIED BY 'PwD\@2023\!\#\.';flush privileges;
Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

MySQL> update mysql.user set authentication_string=password('PwD\@2023\!\#\.') where user='user1';flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

Query OK, 0 rows affected (0.00 sec)

MySQL5741> set password for 'user1'@'%'=password('PwD\@2023\!\#\.');flush privileges;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

3、MySQL8.0组件component_validate_password

在MySQL8.0版本中，插件validate_password被重新实现为组件validate_password。插件validate_password已被弃用；预计在MySQL的未来版本中被删除。当前MySQL8.0.31版本测试，插件validate_password依然有效，当组件和插件同时存在时，会先尝试使用该组件，如果组件不可用，则回退到插件。

# 安装组件component_validate_password
MySQL8031> install component 'file://component_validate_password';
Query OK, 0 rows affected (0.03 sec)

MySQL8031> select * from mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn                      |
+--------------+--------------------+------------------------------------+
|            1 |                  1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)

MySQL8031> show global 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      |
+--------------------------------------+--------+
7 rows in set (0.01 sec)
参数解释及测试这里不再进行，参考上面测试

4、卸载密码复杂度插件

测试或者完成等保三审核后按需卸载密码复杂度插件

# 卸载插件validate_password
MySQL> uninstall plugin validate_password;
Query OK, 0 rows affected (0.00 sec)

# 卸载组件validate_password
MySQL8031> uninstall component 'file://component_validate_password';
Query OK, 0 rows affected (0.00 sec)

三、启用登录失败处理功能

可采取结束会话、限制非法登录次数和自动退出等措施
要求：
建议配置登录失败处理功能，例如：输错密码5次锁定30分钟（时间次数自己定）
实施步骤：
需要采用connection_control（会话控制限制登录次数）和
connection_control_failed_login_attempts共同实现

1、安装连接控制插件

数据库中安装connection_control.so插件，就不用在my.cnf配置需要加载的插件。

# 查看是否安装连接控制插件
MySQL> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%connection%';
Empty set (0.00 sec)

MySQL> show global variables like 'connection_control%';
Empty set (0.00 sec)

# 安装连接控制插件
MySQL> install plugin connection_control soname 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)

MySQL> install plugin connection_control_failed_login_attempts soname 'connection_control.so';
Query OK, 0 rows affected (0.01 sec)

MySQL> select plugin_name,plugin_status from information_schema.plugins where plugin_name like '%connection%';
+------------------------------------------+---------------+
| plugin_name                              | plugin_status |
+------------------------------------------+---------------+
| CONNECTION_CONTROL                       | ACTIVE        |
| CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS | ACTIVE        |
+------------------------------------------+---------------+
2 rows in set (0.00 sec)

# 参数解读
MySQL> show global 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       |
+-------------------------------------------------+------------+
3 rows in set (0.01 sec)

connection_control_failed_connections_threshold ：连续失败最大次数3次,0表示不开启
connection_control_max_connection_delay :超过最大失败次数之后阻塞登录最大时间（毫秒）
connection_control_min_connection_delay :超过最大失败次数之后阻塞登录最小时间（毫秒）

# 状态解读
MySQL> show status like '%connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Connection_control_delay_generated：表示连接控制的使用次数(可用户判断是否存在暴力登录尝试)
重新配置connection_control_failed_connections_threshold变量，该记录及表information_schema.connection_control_failed_login_attempts数据会被删除(重置)

2、测试验证

验证登录失败及插件使用情况。

# 便于测试时间和次数设置小点
MySQL> set global connection_control_failed_connections_threshold=2;
Query OK, 0 rows affected (0.00 sec)

MySQL> set global connection_control_max_connection_delay=120000;
Query OK, 0 rows affected (0.00 sec)

MySQL> set global connection_control_min_connection_delay=60000;
Query OK, 0 rows affected (0.00 sec)

MySQL> show global variables like 'connection_control%';
+-------------------------------------------------+--------+
| Variable_name                                   | Value  |
+-------------------------------------------------+--------+
| connection_control_failed_connections_threshold | 2      |
| connection_control_max_connection_delay         | 120000 |
| connection_control_min_connection_delay         | 60000  |
+-------------------------------------------------+--------+
3 rows in set (0.00 sec)

# 测试存在的用户和不存在的用户
mysql -uuser1 -p'pwd'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

mysql -uuser1 -p'pwd'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)

date && mysql -uuser1 -p'pwd' || date
Tue Jun 27 18:31:30 CST 2023
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user1'@'localhost' (using password: YES)
Tue Jun 27 18:32:30 CST 2023
上面结果可以看出，user1用户用错误密码连接数据库，前两次被拒绝连接，第三次会报错，且不能访问数据库的时间间隔为1分钟。

mysql -uuser -p'pwd'
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'user'@'localhost' (using password: YES)

# 查看连接控制状态值
MySQL> show status like '%connection_control%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 2     |
+------------------------------------+-------+
1 row in set (0.00 sec)

# 查询各账号登录失败次数，如果使用不存在的用户登录，则该表记录用户名为空，但会记录具体登录的IP
MySQL> select * from information_schema.connection_control_failed_login_attempts;
+--------------------+-----------------+
| USERHOST           | FAILED_ATTEMPTS |
+--------------------+-----------------+
| 'user1'@'%'        |               3 |
| 'user'@'localhost' |               1 |
+--------------------+-----------------+
2 rows in set (0.00 sec)

# 重置参数connection_control_failed_connections_threshold
MySQL> set global connection_control_failed_connections_threshold=0;
Query OK, 0 rows affected (0.00 sec)

MySQL> show global status like '%connection_control%';select * from information_schema.connection_control_failed_login_attempts;
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| Connection_control_delay_generated | 0     |
+------------------------------------+-------+
1 row in set (0.00 sec)

Empty set (0.00 sec)

3、按等保三规则调整

动态调整连接控制参数及更新my.cnf配置文件。

# 动态调整连接控制参数
MySQL> set global connection_control_failed_connections_threshold=3;
Query OK, 0 rows affected (0.00 sec)

MySQL> set global connection_control_max_connection_delay=1800000;
Query OK, 0 rows affected (0.00 sec)

MySQL> set global connection_control_min_connection_delay=600000;
Query OK, 0 rows affected (0.00 sec)

MySQL> show global variables like 'connection_control%';
+-------------------------------------------------+---------+
| Variable_name                                   | Value   |
+-------------------------------------------------+---------+
| connection_control_failed_connections_threshold | 3       |
| connection_control_max_connection_delay         | 1800000 |
| connection_control_min_connection_delay         | 600000  |
+-------------------------------------------------+---------+
3 rows in set (0.00 sec)

# 更新my.cnf配置文件在[mysqld]下添加参数避免数据库服务重启失效
loose-connection_control_failed_connections_threshold=3
loose-connection_control_max_connection_delay=1800000
loose-connection_control_min_connection_delay=600000

4卸载密码复杂度插件

测试或者完成等保三审核后按需卸载连接控制插件

# 卸载密码复杂度插件
MySQL> uninstall plugin connection_control;
Query OK, 0 rows affected (0.01 sec)

MySQL> uninstall plugin connection_control_failed_login_attempts;
Query OK, 0 rows affected (0.01 sec)

四、重命名数据库默认账户

数据库默认管理员账户为：root@localhost
要求：
建议默认账户重命名
实施步骤：
重命名默认管理员账户

科普交流知道更多，请加入：也可联系本人绿泡泡：xiaodijia345

# 更新’root'@'localhost'用户名及验证
MySQL> update mysql.user set user='sysadmin' where user='root' and host='localhost';flush privileges;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.02 sec)

MySQL8031> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| user1            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| sysadmin         | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)
MySQL8.0多了一个mysql.infoschema@localhost的用户。