前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >利用开源审计插件对mysql进行审计

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

作者头像
SEian.G
发布2021-03-03 10:05:42
3K0
发布2021-03-03 10:05:42
举报
文章被收录于专栏:SEian.G学习记录SEian.G学习记录
今天写写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、解压

代码语言:javascript
复制
[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的插件目录:

代码语言:javascript
复制
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库目录下

代码语言:javascript
复制
[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、安装插件

代码语言:javascript
复制
root@localhost [wjqtest]>install plugin AUDIT soname 'libaudit_plugin.so';
root@localhost [wjqtest]>install plugin AUDIT soname 'libaudit_plugin.so';

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

代码语言:javascript
复制
root@localhost [wjqtest]>show plugins;

6、查看版本:

代码语言:javascript
复制
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功能

代码语言:javascript
复制
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文件(默认为该文件)。

代码语言:javascript
复制
[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格式)

代码语言:javascript
复制
{
  "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’ #要记录哪些命令语句,因为默认记录所有操作;

保存重启即可看到效果。

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2019-04-01,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA的辛酸事儿 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档