20211124---5 使用init-connect实现审计

2021/11/24---5 使用init-connect实现审计 1、实验目的

• 验证Mysql创建用户
• 验证Grant授权的作用

• 配置数据库审计，实现对Mysql进行审计
• 验证Mysql创建用户

2、定义用户及授权

//使用root登录Mysql

//创建新用户guest@‘%’，密码123456
CREATE USER 'guest'@'%' IDENTIFIED BY '123456';
select host,user,authentication_string from mysql.user;   //查询

//将Users表的查询权限、插入、删除授予该用户
grant select,insert,delete on users to guest;
flush privileges;                                         //刷新

//查询该用户权限是否符合要求
show grants for guest;                                    

3、配置审计功能

方法：init-connect+binlog

（1）第一步 创建审计数据库

//创建审计数据库auditlog；
mysql> create database auditlog

//创建审计日志表
mysql> create table 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)
    -> );

（2）第二步 授权

先查询一下所有user

mysql> SELECT HOST,USER,AUTHENTICATION_STRING FROM mysql.user;
+-----------+------------------+------------------------------------------------------------------------+
| HOST      | USER             | AUTHENTICATION_STRING                                                  |
+-----------+------------------+------------------------------------------------------------------------+
| %         | guest            | $A$005$h@Wau9Cky        >5$THISISACOMBINATIonOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A5$THISISACOMBINATIonOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A
5$THISISACOMBINATIonOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root             | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              |
+-----------+------------------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)
 

授权root及guest用户拥有对被审计表的插入、删除、修改权限

mysql> GRANT SELECt,INSERT,DELETE ON school.student to guest，root@localhost; Query OK, 0 rows affected (0.01 sec)

！注意授权root时应该写root@localhost

查询用户：select host,user,authentication_string from mysql.user;

授予guest用户插入audit表权限。

mysql> grant insert on auditlog.audit to guest; Query OK, 0 rows affected (0.01 sec)

（3）第三步 开启binlog

mysql> SHOW VARIABLES LIKE 'log_%'; //检查logbin是否开启 +----------------------------------------+-----------------------------------------------------------------------------------------+ | Variable_name | Value | +----------------------------------------+-----------------------------------------------------------------------------------------+ | log_bin | ON | | log_bin_basename | D:Program Files (x86)mysql-8.0.25-winx64mysql-8.0.25-winx64databinlog | | log_bin_index | D:Program Files (x86)mysql-8.0.25-winx64mysql-8.0.25-winx64databinlog.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | log_error | D:Program Files (x86)mysql-8.0.25-winx64mysql-8.0.25-winx64dataLAPTOP-U14E7OCH.err | | log_error_services | log_filter_internal; log_sink_internal | | log_error_suppression_list | | | log_error_verbosity | 2 | | log_output | FILE | | log_queries_not_using_indexes | OFF | | log_raw | OFF | | log_slave_updates | ON | | log_slow_admin_statements | OFF | | log_slow_extra | OFF | | log_slow_slave_statements | OFF | | log_statements_unsafe_for_binlog | ON | | log_throttle_queries_not_using_indexes | 0 | | log_timestamps | UTC | +----------------------------------------+-----------------------------------------------------------------------------------------+ 19 rows in set, 1 warning (0.01 sec)

我这里搜索出来是开启的，贴一个解决未开启的方法：

在mysql的配置文件my.ini (mac/linux：my.cnf)中，增加log_bin参数即可开启binlog日志，若无该文件则手动创建一个，my.ini在windows上面的位置，如果是安装包安装的，一般在C:ProgramData（这是个隐藏文件）里面的mysql文件夹内，改之前记得备份一个出来，不然你可能会后悔的。

开启binlog方法：windows下打开binlog - 不想下火车的人 - 博客园

修改后记得重启mysql

重启方法：服务中重启

（4）第四步 配置init-connect

在root用户下：

set global init_connect='insert into auditlog.audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());' 

或者

在配置文件my.ini下增加

init-connect='insert into auditlog.audit(id,thread_id,login_time,localname,matchname) values(null,connection_id(),now(),user(),current_user());‘

重启mysql，查看配置是否还在

show variables like 'init_connect%';

我的结果：

■用guest登录后insert一条记录，delete一条记录

mysql> insert into school.student(Name,Age,Grade,Class) values("Peter",19,10,1),("Mary",19,10,1),("Victor",18,9,2); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> delete from school.student where Name="Peter"; Query OK, 1 row affected (0.01 sec)

■使用root登录，查询audit表，查看是否有记录产生

（5）第五步 验证审计能力

 

|附录：

*SQL server *** 作*（SQL SERVER增删查改）

~INSERT INTO table(column1,column2,column3...) VALUES(data1,data2,data2...),(...),(...)

~UPDATe table set  column1=... , ...=...  where ...=...

~DELETE * FROM table where column=... ~SELECt * FROM table where column=...