MySQL审计特性调研_vlambda技术博客

vlambda
2020-06-11

MySQL审计特性调研

女主宣言

数据库的审计功能主要是记录用户对数据库的各类操作行为，用于以后进行查询、分析和跟踪问题。本文主要调研了一些开源的审计插件，并对其安装和使用方式进行了介绍。

PS：丰富的一线技术、多元化的表现形式，尽在“360云计算”，点关注哦！

审计概述

1.1

general log

MySQL本⾝其实已经提供了server所执⾏的所有的语句的信息，也就是我们通常所说的general log，完全可以作为审计⽇志使⽤，但有如下缺点：

a. ⽆论所执⾏语句是否正确执⾏，都会记录，会导致⼤量的⽆⽤信息，后⾯的筛选不易操作；

b. 当server的并发访问⾮常⼤时，log的记录会对IO产⽣⼀定的影响，以致于影响server的性能；

c. ⽇志⽂件很容易快速增⻓，且本⾝⽆按⽇期、⼤⼩等定期rotate的功能，不⽅便维护；

1.2

binlog

另⼀个⽇志是⼆进制⽇志，也就是通常所说的binlog，由于binlog记录所有针对server数据的修改操作，所以理论上，是可以审计到所有数据记录及表结构变更相关的记录，但是由于binlog本⾝并不会记录连接⽤⼾的信息，所以，如果通过binlog审计，需要通过额外的⼿段，记录⽤⼾连接的信息，并通过分配的thread id，与binlog中的thread id进⾏关联分析，得到连接⽤⼾的信息，如：

root@localhost⽤⼾的process id为18494655，连接之后创建了⼀张表：

然后，我们可以解析binlog，可以得到如下信息：

可以看到thread id是可以和process id进⾏匹配，拿到⽤⼾的信息，但如何保存，需要为每⼀个初始连接的⽤⼾，保留其连接信息，可以通过配置init_connect参数，以及创建额外的记录连接信息的表，做到这⼀点：

存在问题：

a. 所有⽤⼾均需要对access_log表具有写权限，否则，是没办法使⽤数据库的；

b. access_log表需要定期⼿⼯维护；

c. 不会记录super user的连接信息；

d. 受限于binlog本⾝记录⽇志的局限，⽆法审计⼀些并不记录在binlog中的内容。

针对以上⽅案的不⾜，出现了⼀些开源的审计插件，以下将做分别说明，可以根据审计需要，酌情使⽤！以下是针对本次测试的⼀些基准性说明：

a. 不涉及性能对⽐，只针对于功能性测试；

b. MariaDB 及McAfee的审计插件，测试操作基于MySQL 5.7.25版本，MySQL 8.0作为对⽐的版本为8.0.18；

c. Percona 审计插件以Percona MySQL分⽀作为MySQL的基础环境，版本为8.0.19

MariaDB Audit Plugin

2.1

支持版本

MySQL 5.7及以下

2.2

安装

a. 安装插件：

b. 配置文件

c. MySQL 5.7加载插件

d. MySQL 8.0加载问题

2.3

相关参数

4.4

示例

{"msg-type":"activity","date":"1588835829430","thread-id":"7","query-id":"30","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"rows":"5","status":"0","cmd":"show_databases","objects":[{"db":"information_schema","name":"/home/vicigel/sandboxes/msb_5_7_25_1/tmp/#sql_3b49_0","obj_type":"TABLE"}],"query":"show databases"}

{"msg-type":"activity","date":"1588835829430","thread-id":"7","query-id":"31","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"status":"0","cmd":"show_tables","objects":[{"db":"information_schema","name":"/home/vicigel/sandboxes/msb_5_7_25_1/tmp/#sql_3b49_0","obj_type":"TABLE"}],"query":"show tables"}

{"msg-type":"activity","date":"1588835843868","thread-id":"7","query-id":"32","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"status":"0","cmd":"create_table","objects":[{"db":"test","name":"t1","obj_type":"TABLE"}],"query":"create table t1(id int primary key auto_increment, b int)"}

{"msg-type":"activity","date":"1588835856549","thread-id":"7","query-id":"33","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"rows":"2","status":"0","cmd":"insert","objects":[{"db":"test","name":"t1","obj_type":"TABLE"}],"query":"insert into t1 values(0,1),(2,3)"}

{"msg-type":"activity","date":"1588835864360","thread-id":"7","query-id":"34","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"rows":"1","status":"0","cmd":"delete","objects":[{"db":"test","name":"t1","obj_type":"TABLE"}],"query":"delete from t1 where id = 1"}

{"msg-type":"activity","date":"1588835865148","thread-id":"7","query-id":"35","user":"msandbox","priv_user":"msandbox","ip":"127.0.0.1","host":"localhost","connect_attrs":{"_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"18280","_client_version":"5.7.25","_platform":"x86_64","program_name":"mysql"},"cmd":"Quit","query":"Quit"}

总结对比

以下是从下面几个维度对上面几个审计插件的对比

5.1

审计粒度

Percona audit plugin可以通过参数audit_log_exclude(include)_commands控制多种不同粒度的审计，McAfee audit plugin可以通过参数audit_record_cmds(audit_whitelist_cmds)控制审计粒度，MariaDB audit plugin只能通过审计事件控制，故：

Percona audit plugin = McAfee audit plugin > MariaDB audit plugin

5.2

审计格式日志

Percona audit plugin可以通过参数audit_log_format控制不同的日志输出格式，McAfee audit plugin及MariaDB audit plugin都没有相关参数控制，故：

Percona audit plugin > McAfee audit plugin = MariaDB audit plugin

5.3

性能影响可控

Percona audit plugin可以通过audit_log_strategy参数控制不同的日志刷盘策略，可以很好的在性能和日志完整性方面取舍及折中，McAfee audit plugin可以通过audit_json_file_sync参数控制每audit_json_file_sync日志刷盘，MariaDB audit plugin无相关参数控制，故：

Percona audit plugin > McAfee audit plugin > MariaDB audit plugin

从对比结果来看，Percona audit plugin相比之下更优秀，McAfee audit plugin次之，如果MySQL选择Percona分支，那无疑自带的审计方案是最优的，而另外的McAfee audit plugin及MariaDB audit plugin在线上实际使用之前，还应做好充分的测试。

https://mariadb.com/kb/en/mariadb-audit-plugin/
https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html
https://github.com/mcafee/mysql-audit/wiki/Configuration

360云计算