如何在无审计的环境中追踪Truncate/Drop等危险的DDL操作

在有充分审计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日志等。

Comments

  1. stad1105 says

    非常有想法的技术研究,也有些实用性,支持下

stad1105进行回复 取消回复

*

沪ICP备14014813号-2

沪公网安备 31010802001379号