通过init-connect + binlog 实现MySQL审计功能

转载 2015年11月20日 09:20:30

通过init-connect + binlog 实现MySQL审计功能

背景:

假设这么一个情况,你是某公司mysql-DBA,某日突然公司数据库中的所有被人为删了。

尽管有数据备份,但是因服务停止而造成的损失上千万,现在公司需要查出那个做删除操作的人。

但是拥有数据库操作权限的人很多,如何排查,证据又在哪?

是不是觉得无能为力?

mysql本身并没有操作审计的功能,那是不是意味着遇到这种情况只能自认倒霉呢?

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

 

关键字:init—connect,binlog,trigger

概述:

其实mysql本身已经提供了详细的sql执行记录–general log ,但是开启它有以下几个缺点

无论sql有无语法错误,只要执行了就会记录,导致记录大量无用信息,后期的筛选有难度。

sql并发量很大时,log的记录会对io造成一定的印象,是数据库效率降低。

日志文件很容易快速膨胀,不妥善处理会对磁盘空间造成一定影响。

基本原理:

由于审计的关键在于DML语句,而所有的DML语句都可以通过binlog记录。

不过遗憾的是目前MySQL binlog 中只记录,产生这条记录的connection id(随连接数自增,循环使用),这对之后的反查没有任何帮助。

因此考虑通过init-connect,在每次连接的初始化阶段,记录下这个连接的用户,和connection_id信息。

在后期审计进行行为追踪时,根据binlog记录的行为及对应的connection-id 结合 之前连接日志记录 进行分析,得出最后的结论

 声明:

本文只是探讨思路的可行性,并没有经过严格的线上测试。请慎重使用在生产环境!


正文:

1. 设置init-connect

1.1创建用于存放连接信息的表

create database AuditDB default charset utf8;
use AuditDB;
create table accesslog (ID int primary key auto_increment,ConnectionID int, ConnUserName varchar(30), PrivMatchName varchar(30), LoginTIme timestamp);

 

1.2 保证所有的用户对此表有写权限

insert into db (Host,Db,User,Insert_priv) values ('%','AuditDB','','Y');
flush privileges;

 

1.3 设置init-connect

在my.cnf 中的 [mysqld] 的block 添加以下配置;

init-connect='insert into AuditDB.accesslog (ConnectionID,ConnUserName,PrivMatchName,LoginTime) values(connection_id(),user(),current_user(),now());'
log-bin

 

1.4 重启数据库生效

service mysqld restart

 

 

2. 记录追踪

2.1 thread_id确认

假设想知道在2009年11月25日,上午9点多的时候,是谁吧test.dummy这个表给删了。可以用以下语句定位

mysqlbinlog –start-datetime=’2009-11-25 09:00:00′ –stop-datetime=’2009-11-25 09:00:00′  binlog.xxxx | grep ‘dummy’ -B 5

会得到如下结果(可见thread_id为5):

 

复制代码
    # at 300777

    #091124 16:54:00 server id 10  end_log_pos 301396       Query   thread_id=5     exec_time=0     error_code=0

    SET TIMESTAMP=1259052840;

    drop table test.dummy;
复制代码

 
 

2.2 用户确认

thread_id 确认以后,找到元凶就只是一条sql语句的问题了。

select ID,LoginTime,PrivMatchName,ConnUserName fromAuditDB.accesslog where ConnectionID=5 ;

就能发现是testuser2@localhost干的了。

 

复制代码
+——+——————————-+——————————-+—————————–+

| ID   | LoginTime                        | PrivMatchName              | ConnUserName          |

+——+——————————-+——————————-+—————————–+

|   5  | 2009-11-25 10:57:39 | testuser2@localhost | testuser2@%        |

+——+——————————-+——————————-+—————————–+
复制代码

 

 


 

3. Q&A

Q:使用init-connect会影响服务器性能吗?

A:理论上,只会在用户每次连接时往数据库里插入一条记录,不会对数据库产生很大影响。除非连接频率非常高(当然,这个时候需要注意的就是如何进行连接复用和控制,而非是不是要用这种方法的问题了)

Q:access-log表如何维护?

A: 由于是一个log系统,推荐使用archive存储引擎,有利于数据厄压缩存放。如果数据库连接数量很大的话,建议一定时间做一次数据导出,然后清表。

Q:表有其他用途么?

A:有!access-log表当然不只用于审计,当然也可以用于对于数据库连接的情况进行数据分析,例如每日连接数分布图等等,只有想不到没有做不到。

Q:会有遗漏的记录吗?

A:会的,init-connect 是不会在super用户登录时执行的。所以access-log里不会有数据库超级用户的记录,这也是为什么我们不主张多个超级用户,并且多人使用的原因。


原文地址:http://www.cnblogs.com/cenalulu/archive/2012/05/09/2491736.html

相关文章推荐

mysql audit审计插件

mysql audit审计插件     mysql审计功能一直都是弱项:   1.之前有人借助于init-connect和binlog实现变相审计,不过比较悲剧的是不能对root用户(超级权限的用户)...

mysql利用init-connect增加访问审计功能

mysql的连接首先都是要通过init-connect初始化,然后连接到实例。 我们利用这一点,通过在init-connect的时候记录下用户的thread_id,用户名和用户地址实现db的访问审计...
  • wlzjsj
  • wlzjsj
  • 2016年09月02日 17:18
  • 1650

mysql init_connect的几个要点

init_connect的作用 init_connect通常用于:当一个连接进来时,做一些操作,比如设置autocommit为0,比如记录当前连接的ip来源和用户等信息到一个新表里,当做登陆日志信息 ...

MySQL审计之init-connect + binlog

如果只需要统计修改操作,有一个办法,我们可以使用init-connect + binlog的方法进行mysql的操作审计。init-connect是配置文件的一个参数,作用是在每个客户端连接时执行,我...

mysql 用init-connect+binlog实现用户操作追踪 做access 的ip的log 记录

http://blog.sina.com.cn/s/blog_605f5b4f01013xkv.html mysql 用init-connect+binlog实现用户操作追踪 做...

在MySQL中使用init-connect与binlog来实现用户操作追踪记录

前言:测试环境莫名其妙有几条重要数据被删除了,由于在binlog里面只看到是公用账号删除的,无法查询是那个谁在那个时间段登录的,就考虑怎么记录每一个MYSQL账号的登录信息,在MYSQL中,每个连接都...
  • mchdba
  • mchdba
  • 2014年07月29日 09:25
  • 4303

通过解析binlog,实现MySQL的flashback功能【python实现】

工作中经常会遇到需要确定MySQL库中在某个时间点修改了什么数据的需求,通过mysqlbinlog命令可以将binlog解析成文本,不过字段会显示成字段位置,如@1,可读性很差。 本脚本通过调用my...

mysql的审计功能

mysql服务器自身没有提供审计功能,但是我们可以使用init-connect + binlog的方法进行mysql的操作审计。由于mysql binlog记录了所有对数据库长生实际修改的sql语句,...

mysql源码添加审计功能

mysql缺乏审计功能,慢查询主要是针对性能调优的,zhendui

Mysql5.6审计功能

1. 前言         为了安全和操作的可追溯性考虑,越来越多的公司加入了审计功能。mysql5.5推出了相关的审计功能,到5.6.20功能进一步完善,算是勉强可用了,虽然细粒度方面做的不是太好,...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:通过init-connect + binlog 实现MySQL审计功能
举报原因:
原因补充:

(最多只允许输入30个字)