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        ><zAkGi2ytVpjGFE8jY6PQL1XL2LHIgKI94Mt8bJw5pq9 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys        | $A$005$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-winx64\mysql-8.0.25-winx64\data\binlog              |
| log_bin_index                          | D:\Program Files (x86)\mysql-8.0.25-winx64\mysql-8.0.25-winx64\data\binlog.index        |
| log_bin_trust_function_creators        | OFF                                                                                     |
| log_bin_use_v1_row_events              | OFF                                                                                     |
| log_error                              | D:\Program Files (x86)\mysql-8.0.25-winx64\mysql-8.0.25-winx64\data\LAPTOP-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=...