使用触发器实现对Oracle 8i 9i的细粒度审计

1、使用触发器实现对Oracle 8i/9i的细粒度审计 摘要 对于Oracle 8i/9i来说，自带的审计手段只能捕获到“谁”执行此操作，而不能捕获执行了“什么”，审计的内容无法显示数据的变化情况，很难满足细粒度审计的要求。 所以目前来讲，在特定的位置配置相应的触发器用来记录和跟踪数据前后的变化，成为在这两个版本的Oracle数据库中做到细粒度审计的唯一可靠的方法。 本文介绍了使用触发器的基本的思路，以及几种典型的触发器，如对DDL，DML操作和其他的几种触发器的编写。从技术和实践的角度阐述了此种审计手段的功能和可行性。Auditing Oracle 8i/9i in Particular by

2、 TriggerNetwork Security Division Neusoft Co., Ltd.Summary In the Oracle 8i/9i, some common audit methods can only log that who have done which operation in a table. For example ,the administrator can find that a user called hacker had done some update in a important table boss, but nothing was logg

3、ed about what the data had been changed to. So we can make some trigger in the system ,which works as a logger,they can record the changes in particular. This article is about what the trigger is, how to write a trigger and how it works.关键词： ORACLE数据库，触发器，审计； ORACLE ，trigger ，audit.1.绪论 Oracle是以高级结构

4、化查询语言(SQL)为基础的大型关系型数据库，是目前最流行的客户/服务器体系结构的数据库之一。以其良好的性能和稳定的表现，成为市场占有率最高的大型数据库产品。 随着Oracle广泛的应用，用户对数据库中数据保护和操作监控的意识逐渐增强，越来越多的用户提出了更高的安全方面的要求。Oracle也一直在加强数 据库产品在审计和日志记录方面的能力，如在8i之后，加入了对归档日志的分析工具logminer，在9i中加入了FGA（细粒度审计）的模块，最终在 Oracle 10g产品中实现了对整体数据的详细审计功能。虽然如此，在Oracle 8i/9i的审计功能还不是很完善，本文介绍了如何在上述版本的Ora

5、cle产品中实现对数据的及时和详尽的审计跟踪和日志记录。1.1.Oracle 8i/9i的日志和审计功能1.1.1.logminer分析工具 Oracle自身具有很多的日志，如监听器的日志、管理员登陆的日志、每次启动的配置日志和错误日志等等，分别存放在不同文件夹内。而对于数据的更改和数据定义等操作的记录，Oracle提供了一个强大的日志分析工具logminer。1 logminer是Oracle在8i之后推出的一个对数据库的归档文件的分析器。归档文件记录了Oracle所有的数据变化的情况，只有在数据库工作在 归档模式（archive）下的时候才会生成，而默认Oracle是在非归档（noarch

6、ive）模式下的。工作在归档模式的好处是，只要有相应的归档 文件，就可以把数据恢复到任意时刻的状态。logminer就是对这些归档文件进行分析，就可以得到数据在过去时间的所有的更改情况。 logminer分析之后会得到一张表v$logmnr_contents，记录了详尽信息，可以使用SQL语句进行查询。如查询某段时间里，特定的表的 数据有那些变化等。使用logminer工具分析的过程比较复杂，8i与9i之间也有一些细节上的差别。对具体的配置和实现的方法，请查阅相关 Oracle的管理文档。1.1.2.审计功能 Oracle8i/9i自带的审计（audit）功能在默认的状态下也是不被开启的。2可

7、以根据需要，设置对不同的数据库操作进行审计记录。有三种类型的审计操作：登录尝试、对象存取（具体对象上的具体语句）和数据库操作（具体的系统特权和语句，不考虑对象）。1.1.3.FGA 在Oracle 9i之中，引入了一个新的概念FGA（细粒度审计），从Oracle9i开始，提供了一个DBMS_FGA包，可以在线对单个的表进行审计并查询审计资料。但是这个包的审计过程要求数据库运行在CBO优化模式下，如果不是，可能会有意想不到的结果。1.2.Oracle 8i/9i自带功能的不足 以上提到的Oracle 8i/9i自带的日志和审计功能，似乎已经可以很好的满足需要了，其实不然，以上的功能的都存在各自的

