MySQL中审计用户的行为日志

 数据库审计功能主要将用户对数据库的各类操作行为记录审计日志，以便日后进行跟踪、查询、分析，以实现对用户操作的监控和审计。 MySQL社区版没有审计功能，除了商业版的审计插件外，常见的还有三类审计插件Percona Audit Log Plugin、MariaDB Audit Plugin、McAfee MySQL Audit Plugin。除此之外，可以利用init-connect进行连接的初始化，获取用户的登录名称和thread的ID值，然后配合binlog，就可以追踪到每个操作语句的操作时间/操作人等，实现审计。此种方法需要更新配置文件，定位binlog，操作上比较繁琐。

此文中，我们用 通用查询日志来实现一个轻量级的用户操作的监控和审计，只要通过查询语句就可以追着用户的操作记录。

1. Root运行，建立测试环境

#打开普通日志 

set global log_output='table,file';

set global general_log = on;

#改变普通日志表的引擎为MyISAM

SET @old_log_state = @@global.general_log;

SELECT @old_log_state;

SET GLOBAL general_log = 'OFF';

ALTER TABLE mysql.general_log ENGINE = MyISAM;

#为普通日志表建立索引

ALTER TABLE mysql.general_log ADD INDEX (command_type,user_host(5),argument(100));

SET GLOBAL general_log = @old_log_state;

#创建视图以便捷抓取普通入职表数据, 过滤出 行为时间,用户名,执行的SQL语句

create database if not exists audit;

drop view if exists audit.log_per_user;

create view  audit.log_per_user as

SELECT event_time,user_host username,  CAST(argument AS char) sql_stmt from mysql.general_log

where command_type='Query' and upper(argument) not like 'SET%' and upper(argument) not like 'SHOW%' and argument not like '%version_comment limit 1'

;

 #创建一个测试用户

CREATE USER 'logger'@'%' IDENTIFIED BY '123';

GRANT ALL ON audit.* TO 'logger'@'%' ;

2. 另一个用户运行，例如logger, 执行各种DDL\DML语句，准备测试数据

 use audit;

 DROP TABLE if exists member;

 CREATE TABLE   `member` (

`id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,

`name` varchar(30) NOT NULL,

`age` tinyint(1) UNSIGNED NOT NULL DEFAULT '0',

`create_at`  datetime NOT NULL,

PRIMARY KEY (`id`)

) ENGINE = InnoDB CHARSET = utf8;

INSERT INTO  member (`name`, `age`, `create_at`) VALUES ('帝君', '40', NOW());

 INSERT INTO   member(`name`, `age`, `create_at`) VALUES

('SYS', FLOOR(RAND()*100),NOW()),

('DBA', FLOOR(RAND()*100),NOW()),

('DEV', FLOOR(RAND()*100),NOW()),

('PUBLIC', FLOOR(RAND()*100),NOW()),

('Anonymous',FLOOR(RAND()*100),NOW())

;

ALTER TABLE member MODIFY age int ;

UPDATE  member SET age = age + 1 where age>35;

delete from  member where age<20;

select * from  member;

truncate table member;

drop table member;

3. 查询通用查询日志，验证用户的操作日志记录

select * from audit.log_per_user where username like 'root%' and event_time > '2022-01-30';

select * from audit.log_per_user where username like 'logger%' and event_time > '2022-01-30';

4. 另一个用户运行，例如logger

select * from  member1;  member1不存在。

5. 验证 未成功执行的SQL语句也记录下来

select * from audit.log_per_user where username like 'logger%' and event_time > '2022-01-30';

综上，我使用了通用查询日志实现了一个用户的操作日志审计，是一种方便快捷的实现方式。