MySQL审计特性调研

2023-07-20 20:29:16

 DBA团队 360云计算 

女主宣言

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

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，连接之后创建了⼀张表：

MySQL审计特性调研MySQL审计特性调研

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

MySQL审计特性调研

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

MySQL审计特性调研


存在问题：

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. 安装插件：

MySQL审计特性调研

b. 配置文件

MySQL审计特性调研

c. MySQL 5.7加载插件

MySQL审计特性调研

d. MySQL 8.0加载问题

MySQL审计特性调研

2.3

相关参数

MySQL审计特性调研

2.4

审计事件

MySQL审计特性调研

2.5

示例



a. 只审计connect ddl

MySQL审计特性调研

MySQL审计特性调研


b. 只审计connect dml

MySQL审计特性调研


MySQL审计特性调研


c. 只审计connect dcl

MySQL审计特性调研

MySQL审计特性调研

Percona Audit Plugin

3.1

支持版本

Percona MySQL分支

3.2

安装

a. 安装插件

自带，无需额外安装

b. 配置文件


MySQL审计特性调研

3.3

相关参数


MySQL审计特性调研

a. audit_log_format

  • OLD

  • MySQL审计特性调研

  • NEW

MySQL审计特性调研

  • JSON

    {"audit_record":{"name":"Query","record":"4082_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:42Z","command_class":"insert","connection_id":"8","status":1136,"sqltext":"insert into t1 values(0)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}


    {"audit_record":{"name":"Query","record":"4083_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:50Z","command_class":"insert","connection_id":"8","status":0,"sqltext":"insert into t1 values(0,1)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}


    {"audit_record":{"name":"Query","record":"4084_2020-05-07T03:28:39","timestamp":"2020-05-07T03:30:52Z","command_class":"insert","connection_id":"8","status":0,"sqltext":"insert into t1 values(0, 2)","user":"root[root]@localhost []","host":"localhost","os_user":"","ip":"","db":""}}

  • CSV

    "Query","49284_2014-08-27T10:47:11","2014-08-27T10:47:23 UTC","show_databases","37",0,"show databases","root[root]@localhost []","localhost","",""

b. audit_log_exclude(include)_commands


MySQL审计特性调研


c. audit_log_strategy

  • ASYNCHRONOUS - (default) log using memory buffer, do not drop messages if buffer is full

  • PERFORMANCE - log using memory buffer, drop messages if buffer is full

  • SEMISYNCHRONOUS - log directly to file, do not flush and sync every event

  • SYNCHRONOUS - log directly to file, flush and sync every event

3.4

示例

MySQL审计特性调研

Mcafee Audit Plugin

4.1

支持版本

MySQL 5.7及以下

4.2

安装

a. 安装插件


MySQL审计特性调研

b. 配置文件

MySQL审计特性调研

c. MySQL 5.7加载插件

MySQL审计特性调研

d. MySQL 8.0 加载插件

MySQL审计特性调研

4.3

相关参数


MySQL审计特性调研

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在线上实际使用之前，还应做好充分的测试。

相关文章

  1. https://mariadb.com/kb/en/mariadb-audit-plugin/

  2. https://www.percona.com/doc/percona-server/LATEST/management/audit_log_plugin.html

  3. https://github.com/mcafee/mysql-audit/wiki/Configuration


上一篇：linux下安装mysql二进制版本(5.7.33) 生产


下一篇：牛客在线编程练习：SQL46_中等