MySql开启审计日志

       数据库审计能够实时记录网络上的数据库活动，对数据库操作进行细粒度审计的合规性管理，对数据库遭受到的风险行为进行警告，对攻击行为进行阻断。它通过对用户访问数据库行为的记录、分析和汇报，用来帮助用户时候生成合规报告、事故追根溯源，同时加强内外部数据库网络行为记录，提高数据资产安全。


     MySQL官网的收费组件需要购买企业版才可以使用审计功能。下面利用第三方开源审计插件 libaudit_plugin.so 在 MySQL 5.6.39 上完成审计工作。


下载地址 https://bintray.com/mcafee/mysql-audit-plugin/release/1.1.4-725#files下载对应数据库版本的插件压缩包，并上传到服务器


解压插件包


# unzip audit-plugin-mysql-5.6-1.1.4-725-linux-x86_64.zip


将解压好的插件复制到 MySQL 的插件目录下


# cd audit-plugin-mysql-5.6-1.1.4-725/lib/


# cp libaudit_plugin.so /usr/local/mysql/lib/plugin/         #MySQL的插件目录


安装插件


root@localhost 18:18: [(none)]> install plugin audit soname 'libaudit_plugin.so';


查看插件功能是否开启


root@localhost 18:19: [(none)]> 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)


开启插件功能


root@localhost 18:20: [(none)]> set global audit_json_file = 1;
Query OK, 0 rows affected (0.00 sec)


root@localhost 18:20: [(none)]> 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)


 


OK，现在在 MySQL 目录下会多出一个审计日志


# ls /usr/local/mysql/data/mysql-audit.json


查看 mysql-audit.json 文件，可以找到操作SQL语句的用户名、主机地址。这可以让在数据库上做了坏事又不认账的人无法赖账，起到了对操作数据库很好的监控效果。


比如现在有一个家伙，对 scott 库下的 emp 表，做了 select * from emp; 的操作，现在来看下审计日志中的记录。


# cat /usr/local/mysql/data/mysql-audit.json
{"msg-type":"activity","date":"1537352639624","thread-id":"3","query-id":"20","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"2201","_client_version":"5.7.18","_platform":"x86_64","program_name":"mysql"},"pid":"2201","os_user":"root","appname":"mysql","rows":"1","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1537352639624","thread-id":"3","query-id":"21","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"2201","_client_version":"5.7.18","_platform":"x86_64","program_name":"mysql"},"pid":"2201","os_user":"root","appname":"mysql","rows":"1","cmd":"Init DB","objects":[{"db":"scott","obj_type":"DATABASE"}],"query":"Init DB"}
{"msg-type":"activity","date":"1537352640539","thread-id":"3","query-id":"22","user":"root","priv_user":"root","ip":"","host":"localhost","connect_attrs":{"_os":"Linux","_client_name":"libmysql","_pid":"2201","_client_version":"5.7.18","_platform":"x86_64","program_name":"mysql"},"pid":"2201","os_user":"root","appname":"mysql","rows":"14","cmd":"select","objects":[{"db":"scott","name":"emp","obj_type":"TABLE"}],"query":"select * from emp"}
————————————————


安装的过程中可能会遇到的问题：
mysql> install plugin audit soname ‘libaudit_plugin.so’;


ERROR 1123 (HY000): Can’t initialize function ‘audit’; Plugin initialization function failed.
使用offset-extract.sh获取本机上mysql版本对应的offsets,./offset-extract.sh mysqld
如果不后驱offsets值会出现上述报错