网站建设微盘下载,网上开店策划书,漳州网站建设优化推广,赣州网站建设多少钱文章目录 前言一、开启审计日志1.1 查看当前状态1.2 开启方式1.3 查看开启后状态 二、密码有效期2.1 查看当前状态2.2 开启方式2.3 查看开启后状态 三、密码复杂度3.1 查看当前状态3.2 开启方式3.3 查看开启后状态 四、连接控制4.1 查看当前状态4.2 开启方式4.3 查看开启后状态… 文章目录 前言一、开启审计日志1.1 查看当前状态1.2 开启方式1.3 查看开启后状态 二、密码有效期2.1 查看当前状态2.2 开启方式2.3 查看开启后状态 三、密码复杂度3.1 查看当前状态3.2 开启方式3.3 查看开启后状态 四、连接控制4.1 查看当前状态4.2 开启方式4.3 查看开启后状态 五、连接超时5.1 查看当前状态5.2 开启方式5.3 查看开启后状态 六、三权分立使用init-connect记录登陆日志6.1 创建管理员用户并赋予全部权限6.2 创建操作员用户并赋予业务数据库的权限6.3 创建审计员用户6.4 创建存放连接日志的数据库表6.5 赋予所有用户链接日志表的插入权限6.6 审计用户赋予连接日志表审计权限6.7 配置init-connect 总结 前言
MYSQL 5.7.36 等保 建设记录
参考博文: MySql数据库之审计开启log设置init-connect实现无插件审计 参考博文: 二级等保标准和解决方法服务器数据库应用 参考博文: 等保测评mysql实例 一、开启审计日志
1.1 查看当前状态
mysqlshow 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_logon;
set log_timestamps system;1.3 查看开启后状态
mysqlshow variables like %general_log%;
-------------------------------------------------------------------------
| Variable_name | Value |
-------------------------------------------------------------------------
| general_log | ON |
| general_log_file | /var/lib/mysql/mysql-58f8894999-5sv88.log |
-------------------------------------------------------------------------二、密码有效期
2.1 查看当前状态
mysqlshow 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_lifetime90命令开启
set global default_password_lifetime90;2.3 查看开启后状态
mysqlshow 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-addvalidate_password.so
validate-passwordFORCE_PLUS_PERMANENT
validate_password_policy2命令开启
INSTALL PLUGIN validate_password SONAME validate_password.so;
set global validate_password_policy2;
set global validate-passwordFORCE_PLUS_PERMANENT;3.3 查看开启后状态
mysqlshow 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-addconnection_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 likeconnect_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 Admin2024;
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 Develop2024;
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 Auditor2024;查看创建结果
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)
)ENGINEInnoDB default charsetutf8 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.sessionlocalhost; |
| grant insert on auditlog.t_audit to mysql.syslocalhost; |
| grant insert on auditlog.t_audit to rootlocalhost; |
----------------------------------------------------------------------
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.sessionlocalhost;
grant insert on auditlog.t_audit to mysql.syslocalhost;
grant insert on auditlog.t_audit to rootlocalhost; 6.6 审计用户赋予连接日志表审计权限
拼接赋权语句
grant select,insert,delete on auditlog.t_audit to auditor% ;6.7 配置init-connect
配置文件mysql.cnf参数开启
[mysqld]
init_connectinsert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());总结