8、缺陷，配合使用也在一定程度上无法满足需要。 基于logminer的分析要求数据库工作在归档模式下，而此种工作模式会记录整个数据库每次数据的变化，会很大的增加服务器负担，如果数据的变更比较频 繁，归档的文件也就越大。即时在允许这种模式的情况下，每一次的分析过程都要生成新的字典文件，对归档的二进制文件进行分析，这个过程是很占系统资源的。 虽然可以异地分析，但是需要复制数据字典，归档文件到另一台相同版本的Oracle服务器上再进行分析。所以说，使用logminer工具，侧重于对整个 数据库变更的全面的事后分析，操作比较复杂，对服务器的负担比较重，而且很难满足对审计功能的实时性的要求。 基于审计的方法

9、相对来说要简单的多，系统会把需要的日志实时记录在特定的表中，管理员只要查询相应的表或视图，就可以了解审计信息。但是，在 Oracle 9i 数据库及其较低版本中，审计只能捕获“谁”执行此操作，而不能捕获执行了“什么”内容。如图1-1图1-1 可见，审计跟踪到system用户在表中执行过删除和插入操作，但是无法显示出具体的数据变化情况，无法满足我们对数据的细粒度审计的要求。 而9i中的FGA也只可以跟踪到selelct语句的数据情况，而不能用于 DML，如 update、insert 和 delete 语句。 因此，对于 Oracle 数据库 10g 之前的版本，使用自带的功能很难满足对细粒度审

10、计的实时性和准确性的要求。于是手动的设置触发器跟踪用户的行级的更改，成为一种在8i和9i中可靠的审计手段。2.触发器 触发器（trigger）是存储过程，当发生特定的动作时，就会激活它。触发器可以被编码，当针对一个表进行插入、更新、删除或三种操作的结合时，激活触 发器，也可以在某行被影响或某条语句出现时被激活。触发器经常用于加强数据完整性约束和业务规则，这些业务规则对于内建的Oracle引用完整性约束来说 实在是太复杂了。关于数据库触发器的信息可以在DBA_TRIGGERS 视图中找到。2.1.触发器的功能安全性 可以基于数据库的值使用户具有操作数据库的某种权利。 可以基于时间限制用户的操作，

11、例如不允许下班后和节假日修改数据库数据。 可以基于数据限制用户的操作，例如不允许删除特定的表或表中的记录。审计可以跟踪用户对数据库的操作。 审计用户操作数据库的语句。 把用户对数据库的更新写入审计表。实现复杂的数据完整性规则 在修改或删除时级联修改或删除其它表中的与之匹配的行。在修改或删除时把其它表中的与之匹配的行设成NULL值。在修改或删除时把其它表中的与之匹配的行级联设成缺省值。触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。自动计算数据值如果数据的值达到了一定的要求，则进行特定的处理。同步实时地复制表中的数据2.2.触发器的分类 触发器的类型是由触发事务处理的类

12、型和执行该触发器的级别来定义的。3分为：行级触发器（Row-Level trigger） 对DML语句影响的每个行执行一次。语句级触发器（statement-Level trigger） 触发器对每个DML语句执行一次。如果一条insert语句在表中插入了50行，那么这个表上的语句级触发器只执行一次。BEFORE和AFTER触发器 由于触发器是事件驱动的，因此可以设置触发器在这些事件之前或之后立即执行。在触发器种可以引用DML语句中涉及的旧值或新值。如对update操作，可以记录下把原来的记录更改成什么样子。INSTEAD OF触发器 可以指定Oracle要做的事情，而不是执行原来的操作。比如

13、将数据重定向到其它的表中。模式触发器 可以在模式级的操作上建立触发器，如create table、alter table和drop table等。甚至可以用来防止特定的表被删除。此类触发器主要提供两种功能：阻止DDL操作以及在发生DDL操作时提供额外的安全监控。数据库级触发器可以创建在数据库事件上的触发器，包括错误、注册、注销、关闭和启动。3.Oracle中审计触发器的设计 主要介绍在Oracle审计中最重要的两个触发器的编写3.1.DDL操作触发器的设计 第一步，创建存放日志的审计表第二步，编写触发器在触发器创建好之后，如果执行了DDL操作，如：CREATE USER rrt IDENTIF

