利用开源审计插件对mysql进行审计

转载：https://cloud.tencent.com/developer/article/1796107 

今天写写mysql审计的，在这里分享一下！

假设这么一个情况，你是某公司mysql DBA，某日突然公司数据库中的所有被人为删了。尽管有数据备份，但是因服务停止而造成的损失上千万，现在公司需要查出那个做删除操作的人。

但是拥有数据库操作权限的人很多，如何排查，证据又在哪？是不是觉得无能为力？mysql本身并没有操作审计的功能，那是不是意味着遇到这种情况只能自认倒霉呢？现在企业级的审计系统非常的多，但都是要monery

本文就将讨论一种简单易行的，用于mysql访问审计的思路。

1、MySQL Enterprise Audit Plugin – This plugin is not open source and is only available with MySQL Enterprise, which has a significant cost attached to it. It is the most stable and robust.
备注：MySQL企业版本才能使用这个audit插件哦，MySQL社区版慢慢等着吧
2、Percona Audit Log Plugin – Percona provides an open source auditing solution that installs with Percona Server 5.5.37+ and 5.6.17+. This plugin has quite a few output features as it outputs XML, JSON and to syslog. Percona’s implementation is the first to be a drop-in replacement for MySQL Enterprise Audit Plugin. As it has some internal hooks to the server to be feature-compatible with Oracle’s plugin, it is not available as a standalone for other versions of MySQL. This plugin is actively maintained by Percona.
备注：Percon说了，这个插件只能给Percona_sever使用，其他人不能用
3、McAfee MySQL Audit Plugin – Around the longest and has been used widely. It is open source and robust, while not using the official auditing API. It isn’t updated as often as one may like. There hasn’t been any new features in some time. It was recently updated to support MySQL 5.7
备注：发现该插件貌似不支持审计日志自动切割，感觉这个查看起来不是特别的方便

下载地址：https://bintray.com/mcafee/mysql-audit-plugin/release#files

1、解压

[root@VM_35_215_centos wjq-software]# unzip audit-plugin-mysql-5.7-1.1.6-784-linux-x86_64.zip
[root@VM_35_215_centos wjq-software]# cd audit-plugin-mysql-5.7-1.1.6-784/lib

2、查看MySQL的插件目录：

root@localhost [wjqtest]>show variables like 'plugin_dir';
+---------------+------------------------------+
| Variable_name | Value |
+---------------+------------------------------+
| plugin_dir | /usr/local/mysql/lib/plugin/ |
+---------------+------------------------------+
1 row in set (0.01 sec)

3、复制库文件到MySQL库目录下

[root@VM_35_215_centos lib]# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/
[root@VM_35_215_centos lib]# ll /usr/local/mysql/lib/plugin/libaudit_plugin.so
-rw-r--r-- 1 root root 1535547 Oct 22 23:22 /usr/local/mysql/lib/plugin/libaudit_plugin.so

4、安装插件

root@localhost [wjqtest]>install plugin AUDIT soname 'libaudit_plugin.so';
root@localhost [wjqtest]>install plugin AUDIT soname 'libaudit_plugin.so';

5、安装成功，查看mysql当前已经加载了哪些插件

root@localhost [wjqtest]>show plugins;

6、查看版本：

root@localhost [wjqtest]>show global status like '%audit%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Audit_protocol_version | 1.0 |
| Audit_version | 1.1.6-784 |
+------------------------+-----------+
2 rows in set (0.00 sec)

root@localhost [wjqtest]>show variables like "%audit_json_file%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| audit_json_file | OFF |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
+-------------------------+-------+
5 rows in set (0.00 sec)

7、开启Audit功能

root@localhost [wjqtest]>set global audit_json_file=1;
Query OK, 0 rows affected (0.01 sec)

root@localhost [wjqtest]>show variables like "%audit_json_file%";
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| audit_json_file | ON |
| audit_json_file_bufsize | 1 |
| audit_json_file_flush | OFF |
| audit_json_file_retry | 60 |
| audit_json_file_sync | 0 |
+-------------------------+-------+
5 rows in set (0.00 sec)

