MYSQL 5.7.36 等保 建设记录

---

前言

MYSQL 5.7.36 等保 建设记录

参考博文: MySql数据库之审计（开启log+设置init-connect实现无插件审计）
参考博文: 二级等保标准和解决方法（服务器，数据库，应用）
参考博文: 等保测评mysql实例

---

一、开启审计日志

1.1 查看当前状态

mysql>show variables like '%general_log%';
+------------------+-------------------------------------------------------+
| Variable_name    | Value                                                 |
+------------------+-------------------------------------------------------+
| general_log      | OFF                                                   |
| general_log_file | /var/lib/mysql/mysql-58f8894999-5sv88.log             |
+------------------+-------------------------------------------------------+

1.2 开启方式

按照一些帖子的记录，将记录写入到mysql.general_log表会导致服务的性能下降，所以需要将log_output配置为file类型；为了检测临时改成table类型也可以，不推荐长期配置为table类型。

配置文件mysql.cnf参数开启

[mysqld]
general_log = ON
log_timestamps = system
log_output = file

命令开启

set global general_log=on;
set log_timestamps = system;

1.3 查看开启后状态

mysql>show variables like '%general_log%';
+------------------+-------------------------------------------------------+
| Variable_name    | Value                                                 |
+------------------+-------------------------------------------------------+
| general_log      | ON                                                    |
| general_log_file | /var/lib/mysql/mysql-58f8894999-5sv88.log             |
+------------------+-------------------------------------------------------+

---

二、密码有效期

2.1 查看当前状态

mysql>show variables like '%password_lifetime%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| default_password_lifetime |  0    |
+---------------------------+-------+
1 row in set (0.00 sec)

2.2 开启方式

配置文件mysql.cnf参数开启

[mysqld]
default_password_lifetime=90

命令开启

set global default_password_lifetime=90;

2.3 查看开启后状态

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

---

三、密码复杂度

密码复杂度需要validate_password.so插件的支持

3.1 查看当前状态

mysql> show variables like 'validate_password%';
Empty set (0.01 sec)

3.2 开启方式

配置文件mysql.cnf参数开启

[mysqld]
plugin-load-add=validate_password.so
validate-password=FORCE_PLUS_PERMANENT
validate_password_policy=2

命令开启

INSTALL PLUGIN validate_password SONAME 'validate_password.so';
set global validate_password_policy=2;
set global validate-password=FORCE_PLUS_PERMANENT;

3.3 查看开启后状态

mysql>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      |
+--------------------------------------+--------+

---

四、连接控制

连接控制需要connection_control.so插件的支持

4.1 查看当前状态

mysql> show variables like 'connection%';
Empty set (0.01 sec)

4.2 开启方式

配置文件mysql.cnf参数开启

[mysqld]
plugin-load-add=connection_control.so
connection-control-failed-connections-threshold = 5 
connection-control-min-connection-delay = 300000

命令开启

INSTALL PLUGIN validate_password SONAME 'connection_control.so';
set global connection-control-failed-connections-threshold = 5;
set global connection-control-min-connection-delay = 300000;

4.3 查看开启后状态

MySQL [(none)]> show variables like 'connection%';
+-------------------------------------------------+------------+
| Variable_name                                   | Value      |
+-------------------------------------------------+------------+
| connection_control_failed_connections_threshold | 5          |
| connection_control_max_connection_delay         | 2147483647 |
| connection_control_min_connection_delay         | 300000     |
+-------------------------------------------------+------------+
3 rows in set (0.00 sec)

---

五、连接超时

5.1 查看当前状态

mysql> show global variables like'connect_timeout';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| connect_timeout | 10    |
+-----------------+-------+
1 row in set (0.03 sec)mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 28800 |
+---------------------+-------+
1 row in set (0.01 sec)

5.2 开启方式

配置文件mysql.cnf参数开启

[mysqld]
wait_timeout = 1800 
interactive_timeout = 1800