14、IED BY pass;触发器就会把相关的记录写入到审计表中，如：3.2.DML操作触发器的设计 第一步，创建相应的审计表 DML的审计和DDL的审计不同，它的字段和需要审计的表的结构有关。 如目标的表rrt.test有两个字段name和age,则审计表设计成：第二步，创建DML触发器完成之后，就可以记录下对目标表的所有的DML操作。如图：图3-24.多种触发器的配合使用 以上的两个触发器记录了审计工作中最重要的部分，跟踪了数据库的结构及数据的变化情况，另外还有一些事件也可以通过触发器来实现，如用户登陆的日志，对特定表的保护，以及数据库的开启和关闭的记录等。4.1.会话记录的触发器 下面是一个

15、自动记录用户登陆和会话时间的触发器相应的审计表图4-14.2.防止误操作的触发器4.3.记录数据库开启/关闭的触发器5.总结 通过基于触发器的审计，可以得到一份详尽的数据库历史操作的记录，合理的使用和配置会大大的提高数据库本身的安全性，同时为管理者提供下列的功能：查明数据库的逻辑更改； 侦察并更正用户的误操作； 执行事后审计； 执行变化分析。 以上讨论了在Oracle 8i/9i中如何使用多种触发器实现对数据的细粒度的跟踪审计，填补了Oracle 8i/9i自带审计功能的不足。虽然触发器的使用会在一定的程度上影响程序的性能，但是到目前为止，这是一种较好地在上述两个版本的数据库中做到细粒度审计的

16、方法。6.参考文献1 oracle.oracle OCP book DBA1.pdf:7-332 胡欣杰.Oracle 9i数据库管理员指南，北京：北京希望电子出版社，2002：4173 美Kevin Loney,George Koch.Oracle 9i参考手册.第一版（译者：钟鸣等），机械工业出版社，2003：410又一审计DML在生产环境中，总是可能出现这样的情况：某张或者某些表的数据被莫名其妙的修改了，但是很难定位出是哪个用户、哪个过程修改的。这是一个很让DBA头 痛的事情（往往DBA对于整个代码逻辑并不是非常了解）。要定位出“问题”语句，有几种方法可以选择：log miner；细节粒

17、度审计；触发器。Log miner要求要有归档日志（这个并非所有系统都可以做），而且需要有相当的磁盘空间，好处就是可以离线做；细节粒度升级能够根据条件记录下表的DML操 作（9i及之前只能记录SELECT语句），比较复杂的FGA需要较高权限的用户来实现；触发器比较灵活，能够按照比较复杂的条件来记录需要的信息。下面 介绍触发器如何实现。要建立这样的触发器，需要利用到几张系统视图：v$session, v$sql, v$cursor，（10g, 中可以，, 9.2.0.之前存在bug）SQL connect / as sysdba grant select on SYS

