#####################

 

 

故障初步定位很可能是新来的几位实习生没有遵守运维规范，误操作（没加 where 条件）删表导致服务异常，目前还没确认操作用户身份。

DELETE TABLE XXXXX;（ 环境 autocommit=1 ，没有手动开启事务 ）

尽管测试环境不影响线上应用，但影响了新功能开发进度，暴露出运维管理上的漏洞。

通过以上案例思考，MySQL 本身并没有操作审计的功能，又如何根据现有的功能进行行为分析，避免悲剧再次发生？

文章整理了运维时常用的定位 MySQL 操作用户方法，帮你快速查看用户行为记录。

 

思路：

  1. 设置 init_connect 参数；
  2. 创建用户连接信息表；
  3. 通过 binlog 日志进行查看执行的危险 SQL 语句；
  4. 通过 thread_id 找到对应的用户及来源 IP 地址。

init_connect 参数的功能：当用户在客户端连接 MySQL 时，隐式执行的一条自定义的 SQL 语句（参数值）。

注意：

  • 开启 binlog 日志记录功能；
  • 对拥有 super_priv 权限的用户无效。

 

 

一、创建库和表：

mysql> show create table auditdb.accesslog\G
*************************** 1. row ***************************
       Table: accesslog
Create Table: CREATE TABLE `accesslog` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `Connectionid` int unsigned DEFAULT NULL,
  `ConnUser` varchar(30) NOT NULL DEFAULT '',
  `MatchUser` varchar(30) NOT NULL DEFAULT '',
  `Logintime` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

 

mysql>  select connection_id(),user(),current_user(),now();
+-----------------+----------------+----------------+---------------------+
| connection_id() | user()         | current_user() | now()               |
+-----------------+----------------+----------------+---------------------+
|              26 | root@localhost | root@localhost | 2023-02-03 21:13:26 |
+-----------------+----------------+----------------+---------------------+
1 row in set (0.00 sec)

 

二、创建用户：（前提是不能具有super权限，否则将不会执行init_connect变量中的sql语句）

mysql> create user  work@'%' identified by 'work';
Query OK, 0 rows affected (0.01 sec)
mysql>  grant create ,drop, select ,insert, update on *.* to work@'%';
Query OK, 0 rows affected (0.00 sec)

 

 

 

三、设置全局init_connect变量的值：

mysql> set global init_connect='insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now());';
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'init_connect';
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                         |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| init_connect  | insert into auditdb.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now()); |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)

 

四、work用户登录：

mysql> select * from auditdb.accesslog ;
+----+--------------+----------------+-----------+---------------------+
| id | Connectionid | ConnUser       | MatchUser | Logintime           |
+----+--------------+----------------+-----------+---------------------+
|  1 |           29 | work@localhost | work@%    | 2023-02-03 21:29:42 |
|  2 |           30 | work@localhost | work@%    | 2023-02-03 21:30:34 |
|  3 |           31 | work@localhost | work@%    | 2023-02-03 21:30:37 |
+----+--------------+----------------+-----------+---------------------+
3 rows in set (0.00 sec)