oracle 审计drop单张表,如何在无审计的环境中追踪Truncate/Drop等危险的DDL操作

男娘i 2023-01-18 06:59 144阅读 0赞

![01fac6460a22031b8af8da5848698a70.png][]

在有充分审计Audit   SQL的情况下，定位某条DROp/Truncate DDl还是比较容易的。

问题是 没有任何SQL审计时呢？

首先需要 明确的是 对于 关键的产品数据库系统而言 有效的审计非常重要，不能将以下的方法当做审计选项来用。

其次对于重要的环境和重要的对象， 一般推荐创建一个DDL Trigger 让直接运行的DDL报错，而需要先将对应的DDL Disable之后才能成功 执行DDL， 这样可以降低表/索引因为人为失误导致的误操作。

最简单的仍是通过 dba\_objects 定位其最后的DDL时间，虽然这个LAST\_DDL\_TIME 未必是真实的案发时间了， 但如何与应用程序报错结合起来 还是能大致了解问题发生的时间段的， 而这个时间段 对于问题追查至关重要。 此外对于DROP命令而言显然弄不到这个LAST\_DDL\_TIME。

接着可以通过ASH 视图 DBA\_HIST\_ACTIVE\_SESS\_HISTORY和 V$ACTIVE\_SESSION\_HISTORY来定位一些DDL语句， 由于ASH默认是1秒采样一次，所以如果遇到了一些例如RAC 中truncate/drop 常见的 DFS Lock Handle、Enqueue Lock等等待，那么一般ASH都能捕捉到这个DDL，当然这也看运气，毕竟ASH不是审计功能。

SQL\_OPCODE    12 为DROP TABLE  10为 DROP INDEX、85为TRUNCATE TABLE、86为TRUNCATE CLUSTER

select USER\_ID, SQL\_OPCODE, XID, SAMPLE\_TIME,MODULE,MACHINE

from v$active\_session\_history

where SQL\_OPCODE in (12, 10, 85, 86)

and SAMPLE\_TIME between xx and xx;

select USER\_ID, SQL\_OPCODE, XID, SAMPLE\_TIME,MODULE,MACHINE

from dba\_hist\_active\_sess\_history

where SQL\_OPCODE in (12, 10, 85, 86)

and SAMPLE\_TIME between xx and xx;

如果上述查询给出少量精准结果(例如MACHINE和MODULE很特殊)，那么一般就很容易定位了。  如果查出大量结果，一般优先排除应用程序模块例如 JDBC Thin，如果应用程序本身有BUG导致莫名的DDL，那么理论上应当经常发生。 如果看到一些例如SQLPLUS、PL/SQL Developer认为登陆执行上述命令的记录，则需要特别关注。

由于DDL语句不作为 共享SQL保存在V$SQL、V$SQLAREA中所以 就算你获得了SQL\_ID还是看不到这些SQL语句的，所以无法通过SQL\_TEXT来定位这些SQL到底是什么样子。

这个时候往往需要做Logminer了， 但好在我们有大致的Sample  Time和XID 这样定位SQL就很简单。

EXECUTE DBMS\_LOGMNR.ADD\_LOGFILE( LOGFILENAME => ‘/oracle/logs/log1.f’, OPTIONS => DBMS\_LOGMNR.NEW);

EXECUTE DBMS\_LOGMNR.ADD\_LOGFILE( LOGFILENAME => ‘/oracle/logs/log2.f’, OPTIONS => DBMS\_LOGMNR.ADDFILE);

execute DBMS\_LOGMNR.START\_LOGMNR(DBMS\_LOGMNR.DICT\_FROM\_ONLINE\_CATALOG+DBMS\_LOGMNR.COMMITTED\_DATA\_ONLY);

SELECT (XIDUSN || ‘.’ || XIDSLT || ‘.’ || XIDSQN) AS XID,

USERNAME, SQL\_REDO FROM V$LOGMNR\_CONTENTS WHERE XID=’XXX’;

EXECUTE DBMS\_LOGMNR.END\_LOGMNR;

此外在没有审计的情况下 值得参考的数据还有 Listener监听器的日志、OS登陆的Shell日志等。

[01fac6460a22031b8af8da5848698a70.png]: /images/20221021/2f8fe234dfc84b898919b729a4a14b59.png