18、.V_$SQL to demo; grant select on SYS.V_$SQL_BIND_DATA to demo; grant select on SYS.V_$SQL_CURSOR to demo; grant select on SYS.V_$SESSION to demo; grant create trigger to demo;CREATE TABLE trig_sql(lt DATE, sid NUMBER, SERIAL# NUMBER, USERNAME VARCHAR2(30), OSUSER VARCHAR2(64), MACHINE VARCHAR2(32),

19、TERMINAL VARCHAR2(16), PROGRAM VARCHAR2(64), sqlText VARCHAR2(2000), status VARCHAR2(30);方法1：create or replace trigger ttt_trig after insert or update on pga_tttDECLARE PRAGMA AUTONOMOUS_TRANSACTION;begin INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMIN

20、AL, s.PROGRAM, q.sql_text line, NONE from v$sql q, v$session s where s.audsid=(select userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT;end;-2008-04-22-改进版一-create table TRIG_SQL( LT DATE, SID NUMBER, SERIAL# NUMBER, USERNAME VARCHAR2(30), OSUSE

21、R VARCHAR2(64), MACHINE VARCHAR2(32), TERMINAL VARCHAR2(16), PROGRAM VARCHAR2(64), SQLTEXT VARCHAR2(2000), STATUS VARCHAR2(30), CLIENT_IP VARCHAR2(60)tablespace SYSTEM pctfree 10 pctused 40 initrans 1 maxtrans 255 storage ( initial 64 minextents 1 maxextents unlimited );-触发器create or replace trigger

22、 ttt_trig after insert or update or delete on scott.schemaDECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGIN IF inserting THEN INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, INSERT from v$sql q, v$session s where s.audsid=(sele

23、ct userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; ELSIF deleting then INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, DELETE from v$sql q, v$session s where s.aud

24、sid=(select userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; ELSIF updating then INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, UPDATE from v$sql q, v$session s wh

25、ere s.audsid=(select userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; END IF;END;-改进版二 加Client IP地址create or replace trigger ttt_trig after insert or update or delete on scott.empDECLARE PRAGMA AUTONOMOUS_TRANSACTION;BEGIN IF inserting THEN IN

26、SERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, INSERT, sys_context(userenv,ip_address) from v$sql q, v$session s where s.audsid=(select userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.

27、hash_value; COMMIT; ELSIF deleting then INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, DELETE, sys_context(userenv,ip_address) from v$sql q, v$session s where s.audsid=(select userenv(SESSIONID) from dual) and s.prev_sql

28、_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; ELSIF updating then INSERT INTO trig_sql select sysdate,s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, UPDATE, sys_context(userenv,ip_address) from v$sql q, v$session s where s.audsid=(select us

29、erenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT; END IF;END;- 简化版3create or replace trigger ttt_trig after insert or update or delete on scott.empDECLARE PRAGMA AUTONOMOUS_TRANSACTION; DML_TYPE VARCHAR2(20);BEGIN IF inserting THEN DML_TYPE:=INSE

30、RT; ELSIF deleting THEN DML_TYPE:=DELETE; ELSIF updating THEN DML_TYPE:= UPDATE; END IF; INSERT INTO trig_sql SELECT sysdate, s.SID, s.SERIAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, DML_TYPE, sys_context(userenv,ip_address) FROM v$sql q, v$session s WHERE s.audsid=

31、(SELECT userenv(SESSIONID) FROM dual) AND s.prev_sql_addr=q.address AND s.PREV_HASH_VALUE = q.hash_value; COMMIT;END;-create sequence mtt_num_seq;create sequence member_row_track_seq;create or replace package dml_count isv_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;end;第一个触发器：create or replace tr

32、igger trig_member_rowafter insert or delete or update on memberfor each rowbegin if inserting then insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:new.member_id,:,:new.hire,:new.jikwi,:new.sal,:new.bonus, :new.mgr,:new.dept_id,sysdate,m

33、tt_num_seq.nextval); dml_count.v_mtt_count:=dml_count.v_mtt_count+1; ELSIF deleting then insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:old.member_id,:,:old.hire,:old.jikwi,:old.sal, :old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq

34、.nextval); dml_count.v_mtt_count:=dml_count.v_mtt_count+1; ELSIF updating then insert into member_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:old.member_id,:,:old.hire,:old.jikwi,:old.sal, :old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval);

35、 dml_count.v_mtt_count:=dml_count.v_mtt_count+1; END IF;end;第二个触发器代码：create or replace trigger trig_member_statementafter insert or delete or update on memberbegin if inserting THEN INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count) VALUES (member_row_track_seq.nextval,user,inser

36、t,dml_count.v_mtt_count); ELSIF deleting then INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count) VALUES (member_row_track_seq.nextval,user,delete,dml_count.v_mtt_count); ELSIF updating then INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count) VALUES (member_row_

37、track_seq.nextval,user,update,dml_count.v_mtt_count); END IF;end;3,wan jia sheng :member wan jia sheng :member_tran_trace wan jia sheng :member_row_track-方法2：create or replace trigger ttt_trig after insert or update on pga_tttDECLARE PRAGMA AUTONOMOUS_TRANSACTION;begin for cr in (select s.SID, s.SER

38、IAL#, s.USERNAME, s.OSUSER, s.MACHINE, s.TERMINAL, s.PROGRAM, q.sql_text line, c.status stat from v$sql q, v$sql_cursor c, v$session s where s.audsid=(select userenv(SESSIONID) from dual) and s.prev_sql_addr=q.address AND c.STATUS = CURFETCH) loopINSERT INTO trig_sql VALUES(SYSDATE, cr.sid, cr.SERIA

39、L#, cr.USERNAME, cr.OSUSER, cr.MACHINE, cr.TERMINAL, cr.PROGRAM, cr.line, cr.stat); end loop; COMMIT;end;第一种方法是通过前一SQL的地址（pre_sql_addr）和HASH(prev_hash_value)值来定位出发trigger的语句的，不能用sql_address和hash_value来定位，否则获取到是触发器里面向日志表插入记录数据的语句本身了。第二个方法是通过通过地址加游标的方法，按照视图各个字段的解释，应该是可以通过v$sql_cursor.parent_handle来定位的

40、。但是通过测试发现，只有当前一条语句和查找前一条语句的语句在一个PLSQL块中的时候才有效，SQL set serveroutput onSQL declare 2 v_date date; 3 v_sql varchar2(2000); 4 begin 5 select sysdate into v_date from dual; 6 7 select q.sql_text into v_sql 8 from v$sql q, v$sql_cursor c, v$session s 9 where s.audsid=(select userenv(SESSIONID) from dual)1

41、0 and s.prev_sql_addr=q.address and q.address=c.parent_handle;1112 dbms_output.put_line(v_sql);13 end;14 /SELECT SYSDATE FROM DUALPL/SQL procedure successfully completed.因为触发器本身是一个PLSQL块，所以总是无法获得正确语句，最后只有通过cursor的状态来获取。下面简单了解一下CURSOR各个状态的含义： CURNULL：游标已经存在，但没有任何SQL语句在使用它（即cache在每个session内存中的游标） CURS

42、YNTAX：解析SQL语句过程的一个游标状态，说明调用游标的SQL语句语法正确，但是没有解析完成。 CURPARSE：调用游标的语句解析完毕 CURBOUND：游标使用了帮定变量，并定义好了帮定变量 CURFETCH：游标执行完毕，并fetch了数据 CURROW：游标正指向某一行 ERROR：游标错误，一般是有BUG了。当一条INSERT或者UPDATE语句执行以后才会触发触发器，所以这时候的游标状态是CURFETCH，我们这就通过状态为CURFETCH来定位。比较郁闷的是，我的系统是的，因为存在BUG，这样的触发器做不了，FGA又无法监控INSERT和UPDATE语句。还没找

43、到好的办法-create sequence mtt_num_seq;create sequence member_row_track_seq;create or replace package dml_count isv_mtt_count Member_Tran_Trace.Mtt_Count%TYPE:=0;end;第一个触发器：create or replace trigger trig_member_rowafter insert or delete or update on memberfor each rowbegin if inserting then insert into m

44、ember_row_track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:new.member_id,:,:new.hire,:new.jikwi,:new.sal,:new.bonus, :new.mgr,:new.dept_id,sysdate,mtt_num_seq.nextval); dml_count.v_mtt_count:=dml_count.v_mtt_count+1; ELSIF deleting then insert into member_row_

45、track (member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:old.member_id,:,:old.hire,:old.jikwi,:old.sal, :old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval); dml_count.v_mtt_count:=dml_count.v_mtt_count+1; ELSIF updating then insert into member_row_track (mem

46、ber_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mtt_num) values (:old.member_id,:,:old.hire,:old.jikwi,:old.sal, :old.bonus,:old.mgr,:old.dept_id,sysdate,mtt_num_seq.nextval); dml_count.v_mtt_count:=dml_count.v_mtt_count+1; END IF;end;第二个触发器代码：create or replace trigger trig_member_state

47、mentafter insert or delete or update on memberbegin if inserting THEN INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count) VALUES (member_row_track_seq.nextval,user,insert,dml_count.v_mtt_count); ELSIF deleting then INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_co

48、unt) VALUES (member_row_track_seq.nextval,user,delete,dml_count.v_mtt_count); ELSIF updating then INSERT INTO member_tran_trace ( mtt_num,mtt_user,mtt_action,mtt_count) VALUES (member_row_track_seq.nextval,user,update,dml_count.v_mtt_count); END IF;end;3,wan jia sheng :member wan jia sheng :member_tran_trace wa