使用init_connect记录MySQL登录日志

• 1. 相关背景
• 2. 操作步骤
  • 1. 创建一张登录日志表
  • 2. 生成对访问日志表的授权操作
  • 3. 配置init_connect参数
  • 4. 验证有效性
• 3. 限制条件

1. 相关背景

MySQL提供了一个参数init_connect用来控制当用户登陆时默认执行指定命令,虽然会执行,但不会返回任何结果,但可以将执行的结果保存到表中记录,我们可以利用这个特性来记录用户登录数据库的行为日志

2. 操作步骤

1. 创建一张登录日志表,包含以下信息
   • 数据库中对应的用户
   • 连接数据库的IP或主机名
   • 访问的数据库
   • 登录数据库后分配的连接ID
   • 登录数据库的时间
2. 对数据库中所有用户都授予登录日志表的查询和插入权限
   • 该步骤十分关键,如果用户没该表的相应权限,会导致设置了init_connect参数后用户无法登录
3. 配置init_connect参数触发用户登录时自动插入一条登录日志
4. 验证可用性

1. 创建一张登录日志表

CREATE TABLE `mysql`.`audit_login` (
  `ID` bigint NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `USER` varchar(128)  NOT NULL COMMENT '数据库中的用户',
  `HOST` varchar(64)  DEFAULT NULL COMMENT '登录的IP',
  `DB` varchar(64)  DEFAULT NULL COMMENT '访问的数据库',
  `PROCESSLIST_ID` bigint NOT NULL COMMENT '用户连接',
  `LOGIN_TIME` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) COMMENT '登录时间',
  `UPDATE_TIME` datetime(3) DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT '记录这行数据上次被修改的时间,默认等于LOGIN_TIME',
  PRIMARY KEY (`ID`),
  KEY `IDX_USER` (`USER`),
  KEY `IDX_HOST` (`HOST`),
  KEY `IDX_LOGIN_TIME` (`LOGIN_TIME`)
) ENGINE=InnoDB COMMENT "用户登录审计日志表";

2. 生成对访问日志表的授权操作

这里生成的授权语句会排除内部用户和有super权限的用户

-- 批量生成授权语句的SQL
-- 查询权限可以不授予,可根据具体情况调整
select concat("grant select,insert on mysql.audit_login to '",user,"'@'",host,"';") grants_sql from mysql.user where user not in ('mysql.session','mysql.sys') and Super_priv='N';

-- 输出示例
+-------------------------------------------------------------+
| grants_sql                                                  |
+-------------------------------------------------------------+
| grant select,insert on mysql.audit_login to 'zhenxing'@'%'; |
| grant select,insert on mysql.audit_login to 'bbb'@'%';      |
+-------------------------------------------------------------+

3. 配置init_connect参数

先在数据库中用set global动态设置并验证生效后在配置到my.cnf文件中

set global init_connect="insert into mysql.audit_login(USER,HOST,DB,PROCESSLIST_ID) values(current_user(),substring_index(user(),'@',-1),database(),connection_id());";

-- my.cnf文件中的配置方法
[mysqld]
init_connect="insert into mysql.audit_login(USER,HOST,DB,PROCESSLIST_ID) values(current_user(),substring_index(user(),'@',-1),database(),connection_id());";

4. 验证有效性

使用没有super权限的用户登录数据库并查询mysql.audit_login是否有记录即可
注意:该表中的PROCESSLIST_ID对应的binlog日志中的thread_id,可以使用这个来观测该用户登录到数据库后的修改操作,但由于binlog不记录查询操作,所以不支持对查询操作的匹配.

3. 限制条件

1. 不会记录有super权限的用户的登录操作
   • 防止在不允许重启的情况下因为init_connect配置错误导致有super权限的用户也登不上数据库
2. 密码过期的用户不会记录也不会报错
   • 需要给到密码过期的用户修改密码的机会,不能在登录时就报错
3. 只记录登入行为,不记录登出行为
4. 不记录登录数据库后的增删改查操作
5. 有连接池的情况下,连接会被复用,不一定能一一对应
6. 使用价值不会太高,要细化还是需要专有的审计插件