mysql利用audit审计日志

大体来说除了商业版的审计插件外，还有三类审计插件可以考虑：

  • Percona 5.6.17首次提供的审计插件audit log
  • MariaDB的审计插件server audit
  • Mcafee的审计插件mysql-audit，已经在github上开源

[TOC]

Percona审计插件audit_log

安装插件

  • 确认插件目录
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
  • 确认插件文件（自带的）
ll /opt/mysql/lib/plugin/audit_log.so
  • 安装插件
mysql> INSTALL PLUGIN audit_log SONAME 'audit_log.so';
  • 确认审计功能已开启
mysql> show variables like 'audit%';
+-----------------------------+---------------+
| Variable_name               | Value         |
+-----------------------------+---------------+
| audit_log_buffer_size       | 1048576       |  #.指定审计日志的内存buffer
| audit_log_exclude_accounts  |               |
| audit_log_exclude_commands  |               |
| audit_log_exclude_databases |               |
| audit_log_file              | audit.log     |  #.审计日志文件，默认路径参考 show variables like 'datadir';
| audit_log_flush             | OFF           |
| audit_log_format            | OLD           |  #.指定审计日志格式：OLD(xml属性)、NEW(xml标签)、JSON、CSV
| audit_log_handler           | FILE          |
| audit_log_include_accounts  |               |
| audit_log_include_commands  |               |
| audit_log_include_databases | testdb        |  #.筛选包含xxx的库
| audit_log_policy            | ALL           |  #.核心参数：ALL(记录所有)、LOGIN(记录登录登出信息)、QUERY(只记录查询事件)、NONE(不记录任何事件)
| audit_log_rotate_on_size    | 524288000     |  #.单个轮训切分日志的大小，建议500MB
| audit_log_rotations         | 10            |  #.轮询切分日志的数量
| audit_log_strategy          | ASYNCHRONOUS  |
| audit_log_syslog_facility   | LOG_USER      |
| audit_log_syslog_ident      | percona-audit |
| audit_log_syslog_priority   | LOG_INFO      |
+-----------------------------+---------------+

启用插件

方式一：在线启用插件

mysql> SET GLOBAL audit_log_policy=ALL;

方式二：配置文件加载插件，重启服务后永久生效

[mysqld]
plugin-load=audit_log.so

########################## audit ##########################
audit_log_policy=ALL
audit_log_format=JSON
audit_log_file=/data/mysql_3406/log/audit.log
audit_log_rotate_on_size=500M
audit_log_rotations=10

筛选日志

  • 筛选日志：取值逗号分隔，如果设置include或者exclude，则另一个变量不能设置任何值
mysql> SET GLOBAL audit_log_include_accounts = 'user_test@%';           #.包含xxx用户
mysql> SET GLOBAL audit_log_exclude_accounts = 'root@localhost';        #.排除xxx用户
mysql> SET GLOBAL audit_log_include_databases = 'test,mysql,db1';       #.包含xxx库
mysql> SET GLOBAL audit_log_exclude_databases = NULL;                   #.排除xxx库
mysql> SET GLOBAL audit_log_include_tables = 't1,test';                 #.包含xxx表
mysql> SET GLOBAL audit_log_exclude_tables = NULL;                      #.排除xxx表
mysql> SET GLOBAL audit_log_include_commands = NULL;                    #.包含xxx命令类型
mysql> SET GLOBAL audit_log_exclude_commands= 'set_option,create_db';   #.排除xxx命令类型
mysql> SELECT name FROM performance_schema.setup_instruments WHERE name LIKE "statement/sql/%" ORDER BY name;
  • 无效的参数
# SET GLOBAL audit_log_format=JSON;                                #.只读变量,无法修改
# SET GLOBAL audit_log_file=/data/mysql_3406/log/audit.log;        #.语法错误
# SET GLOBAL audit_log_include_tables = 'student';                 #.未知变量,无效了

停用插件

  • 停用审计及卸载审计插件
mysql> SET GLOBAL audit_log_policy=NONE;
mysql> UNINSTALL PLUGIN audit_log;

审计场景实践

场景1：2020.09.07.审计xxx库的数据变更（insert、update、delete），验证ok。

SET GLOBAL audit_log_policy=QUERIES;
SET GLOBAL audit_log_include_accounts = NULL;
SET GLOBAL audit_log_rotate_on_size=500*1024*1024;
SET GLOBAL audit_log_rotations=10;
SET GLOBAL audit_log_include_databases = NULL;
SET GLOBAL audit_log_exclude_databases = NULL;
SET GLOBAL audit_log_include_databases = 'kidstools_tenant';
SET GLOBAL audit_log_include_commands='insert,update,delete';

MariaDB审计插件server_audit

安装插件

  • mysql社区版没有审计插件，可以在测试机器上安装mariadb，再找出server_audit.so包。也可以直接去mariadb官网下载一个二进制包找。
find / -name server_audit.so
# wget -c http://iso.sqlfans.cn/mysql/plugin/server_audit.so -P /opt
  • 确认插件目录
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
  • 将 server_audit.so 复制到mysql插件的路径下
cp /opt/server_audit.so /opt/mysql/lib/plugin/
  • 安装插件
mysql> INSTALL PLUGIN server_audit SONAME 'server_audit.so';
  • 确认审计功能已开启
