• 欢迎访问DBA的辛酸事儿,推荐使用最新版火狐浏览器和Chrome浏览器访问本网站
  • 欢迎大家关注博主公众号:DBA的辛酸事儿
  • 博文中若有错误的地方,请大家指正,大家的指正是我前进的动力

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

MySQL SEian.G 6年前 (2019-03-31) 5220次浏览 已收录 2个评论

今天写写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;

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

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’   #要记录哪些命令语句,因为默认记录所有操作;
保存重启即可看到效果。
利用开源审计插件对mysql进行审计


如果您觉得本站对你有帮助,那么可以收藏和推荐本站,帮助本站更好地发展,在此谢过各位网友的支持。
转载请注明原文链接:利用开源审计插件对mysql进行审计
喜欢 (6)
SEian.G
关于作者:
用心去记录工作,用心去感受生活,用心去学着成长;座右铭:苦练七十二变,笑对八十一难
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体 签到

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(2)个小伙伴在吐槽
  1. MySQL5.7 安装这个审计插件需要重启吗?
    dayby2019-06-25 19:48 回复 Windows 10 | Firefox浏览器 67.0
    • SEian.G
      是不需要重启的
      SEian.G2019-08-21 14:42 回复 Windows 7 | Chrome 72.0.3626.121