(基础)[Mysqld]参数init-connect+binlog完成审计功能

背景

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

完结撒花

本文说明，主要技术内容来自互联网技术大佬的分享，还有一些自我的加工(仅仅起到注释说明的作用)。如有相关疑问，请留言，将确认之后，执行侵权必删​