mysql> show variables like 'server_audit%';
+-------------------------------+-----------------------+
| Variable_name                 | Value                 |
+-------------------------------+-----------------------+
| server_audit_events           | QUERY_DML_NO_SELECT   |  #.审计事件类型
| server_audit_excl_users       |                       |
| server_audit_file_path        | audit.log             |  #.审计日志文件，默认路径参考 show variables like 'datadir';
| server_audit_file_rotate_now  | OFF                   |  #.on表示强制进行日志轮替
| server_audit_file_rotate_size | 524288000             |  #.设置server_audit.log大小达到限制后进行轮替，建议500MB
| server_audit_file_rotations   | 10                    |  #.日志文件数量
| server_audit_incl_users       |                       |
| server_audit_loc_info         |                       |
| server_audit_logging          | ON                    |  #.核心参数：ON(已开启审计)、OFF(未开启审计)
| server_audit_mode             | 1                     |
| server_audit_output_type      | file                  |
| server_audit_query_log_limit  | 1024                  |
| server_audit_syslog_facility  | LOG_USER              |
| server_audit_syslog_ident     | mysql-server_auditing |
| server_audit_syslog_info      |                       |
| server_audit_syslog_priority  | LOG_INFO              |
+-------------------------------+-----------------------+

server_audit_events，审计事件类型包括：

  • CONNECT：连接、断开连接和失败的连接，包括错误代码
  • QUERY：以纯文本形式执行的查询及其结果，包括由于语法或权限错误而失败的查询
  • TABLE：受查询执行影响的表
  • QUERY_DDL：与QUERY相同，但只筛选DDL类型的查询（create、alter、drop、rename和truncate语句，create/drop[procedure/function/user]和rename user除外（它们不是DDL）
  • QUERY_DML：与QUERY相同，但只筛选DML类型的查询（do、call、load data/xml、delete、insert、select、update、handler和replace语句）
  • QUERY_DCL：与QUERY相同，但只筛选DCL类型的查询（create user、drop user、rename user、grant、revoke和set password语句）
  • QUERY_DML_NO_SELECT：与QUERY_DML相同，但不记录SELECT查询（do、call、load data/xml、delete、insert、update、handler和replace语句）

启用插件

方式一：在线启用插件

mysql> SET GLOBAL server_audit_logging=on;

方式二：配置文件加载插件，重启服务后永久生效

[mysqld]

########################## Mariadb Audit ##########################
plugin_load = server_audit=server_audit.so    #载入审计插件
server_audit = FORCE_PLUS_PERMANENT           #防止插件被卸载
server_audit_file_path = audit.log            #定义审计日志文件名
server_audit_file_rotate_now = OFF            #是否强制切割审计日志
server_audit_file_rotate_size = 524288000     #定义切割审计日志的文件大小500M
server_audit_file_rotations = 10              #定义审计日志的轮询个数，0为不轮询
server_audit_logging = ON                     #开启日志记录
server_audit_output_type = file               #指定日志输出类型，可为SYSLOG或FILE
server_audit_query_log_limit = 1024
server_audit_syslog_facility = LOG_USER
server_audit_syslog_ident = mysql-server_auditing
server_audit_syslog_priority = LOG_INFO

停用插件

mysql> SET GLOBAL server_audit_logging='OFF';

审计场景实践

场景1：2020.09.07.审计xxx库的数据变更（insert、update、delete），验证ok。

SET GLOBAL server_audit_file_path='/data/mysql_3306/log/audit.log';
SET GLOBAL server_audit_file_rotate_size=500*1024*1024;
SET GLOBAL server_audit_file_rotations=10;
SET GLOBAL server_audit_file_rotate_now=on;
SET GLOBAL server_audit_events='QUERY_DML_NO_SELECT';
SET GLOBAL server_audit_logging=on;

建议：若要审计确认xx表为何被修改，建议再添加如下2个字段，以配合确认。

ALTER TABLE kidstools_tenant.student 
ADD COLUMN create_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT '插入时间', 
ADD COLUMN update_time timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '更新时间';

再确认下是否有event、外键、触发器

show variables like 'event_scheduler';
select * from information_schema.events where event_schema not in ('mysql','information_schema','performance_schema','sys') limit 10;
select * from information_schema.triggers where trigger_schema not in ('mysql','information_schema','performance_schema','sys') limit 10;
select * from information_schema.table_constraints where constraint_type = 'FOREIGN KEY' and table_name = 'student' limit 10;

分析结果：查看对 【xx库.xx表.xx字段】 的 【增、删、改】 操作记录

cat /data/mysql_3306/log/audit.log* | grep kidstools_tenant | grep student | grep school_id | grep -v parent | more

结合binlog分析：

mysqlbinlog --no-defaults -vv --base64-output=decode-rows --skip-gtids=true --start-datetime='2020-09-14 05:00:00' -d kidstools_tenant /data/mysql_3306/binlog/mysql-bin.04320* | more

遇到的坑：不要根据某一条被修改的记录（容易被误导）去定位审计日志，也许是 update left join set 批量修改的，比如：

20200922 06:01:02,u-7361-mysql,web_pub,192.168.10.174,2695773,222032218,QUERY,kidstools_attendance,'update student_leave_day_record r left join kidstools_tenant.student s on s.id=r.student_id set s.class_id=r.class_id , s.school_id=r.class_id , r.`status`=s.`status` where r.record_date=\'2021-09-22\' and (s.class_id<>r.class_id or s.school_id<>r.class_id or r.`status`<>s.`status`)',0
Copyright © www.sqlfans.cn 2023 All Right Reserved更新时间： 2022-01-14 17:30:12

results matching ""

    No results matching ""