root@localhost [(none)]>SHOW GLOBAL VARIABLES LIKE ‘%audi%’;
可以查看插件有哪些可配置的参数，其中我们需要关注的参数有：
1. audit_json_file 是否开启audit功能。
2. audit_json_log_file 记录文件的路径和名称信息（默认放在mysql数据目录下）。
3. audit_record_cmds audit记录的命令，默认为记录所有命令。可以设置为任意dml、dcl、ddl的组合。如：audit_record_cmds=select,insert,delete,update。还可以在线设置set global audit_record_cmds=NULL。(表示记录所有命令)
4. audit_record_objs audit记录操作的对象，默认为记录所有对象，可以用SET GLOBAL audit_record_objs=NULL设置为默认。也可以指定为下面的格式：audit_record_objs=,test.*,mysql.*,information_schema.*。
5. audit_whitelist_users 用户白名单。

8、开启后执行任何语句（默认会记录任何语句，有语法错误的不会记录），然后去mysql数据目录查看mysql-audit.json文件（默认为该文件）。

[root@VM_35_215_centos lib]# cd /data/mysql/mysql_3306/data/
[root@VM_54_118_centos data]# ll mysql-audit.json
-rw-r----- 1 mysql mysql 5113 Oct 22 23:47 mysql-audit.json

查看一下mysql-audit.json文件信息（json格式）

{
"msg-type": "activity",
"date": "1540222959299",
"thread-id": "1356",
"query-id": "0",
"user": "root",
"priv_user": "root",
"ip": "123.249.13.24",
"host": "123.249.13.24",
"cmd": "Connect",
"query": "Connect"
}
{
"msg-type": "activity",
"date": "1540223138021",
"thread-id": "573",
"query-id": "633108",
"user": "root",
"priv_user": "root",
"ip": "",
"host": "localhost",
"connect_attrs": {
"_os": "linux-glibc2.12",
"_client_name": "libmysql",
"_pid": "6472",
"_client_version": "5.7.19",
"_platform": "x86_64",
"program_name": "mysql"
},
"client_port": "29044",
"status": "0",
"cmd": "show_variables",
"objects": [
{
"db": "",
"obj_type": "TABLE"
},
{
"db": "performance_schema",
"name": "session_variables",
"obj_type": "TABLE"
}
],
"query": "show variables like '%audit'"
}
{
"msg-type": "activity",
"date": "1540223144979",
"thread-id": "573",
"query-id": "633109",
"user": "root",
"priv_user": "root",
"ip": "",
"host": "localhost",
"connect_attrs": {
"_os": "linux-glibc2.12",
"_client_name": "libmysql",
"_pid": "6472",
"_client_version": "5.7.19",
"_platform": "x86_64",
"program_name": "mysql"
},
"client_port": "29044",
"rows": "30",
"status": "0",
"cmd": "show_variables",
"objects": [
{
"db": "",
"obj_type": "TABLE"
},
{
"db": "performance_schema",
"name": "session_variables",
"obj_type": "TABLE"
}
],
"query": "show variables like '%audit%'"
}
{
"msg-type": "activity",
"date": "1540223230669",
"thread-id": "573",
"query-id": "633110",
"user": "root",
"priv_user": "root",
"ip": "",
"host": "localhost",
"connect_attrs": {
"_os": "linux-glibc2.12",
"_client_name": "libmysql",
"_pid": "6472",
"_client_version": "5.7.19",
"_platform": "x86_64",
"program_name": "mysql"
},
"client_port": "29044",
"rows": "1",
"status": "0",
"cmd": "show_variables",
"objects": [
{
"db": "",
"obj_type": "TABLE"
},
{
"db": "performance_schema",
"name": "session_variables",
"obj_type": "TABLE"
}
],
"query": "show variables like '%audit_record_cmds%'"
}

最后为了保证重启数据库，配置不丢失，修改my.cnf 配置文件，将下面的配置添加到[mysqld]中，所以在配置文件中my.cnf加入参数：

• audit_json_file=on
  #保证mysql重启后自动启动插件
• plugin-load=AUDIT=libaudit_plugin.so
  #防止删除了插件，重启后又会加载
• audit_record_cmds=’insert,delete,update,create,drop,alter,grant,truncate’ #要记录哪些命令语句,因为默认记录所有操作；

保存重启即可看到效果。