oracle查看表被谁删了_技术分享是谁删了表？
作者：王少鹏
爱可⽣ 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 语句（参数值）。
init_connect 参数的功能：
注意：
开启 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/Xthread技术
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 地址谁在使⽤。
完成了通过⾏为定位⽤户的操作。
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论