现在的位置: 首页Mysql>正文
mysql audit审计插件
2015年04月22日 Mysql 暂无评论 ⁄ 被围观 4,319 view+

mysql审计功能一直都是弱项:

1.之前有人借助于init-connect和binlog实现变相审计,不过比较悲剧的是不能对root用户(超级权限的用户)进行审计!可以参考:http://bbs.chinaunix.net/forum.php?mod=viewthread&tid=3632588

2.Oracle公司在5.5的企业版也增加了mysql-audit plugin,问题是该插件类似于general-log,而且仅仅存在于企业版!可以参考:http://www.cnblogs.com/cenalulu/archive/2012/11/12/mysql_audit_plugin_test.html

3.macfee公司基于percona开发的mysql audit 插件。

macfee的mysql audit插件虽然日志信息比较大,对性能影响大,但是如果想要开启审计,那也应该忍受了。介绍几个参考地址:

wiki首页:https://github.com/mcafee/mysql-audit/wiki

二进制包下载:https://bintray.com/mcafee/mysql-audit-plugin/release包含了5.1,5.5,5.6对应的二进制包

使用非常简单!可以在线开启,也可以使用plugin-load= AUDIT = libaudit_plugin.so重新启动!

在线开启非常简单!

//1. 下载对应版本的二进制包
//2.  查看mysql插件目录
mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+--------------------------------+
| Variable_name | Value                          |
+---------------+--------------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/       |
+---------------+--------------------------------+
1 row in set (0.01 sec)
//3. 复制下载的so文件至plugin_dir
//4. 安装插件
mysql> INSTALL PLUGIN AUDIT SONAME 'libaudit_plugin.so';
//5. 查看对应版本
mysql> SHOW GLOBAL STATUS LIKE 'AUDIT_version';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| Audit_version | 1.0.8-527 |
+---------------+-----------+
1 row in set (0.00 sec)
//安装成功
//6.开启audit功能
mysql> SET GLOBAL audit_json_file=ON;
//7.执行任何语句(默认会记录任何语句),然后去mysql数据目录查看mysql-audit.json文件(默认为该文件)

当然,我们还可以通过命令查看audit相关的命令

+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                   | Value                                                                                                                                                                                                                                                                                                                                                                                       |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| audit_checksum                  |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_delay_cmds                |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_delay_ms                  | 0                                                                                                                                                                                                                                                                                                                                                                                           |
| audit_force_record_logins       | OFF                                                                                                                                                                                                                                                                                                                                                                                         |
| audit_header_msg                | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_json_file                 | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_json_file_bufsize         | 1                                                                                                                                                                                                                                                                                                                                                                                           |
| audit_json_file_flush           | OFF                                                                                                                                                                                                                                                                                                                                                                                         |
| audit_json_file_retry           | 60                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_json_file_sync            | 0                                                                                                                                                                                                                                                                                                                                                                                           |
| audit_json_log_file             | mysql-audit.json                                                                                                                                                                                                                                                                                                                                                                            |
| audit_json_socket               | OFF                                                                                                                                                                                                                                                                                                                                                                                         |
| audit_json_socket_name          | /tmp/mysql.audit__data_mysql_3111                                                                                                                                                                                                                                                                                                                                                           |
| audit_json_socket_retry         | 10                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_offsets                   |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_offsets_by_version        | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_password_masking_cmds     | CREATE_USER,GRANT,SET_OPTION,SLAVE_START,CREATE_SERVER,ALTER_SERVER,CHANGE_MASTER                                                                                                                                                                                                                                                                                                           |
| audit_password_masking_regex    | identified(?:/\*.*?\*/|\s)*?by(?:/\*.*?\*/|\s)*?(?:password)?(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?\((?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"](?:/\*.*?\*/|\s)*?\)|password(?:/\*.*?\*/|\s)*?(?:for(?:/\*.*?\*/|\s)*?\S+?)?(?:/\*.*?\*/|\s)*?=(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"]|password(?:/\*.*?\*/|\s)*?['|"](?<psw>.*?)(?<!\\)['|"] |
| audit_record_cmds               | drop,alert,select,update,delete,insert                                                                                                                                                                                                                                                                                                                                                      |
| audit_record_objs               |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_uninstall_plugin          | OFF                                                                                                                                                                                                                                                                                                                                                                                         |
| audit_validate_checksum         | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_validate_offsets_extended | ON                                                                                                                                                                                                                                                                                                                                                                                          |
| audit_whitelist_cmds            |                                                                                                                                                                                                                                                                                                                                                                                             |
| audit_whitelist_users           |                                                                                                                                                                                                                                                                                                                                                                                             |
+---------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

其中我们需要关注的参数有:

1. audit_json_file
//是否开启audit功能

2. audit_json_log_file
//记录文件的路径和名称信息

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
//用户白名单

另外还有offsets参数的设置,如果开启audit_offsets_by_version=ON,则必须设置audit_offsets = 6136, 6184, 3816, 4312, 88, 2592, 96, 0, 32, 104(数字为工具计算出来的结果,各种版本不一样)。如果设置不当,在mysql错误日志中将会有如下错误内容

[Note] Audit Plugin: setup_offsets audit_offsets: (null) validate_checksum: 1 offsets_by_version: 1
[ERROR] Couldn't load plugin named 'AUDIT ' with soname ' libaudit_plugin.so'.

可以通过计算offsets,并分配适当值

1、Download the offset-extract.sh script from: https://raw.github.com/mcafee/mysql-audit/master/offset-extract/offset-extract.sh

2、确认gdb是否已安装(ensure gdb is installed)

3、chmod +x offset-extract.sh

4、./offset-extract.sh /data/app/mysql/mysql/bin/mysqld (mysqld路径)
//offsets for: /data/app/mysql/mysql/bin/mysqld (5.5.29)
{"5.5.29","53eea146441ed02575184b11f95283a1", 6032, 6080, 3784, 4208, 88, 2568},

5、修改my.cnf 在[mysqld]增加如下
plugin-load=AUDIT=libaudit_plugin.so
audit_offsets=6032, 6080, 3784, 4208, 88, 2568

offsets具体可以参考 https://github.com/mcafee/mysql-audit/wiki/Troubleshooting

附属一份切割mysql-audit.json的脚本,比较low

#!/bin/bash

PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin
export PATH

file="mysql-audit.json"
newfile="${file}_`date '+%Y%m%d%H%M%S'`"
max=50000

cd /data/mysql/

num=`cat ${file}|wc -l`
if [ ${num} -gt ${max} ]; then
split -l ${max} ${file} ${newfile}
chown mysql:mysql ${newfile}ab
chmod 660 ${newfile}ab
mysql -uroot -pxxx -Pxxx -e"SET GLOBAL audit_json_file=OFF;"
mv ${newfile}ab ${file}
mysql -uroot -pxxx -Pxxx -e"SET GLOBAL audit_json_file=ON;"
else
echo ${num}
fi

最后建议使用percona官方内置的audit,macfee的audit据说会引起crash。目前还在观测中...

来源:http://imysqldba.blog.51cto.com

给我留言

留言无头像?


×
腾讯微博