阿里云和腾讯云的MySQL都带有云审计功能,如果只是想自己实现简单的审计功能,可以通过init-connect来实现审计功能。
📊 建审计库及表
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 '审计用户登录信息';
🔐 给所有用户授权
select concat("grant select,insert on auditlog.t_audit to '",user,"'@'",host,"';") from mysql.user;
然后将这些查出来的SQL语句执行,切勿忘记,不执行的话,MySQL用户会连接不上!!!!
flush privileges;
注意,以后每添加一个用户都必须授权此表的插入权限,要不会连接不上!
grant select,insert on auditlog.t_audit to 'usera'@'%';
grant select,insert on auditlog.t_audit to 'userb'@'%';
⚙️ 设置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());';
🔍 查询配置
查看相关配置:
show global variables like '%init_connect%';
show global variables like 'log_timestamps';
show global variables like '%general%';
查询结果示例:
mysql> show global variables like '%init_connect%';
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
| init_connect | insert into auditlog.t_audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user()); |
+---------------+-------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)
mysql> show global variables like 'log_timestamps';
Empty set
mysql> show global variables like '%general%';
+------------------+--------------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------------+
| general_log | OFF |
| general_log_file | /data1/mysql_root/data/20176/TENCENT64.log |
+------------------+--------------------------------------------+
2 rows in set (0.09 sec)