命令开启

set global wait_timeout = 1800;
set global interactive_timeout = 1800;

5.3 查看开启后状态

mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 1800  |
+---------------+-------+
1 row in set (0.00 sec)mysql> show variables like 'interactive_timeout';
+---------------------+-------+
| Variable_name       | Value |
+---------------------+-------+
| interactive_timeout | 1800  |
+---------------------+-------+
1 row in set (0.01 sec)

---

六、三权分立，使用init-connect记录登陆日志

6.1 创建管理员用户并赋予全部权限

create user 'admin'@'%' identified by 'Admin@2024';
GRANT ALL privileges on *.* to 'admin'@'%' with grant option;

查看创建结果

mysql> show grants for 'admin'@'%';
+--------------------------------------------------------------+
| Grants for admin@%                                           |
+--------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION |
+--------------------------------------------------------------+
1 rows in set (0.00 sec)

6.2 创建操作员用户并赋予业务数据库的权限

create user 'develop'@'%' identified by 'Develop@2024';
GRANT ALL privileges on yewu.* to 'develop'@'%' with grant option;

查看创建结果

mysql> show grants for 'develop'@'%';
+-----------------------------------------------------------------------+
| Grants for develop@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'develop'@'%'                                   |
| GRANT ALL PRIVILEGES ON `yewu`.* TO 'develop'@'%' WITH GRANT OPTION |
+-----------------------------------------------------------------------+
2 rows in set (0.00 sec)

6.3 创建审计员用户

create user 'auditor'@'%' identified by 'Auditor@2024';

查看创建结果

mysql> show grants for 'auditor'@'%';
+-----------------------------------------------------------------------+
| Grants for auditor@%                                                  |
+-----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'auditor'@'%'                                   |
+-----------------------------------------------------------------------+
1 rows in set (0.00 sec)

6.4 创建存放连接日志的数据库表

create database auditlog;
create table auditlog.t_audit(id int not null auto_increment,thread_id int not null,login_time timestamp,localname varchar(50) default null,matchname varchar(50) default null, primary key (id)
)ENGINE=InnoDB default charset=utf8 comment '审计用户登录信息';

6.5 赋予所有用户链接日志表的插入权限

拼接赋权语句

mysql> select concat("grant insert on auditlog.t_audit to '",user,"'@'",host,"';") from mysql.user;
+----------------------------------------------------------------------+
| concat("grant insert on auditlog.t_audit to '",user,"'@'",host,"';") |
+----------------------------------------------------------------------+
| grant insert on auditlog.t_audit to 'admin'@'%';                     |
| grant insert on auditlog.t_audit to 'auditor'@'%';                   |
| grant insert on auditlog.t_audit to 'develop'@'%';                   |
| grant insert on auditlog.t_audit to 'root'@'%';                      |
| grant insert on auditlog.t_audit to 'mysql.session'@'localhost';     |
| grant insert on auditlog.t_audit to 'mysql.sys'@'localhost';         |
| grant insert on auditlog.t_audit to 'root'@'localhost';              |
+----------------------------------------------------------------------+
7 rows in set (0.00 sec)

执行赋权语句

grant insert on auditlog.t_audit to 'admin'@'%';                     
grant insert on auditlog.t_audit to 'auditor'@'%';                   
grant insert on auditlog.t_audit to 'develop'@'%';                   
grant insert on auditlog.t_audit to 'root'@'%';                      
grant insert on auditlog.t_audit to 'mysql.session'@'localhost';     
grant insert on auditlog.t_audit to 'mysql.sys'@'localhost';         
grant insert on auditlog.t_audit to 'root'@'localhost';              

6.6 审计用户赋予连接日志表审计权限

拼接赋权语句

grant select,insert,delete on auditlog.t_audit to 'auditor'@'%' ;

6.7 配置init-connect

配置文件mysql.cnf参数开启

[mysqld]
init_connect='insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());'

---

总结