背景
MySQL日志审计(社区版无审计插件的情况下,通过库表记录用户登录信息+binlog查看用户的操作)
原因:基于插件的审计目前存在于Mysql的企业版、Percona和MariaDB上,但是mysql社区版本有提供init-connect选项,基于此我们可以用它来完成审计功能。
写在前面
原文链接https://www.cnblogs.com/zejin2008/p/5756192.html
link
init-connect
http://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_init_connect
link
Command-Line Format | –init-connect=name |
---|---|
System Variable | init_connect |
Scope | Global |
Dynamic | Yes |
Type | String |
1 只有超级账户才可以设置(super_priv权限)
2 超级账户无视init_connect设置(即init_connect的设置对来自超级账户的连接不生效)
文档解释:
1.服务器为每个连接的客户端执行的字符串。该字符串由一个或多个SQL语句组成,以分号字符分隔。
2.对于具有SUPER 特权的用户,init_connect不会执行的内容 。这样做是为了使的错误值 init_connect不会阻止所有客户端连接。例如,该值可能包含一个语法错误的语句,从而导致客户端连接失败。init_connect对具有SUPER特权的用户不执行 将使他们能够打开连接并固定 init_connect值。
3.从MySQL 5.7.22开始, init_connect密码过期的所有客户端用户都将跳过执行。这样做是因为这样的用户无法执行任意语句,因此init_connect 执行失败,从而使客户端无法连接。跳过init_connect 执行使用户可以连接和更改密码。
4.服务器将舍弃由值的语句产生的任何结果集init_connect。
操作
每次用户登录都会记录一条登录信息
1.
set names utf8
create database auditlog;
create table auditlog.t_audit(
id int not null auto_increment,
thread_id int not null,
login_time timestamp,
localname varchar(50) default null,
matchname varchar(50) default null,
primary key (id)
)ENGINE=InnoDB default charset=utf8 comment '审计用户登录信息';
2.
授权所有的用户拥有对审计表的插入权限
select concat("grant insert on auditlog.t_audit to '",user,"'@'",host,"';") from mysql.user; #拼结授权语句
……(执行grant语句)
flush privileges;
3.注意,以后每添加一个用户都必须授权此表的插入权限,要不会连接不上。
4.
设置init_connect参数
set global init_connect='insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());';
并在配置文件中增加如下语句:
init-connect='insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());'
以便下次重启时能生效
5.建立用户
CREATE USER 'repl'@'192.168.66.%' IDENTIFIED BY '123456';
GRANT select,update,delete,insert ON test.* TO 'repl'@'192.168.66.%';
需要重新执行第2步,或是手动执行grant insert on auditlog.t_audit to 'repl'@'192.168.66.%';
权限说明:对业务库有DML,对审计库表auditlog.t_audit只有插入insert
请检查:show grants for 'repl'@'192.168.66.%';
show grants for 'repl'@'192.168.66.%';
+---------------------------------------------------------------------------+
| Grants for repl@192.168.66.% |
+---------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'repl'@'192.168.66.%' #只有登录权限,只看 | |GRANT SELECT, INSERT, UPDATE, DELETE ON `test`.* TO 'repl'@'192.168.66.%' |
| GRANT INSERT ON `auditlog`.`t_audit` TO 'repl'@'192.168.66.%' |
+---------------------------------------------------------------------------+
备用:
revoke SELECT, INSERT, UPDATE, DELETE ON *.* from 'repl'@'192.168.66.%';
6.使用以上用户登录,验证记录信息
mysql -urepl -p -h192.168.66.140
验证:
select * from auditlog.t_audit;
+----+-----------+---------------------+---------------+-------------------+
| id | thread_id | login_time | localname | matchname |
+----+-----------+---------------------+---------------+-------------------+
| 1 | 8 | 2021-02-26 14:16:14 | repl@mysqlvms | repl@192.168.66.% |
+----+-----------+---------------------+---------------+-------------------+
7.生成数据
create table t1(x int primary key auto_increment);
insert into t1 () values (),(),(),();
insert into t1(x) select x +(select count(*) from t1) from t1;(可多执行几次生成更改行)
8.使用repl用户删除部分数据,然后通过binlog日志找到执行的具体语句对应的thread_id值。
最后通过审计表auditlog.t_audit,实现简单的审计功能。(binlog日志需要根据时间确定,属于费时的部分)
删除语句:
delete from t1 where x>10
binlog信息:找到thread_id=11
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000002 >2.txt
grep -B3 -w t1 2.txt |grep -v '^--$' >tb_name.sql #缩小查找范围
# at 8882
#210226 15:00:35 server id 1403306 end_log_pos 8981 CRC32 0x5bf989d4 Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1614322835/*!*/;
delete from t1 where x>10
最终对应出repl@mysqlvms用户和主机名
步骤2截图:
步骤8截图
mysqlbinlog
常用的语句(之后会出一篇较为详细的文章)
1.全部信息
mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000002 >2.txt
grep -B3 -w t1 2.txt |grep -v '^--$' >tb_name.sql #缩小查找范围
2.过滤出test.t1表的delete语句执行时间
mysqlbinlog mysql-bin.000002 --database test --base64-output=decode-rows -vv --skip-gtids=true |grep -C 1 -i "delete from t1" > sql2.log
more sql2.log
SET TIMESTAMP=1614322835/*!*/;
delete from t1 where x>10
完结撒花
本文说明,主要技术内容来自互联网技术大佬的分享,还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问,请留言,将确认之后,执行侵权必删