专栏首页爱可生开源社区技术分享 | 是谁删了表?

技术分享 | 是谁删了表?

作者:王少鹏

爱可生 DBA 团队成员,负责项目数据库日常问题处理及公司 DMP 平台问题处理,对数据库有强烈的兴趣。认为不会游泳的厨师绝不是一个好数据库工程师。

本文来源:原创投稿 *爱可生开源社区出品,原创内容未经授权不得随意使用,转载请联系小编并注明来源。


背景

某日某公司的测试数据库突发告警!

故障初步定位很可能是新来的几位实习生没有遵守运维规范,误操作(没加 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 权限的用户无效。

二、准备工作

2.1 init_connect 参数

mysql> show variables like 'init_connect';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| init_connect  |       |
+---------------+-------+
1 row in set (0.00 sec)


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 variables like 'init_connect';
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                                                                         |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| init_connect  | insert into auditd.accesslog(connectionID,ConnUser,MatchUser,LoginTime) values(connection_id(),user(),current_user(),now()); |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

既然是要 insert 一条数据,那是不是这个普通用户要对这张表拥有 insert 权限呢?

答案是肯定的。

2.2 连接信息表

mysql> create database auditdb charset utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> create table auditdb.accesslog (id int (10) unsigned not null primary key auto_increment,
       Connectionid int(10) unsigned,ConnUser varchar (30) not null default '',
       MatchUser varchar (30) not null default '',
       Logintime datetime);
Query OK, 0 rows affected (0.02 sec)

对所有用户都赋予 insert 权限

mysql> grant insert on auditdb.accesslog to mindoc@'%';
Query OK, 0 rows affected (0.00 sec)

注意:

  • 此方法需要给数据库所有用户都对 auditdb.accesslog 授写权限,否则插入用户信息会失败;
  • 不要授权 update 、delete 等权限,否则普通用户登录 MySQL 可以手动删除他连接的信息。

三、误删除实验

3.1 模拟误删除

[root@db ~]# mysql -u mindoc -p -h 172.18.1.76
Enter password:

mysql> create table temp(id int,name varchar(32));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into temp values(1,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp values(2,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> insert into temp values(3,'aa');
Query OK, 1 row affected (0.01 sec)

mysql> delete from temp;
Query OK, 3 rows affected (0.01 sec)

此时数据已经被删除了,你的应用程序应该会报错或开始告警。通过检查应用日志,可大致推断时间范围。

3.2 导出并分析 binlog 日志

[root@db ~]# mysqlbinlog -v --base64-output=decode-rows /usr/local/mysql-5.7.20/binlog/mysql-bin.000002 > audit.log

查看危险语句的进程 ID 号( 在解析后的 binlog 文件搜索危险命令关键字 )

通过执行的 delete 语句与大概执行时间,确定是哪个用户连接( thread_id=130 )

[root@db ~]# tail -35 audit.log
# at 49003
#191120 13:02:18 server id 76  end_log_pos 49080 CRC32 0x73dc1dda     Query    thread_id=130    exec_time=0    error_code=0
SET TIMESTAMP=1574226138/*!*/;
BEGIN
/*!*/;
# at 49080
#191120 13:02:18 server id 76  end_log_pos 49135 CRC32 0x360e7fe4     Table_map: `mindoc_db`.`temp` mapped to number 249
# at 49135
#191120 13:02:18 server id 76  end_log_pos 49194 CRC32 0xbbf0d78f     Delete_rows: table id 249 flags: STMT_END_F

BINLOG '
2sjUXRNMAAAANwAAAO+/AAAAAPkAAAAAAAEACW1pbmRvY19kYgAEdGVtcAACAw8CgAAD5H8ONg==
2sjUXSBMAAAAOwAAACrAAAAAAPkAAAAAAAEAAgAC//wBAAAAAmFh/AIAAAACYWH8AwAAAAJhYY/X
8Ls=
'/*!*/;
### DELETE FROM `mindoc_db`.`temp`
### WHERE
###   @1=1
###   @2='aa'
### DELETE FROM `mindoc_db`.`temp`
### WHERE
###   @1=2
###   @2='aa'
### DELETE FROM `mindoc_db`.`temp`
### WHERE
###   @1=3
###   @2='aa'
# at 49194
#191120 13:02:18 server id 76  end_log_pos 49225 CRC32 0x277ece0b     Xid = 23721
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

3.3 查看对应时间对应的用户信息

可以看到线程 threadid=130,并且时间也是 2019-11-20 中午一点左右。可以确定就是 mindoc@’%’ 用户操作的 delete 语句。

通过 ConnUser 字段可以看到是 172.18.1.99 这个地址使用 mindoc@’%’ 用户连接的 MySQL 数据库。

mysql> select * from auditdb.accesslog where Connectionid=130;
+----+--------------+--------------------+-----------+---------------------+
| id | Connectionid | ConnUser           | MatchUser | Logintime           |
+----+--------------+--------------------+-----------+---------------------+
|  1 |          130 | mindoc@172.18.1.99 | mindoc@%  | 2019-11-20 12:59:21 |
+----+--------------+--------------------+-----------+---------------------+
1 row in set (0.00 sec)

然后你就可以去找谁使用的 mindoc@% 用户了,还有定位下 172.18.1.99 这个 IP 地址谁在使用。

完成了通过行为定位用户的操作。

本文分享自微信公众号 - 爱可生开源社区(ActiontechOSS),作者:王少鹏

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-07-21

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 第09期:有关 MySQL 字符集的乱码问题

    相信大家通过前几篇文章,已经了解了 MySQL 字符集使用相关注意事项。那么数据乱码问题在这儿显得就非常简单了,或许说可能不会出现这样的问题。

    爱可生开源社区
  • 技术分享 | 如何使用 bcc 工具观测 MySQL 延迟

    爱可生测试团队成员,主要负责 TXLE 开源项目相关测试任务,擅长 Python 自动化测试开发,最近醉心于 Linux 性能分析优化的相关知识。

    爱可生开源社区
  • 第01期:MySQL 数据类型的艺术

    对于关系型数据库来说,表的设计 以及 SQL 的写法 尤为重要,占据性能的 90% 都不为过,所以这次专门针对这两大类知识领域,为您抽丝剥茧详细分析。

    爱可生开源社区
  • mac搭建web环境

    在centos上有yum,ubuntu上有apt,而mac则是brew。这个并不是内置的。需要在终端执行命令进行安装。命令如下:

    魔王卷子
  • jQuery打造智能提示插件

    插件根据实际需要在单功能上封装的,实现传入后台数据地址,要保存值的input,前台要传入的参数(过滤条件),来返回下拉提示数据,数据过多可上下滚动选择,选择后显...

    欢醉
  • 亚马逊CTO对云计算发展八大趋势的判断

    大数据文摘
  • 声明式服务调用-Feign

    博主在昨天的文章中,提及到项目中选用Eureka作为服务的注册中心,那么今天就应该是开始进行服务的调用, 即认证平台调用数据服务。在之前的文章中我都是很模糊的描...

    每天学Java
  • 快速开发基于AUTOSAR标准的驱动软件

    Automotive Open System Architecture (AUTOSAR),是汽车电子行业内解决软件复用问题的一种标准化软件架构,现在目前最新的...

    用户1605515
  • JPEG文件格式解析(一) Exif 与 JFIF

    而我们通常说的JPEG指的是以JPEG格式压缩的图片(即文件后缀为.jpeg .jpe )。经过JPEG重新编码的图片,文件压缩率可以达到90%以上,而且图片本...

    天天P图攻城狮
  • 阅面携手英特尔重磅发布“繁星”,计算机视觉迈入AI芯片新纪元!

    镁客网

扫码关注云+社区

领取腾讯云代金券