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

使用触发器实现对Oracle8i/9i的细粒度审计摘要对于Oracle8i/9i来说，自带的审计手段只能捕获到“谁”执行此操作，而不能捕获执行了“什么”，审计的内容无法显示数据的变化情况，很难满足细粒度审计的要求。所以目前来讲，在特定的位置配置相应的触发器用来记录和跟踪数据前后的变化，成为在这两个版本的Oracle数据库中做到细粒度审计的唯一可靠的方法。本文介绍了使用触发器的基本的思路，以及几种典型的触发器，如对DDL，DML操作和其他的几种触发器的编写。从技术和实践的角度阐述了此种审计手段的功能和可行性。AuditingOracle8i/9iinParticularbyTriggerNetworkSecurityDivisionNeusoftCo.,Ltd.SummaryIntheOracle8i/9i,somecommonauditmethodscanonlylogthatwhohavedonewhichoperationinatable.Forexample,theadministratorcanfindthatausercalled"hacker"haddonesomeupdateinaimportanttable"boss”,butnothingwasloggedaboutwhatthedatahadbeenchangedto.Sowecanmakesome"trigger"inthesystem,whichworksasalogger,theycanrecordthechangesinparticular.Thisarticleisaboutwhatthetriggeris,howtowriteatriggerandhowitworks.关键词：ORACLE数据库，触发器，审计；ORACLE，trigger，audit.绪论Oracle是以高级结构化查询语言(SQL)为基础的大型关系型数据库，是目前最流行的客户/服务器体系结构的数据库之一。以其良好的性能和稳定的表现，成为市场占有率最高的大型数据库产品。随着Oracle广泛的应用，用户对数据库中数据保护和操作监控的意识逐渐增强，越来越多的用户提出了更高的安全方面的要求。Oracle也一直在加强数据库产品在审计和日志记录方面的能力，如在8i之后，加入了对归档日志的分析工具一logminer，在9i中加入了FGA(细粒度审计)的模块，最终在Oracle10g产品中实现了对整体数据的详细审计功能。虽然如此，在Oracle8i/9i的审计功能还不是很完善，本文介绍了如何在上述版本的Oracle产品中实现对数据的及时和详尽的审计跟踪和日志记录。l.l.Oracle8i/9i的日志和审计功能logminer分析工具Oracle自身具有很多的日志，如监听器的日志、管理员登陆的日志、每次启动的配置日志和错误日志等等，分别存放在不同文件夹内。而对于数据的更改和数据定义等操作的记录，Oracle提供了一个强大的日志分析工具logminer。[1]logminer是Oracle在8i之后推出的一个对数据库的归档文件的分析器。归档文件记录了Oracle所有的数据变化的情况，只有在数据库工作在归档模式（archive）下的时候才会生成，而默认Oracle是在非归档（noarchive）模式下的。工作在归档模式的好处是，只要有相应的归档文件，就可以把数据恢复到任意时刻的状态。logminer就是对这些归档文件进行分析，就可以得到数据在过去时间的所有的更改情况。logminer分析之后会得到一张表v$logmnr_contents，记录了详尽信息，可以使用SQL语句进行查询。如查询某段时间里，特定的表的数据有那些变化等。使用logminer工具分析的过程比较复杂，8i与9i之间也有一些细节上的差别。对具体的配置和实现的方法，请查阅相关Oracle的管理文档。审计功能Oracle8i/9i自带的审计（audit）功能在默认的状态下也是不被开启的。可以根据需要，设置对不同的数据库操作进行审计记录。有三种类型的审计操作：登录尝试、对象存取（具体对象上的具体语句）和数据库操作（具体的系统特权和语句，不考虑对象）。FGA在Oracle9i之中，引入了一个新的概念FGA（细粒度审计），从Oracle9i开始，提供了一个DBMS_FGA包，可以在线对单个的表进行审计并查询审计资料。但是这个包的审计过程要求数据库运行在CBO优化模式下，如果不是，可能会有意想不到的结果。Oracle8i/9i自带功能的不足以上提到的Oracle8i/9i自带的日志和审计功能，似乎已经可以很好的满足需要了，其实不然，以上的功能的都存在各自的缺陷，配合使用也在一定程度上无法满足需要。基于logminer的分析要求数据库工作在归档模式下，而此种工作模式会记录整个数据库每次数据的变化，会很大的增加服务器负担，如果数据的变更比较频繁，归档的文件也就越大。即时在允许这种模式的情况下，每一次的分析过程都要生成新的字典文件，对归档的二进制文件进行分析，这个过程是很占系统资源的。虽然可以异地分析，但是需要复制数据字典，归档文件到另一台相同版本的Oracle服务器上再进行分析。所以说，使用logminer工具，侧重于对整个数据库变更的全面的事后分析，操作比较复杂，对服务器的负担比较重，而且很难满足对审计功能的实时性的要求。基于审计的方法相对来说要简单的多，系统会把需要的日志实时记录在特定的表中，管理员只要查询相应的表或视图，就可以了解审计信息。但是，在Oracle9i数据库及其较低版本中，审计只能捕获“谁”执行此操作，而不能捕获执行了“什么"内容。如图1-1Query!|seXect*from^iBA_AUDIT_0BJECT;4jUSERNAMETERMINALTIMESTAMPOWNERCi虬岫EMTtCi虬帕ME1SYSTEMCLMOt/13Z20Ete：^s：11SYSTEMHACKER..TESTSESSIONREC2SYSTEM|CLM[01/1SQ0Q516.56:47SYSTEMHACKER_JESTDELETE3\SYSTEMCLM0VI3^300510-53:34SYSTEMHACKER_TESTINSERT图1-1可见，审计跟踪到system用户在表中执行过删除和插入操作，但是无法显示出具体的数据变化情况，无法满足我们对数据的细粒度审计的要求。而9i中的FGA也只可以跟踪到selelct语句的数据情况，而不能用于DML，如update、insert和delete语句。因此，对于Oracle数据库10g之前的版本，使用自带的功能很难满足对细粒度审计的实时性和准确性的要求。于是手动的设置触发器跟踪用户的行级的更改，成为一种在8i和9i中可靠的审计手段。触发器触发器（trigger）是存储过程，当发生特定的动作时，就会激活它。触发器可以被编码，当针对一个表进行插入、更新、删除或三种操作的结合时，激活触发器，也可以在某行被影响或某条语句出现时被激活。触发器经常用于加强数据完整性约束和业务规则，这些业务规则对于内建的Oracle引用完整性约束来说实在是太复杂了。关于数据库触发器的信息可以在DBA_TRIGGERS视图中找到。触发器的功能•安全性可以基于数据库的值使用户具有操作数据库的某种权利。可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。可以基于数据限制用户的操作，例如不允许删除特定的表或表中的记录。•审计可以跟踪用户对数据库的操作。审计用户操作数据库的语句。把用户对数据库的更新写入审计表。•实现复杂的数据完整性规则*在修改或删除时级联修改或删除其它表中的与之匹配的行。在修改或删除时把其它表中的与之匹配的行设成NULL值。在修改或删除时把其它表中的与之匹配的行级联设成缺省值。触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。*・自动计算数据值如果数据的值达到了一定的要求，则进行特定的处理。*•同步实时地复制表中的数据触发器的分类触发器的类型是由触发事务处理的类型和执行该触发器的级别来定义的。[3]分为：•行级触发器(Row-Leveltrigger)对DML语句影响的每个行执行一次。•语句级触发器(statement-Leveltrigger)触发器对每个DML语句执行一次。如果一条insert语句在表中插入了50行，那么这个表上的语句级触发器只执行一次。•BEFORE和AFTER触发器由于触发器是事件驱动的，因此可以设置触发器在这些事件之前或之后立即执行。在触发器种可以引用DML语句中涉及的旧值或新值。如对update操作，可以记录下把原来的记录更改成什么样子。*•INSTEADOF触发器可以指定Oracle要做的事情，而不是执行原来的操作。比如将数据重定向到其它的表中。*•模式触发器可以在模式级的操作上建立触发器，如createtable、altertable和droptable等。甚至可以用来防止特定的表被删除。此类触发器主要提供两种功能：阻止DDL操作以及在发生DDL操作时提供额外的安全监控。*•数据库级触发器可以创建在数据库事件上的触发器，包括错误、注册、注销、关闭和启动3.Oracle中审计触发器的设计主要介绍在Oracle审计中最重要的两个触发器的编写DDL操作触发器的设计第一步，创建存放日志的审计表CREATETABLESYSTEM.AUDIT_DDLC+JTIMEDATE,//^f间NSESSION_IDNUMBER,//^话ID+JOS_USERVARCHAR2(200),//终端口3用户+JIP_^DDRESSVARCHAR2(2W)恳摩端IP地址+JTERMINALVARCHAR2(200),HOSTVARCHAR2C200) 端主机名+JUSER_NAMEVARCHAR2(3O),//0racle用户名PDDL_T/PEVARCHAR2(3O),//DDL操作的类型卜OBJECT_TVPE仰.RCHARN(IE),//操作对象的类型WOWNERVARCHAR2(粕)</问象的所有者+JOBJECT_NAMEVARCHAR2C1280kV鬲象的名称+J第二步，编写触发器匚REA7EOFREPLACETRIEGER5V5.DDL_TFT5GERafterDDLondmtmbaw白deciareLiser.varvai'charsc^oo).;■ip_add_»arvarchar2(300)jterrrnra1_vai-:Varchar2(2OO)j-host_var■varchars(200);session.!d_varnurrter;b*】nselectsy5_contextCuserenv','os_U5ER:调弭oracle幽输君重应力借自.jys^cont^xtC'usERENV'r'ipjddress'),syr_Con 'USERENV'?'TERM邛业,J,Jsys^contEKt(,U5ERENV,,'HOST'/55J：s_contEKt('USERENV',,5E55TON7T'Jintouser_war3ip_add_varsterminal_war3ho5t_uar3SE^siori_.d_varfromdu2;■insertintosystem.audit_ddlC丁/布待旦曲偕辱帽A即cj■■戒中77幡,5E55™_TDt05_U5ER,TP_AC0RE55,IERMU1RL,1105T?ISLRJIAhE,LDL-TYPE,03JECT-TYPE,WNER,oaJECT-N^rt)：愠111白5.Xsysdate丫,sassTon-id_varfwsar_wir,ip_3.dd_v^r?tarminal_ :host_^3.r,orzi_log-iusarfora^sysEuent,oraddict_obj_type,oraddict_obj_owier；oraddict_obj_nsn)e旭end;在触发器创建好之后，如果执行了DDL操作，如:CREATEUSERrrtIDENTIFIEDBYpass;触发器就会把相关的记录写入到审计表中，如：(3如呵』select*fromsystem.auditddlttTIMEIP_ADDRESSIJSER_NAMEDDL_TYPE□BJECT_TYPE□WN□BJECT_NAME►101/16/200522:07:336|SYSTEMCreateUSERRRTDML操作触发器的设计第一步，创建相应的审计表DML的审计和DDL的审计不同，它的字段和需要审计的表的结构有关。如目标的表rrt.test有两个字段name和age,则审计表设计成：CREATETABLESYSTEM.AUDIT_RR7L_TE5T_DML〔TIMEDATE.//H间戳SESSION_1DNUMBER,//会德IDOS_USERVARCHAR2C^OO) OS户IP_ADDRESSVARCHAR2C^OO) IPTERMINALVARCHAR2(200),//■舞端HOSTVARCHAR2〔NDd）■婴蝠主战名USER_NAMEVARCHARZC^O),//0raclACTION渺用CHARW〔1。），//幼作TABLE_NAMEVARCHAR迎如），〃表名□LD_NAMEVARCHAR2(2oo),//S"name"字段数棍□LD.JkGENUMBER,77原¥g叶字段数棍NEW_NAf^lE■:VARCHAR2E□口），//魅改后的"name"字段NEWWENUMBER）；//惨改后的哈字段敷椎第二步，创建DML触发器CREATEORREPLACETRIGGERSYS.RRT.TEST.DML^FP.IGGERbeforeupdateorinsertordeleteonrrt.testforeachrowdeclareuser_varvarchar2(2OO);p_addj\/arvarcharz(200);terminail_varvarcharz(200);host_varvarcharZC2OO);sessioruldnumber;beglnselectsys-ContextfUSEREI^/'?'OS-USER*3,sy5_contextC'userenv'51ip_address,sys-contextC'USERENV1?'TERMINAL'),sys-contextC'USERENV1?,FDST,)?5ys_contextC1userenv'51sessionid1)intouser_var?1p_add_var,termlnal_var,host_var?5ession_1fromdual;ifinsertingthen1nsert Into SYSTEM.AUDITJ^RTLTES*Ctime,action?tab!e_name?new_name,new_age?os_user?1p_addressminal5host,session_id5user_name)values(sysdate,11nsert','HACKER.TEST15:,:new.age,user,1p_add_var,termlnal_var,host_var,sesslQn_id,ora^logln_userend1f;1fupdatlngthen1nsert 1nto SYSTEM.AUDIT_R.RT_TES-Ctime,action,tab!e_name5old_name5old_age,new_nane,new_age,oer?1p_address,terrninal,host,sesslon_1d,user_name)values(sysdate,1update1,1hacker.test'5:,:old-age,:neiAe?:new.age,user_var,1p_add_yar,terminal_var,host_var,sessio,ora_login_userj;endIf;1fdeletingthen1nsert Into SYSTEM.AUDITJ5.RT_JTES-Ctime5action,tab!e_name5old_name5old_age,O5_user5ip_addressmlnal,host,sesslon_id?userjiameJvalues(sysdate,1delete','hacker.test15:,:old.age,u^er,1p_add_var,termlnal_var,host_var,sesslQn_1d,ora^login_userend1f;end;完成之后，就可以记录下对目标表的所有的DML操作。如图:select*fromSYSTEM.AUDIT_RRT_TEST_I：'ML;ttTIMEIP.ADDRESSUSEFLNAMEACTIONOLD.NAMEOLD.AGENEW.NAMENEW-AGE101/18/200522:54:026SYSTEMinsertwang252□1/1BZ200522:55:141：93;1-68.75-.96SYSTEMupdatewang布wang28301/18/200522:58:546SYSTEMdeletewang28图3-2多种触发器的配合使用以上的两个触发器记录了审计工作中最重要的部分，跟踪了数据库的结构及数据的变化情况，另外还有一些事件也可以通过触发器来实现，如用户登陆的日志，对特定表的保护，以及数据库的开启和关闭的记录等。会话记录的触发器下面是一个自动记录用户登陆和会话时间的触发器创建记乖竟俗舶震器createorreplacetriggersy-^.logon_audit_triggerAFTERLOGONONDATABASEdeclareuser_warva「匚har2C2GOJ;■ip_add_varvarcha.r2(2QQ)jterrrnnal_varvarchar2(2OO)j-.ho5t_varvar匚h己「E(ECX)〕；se55ion_id^varnumber;beginseiect5ys_Qontext('U5EREMV's'aS.USER'),Ey£_contextCU5EEENV','IP_/JDDRE55'),5y5_contextCU5ERENV','TERMINALJ.sys.cont^tCUS'ER£NVr'^1HOST1)r£V5_contektCU5；ERENV",.'SESSIONID'Jintouser^Var,ip_add_var,terrrrinal host_var,5es£_ion_'id_varfromdual；.■insertinto^'y.stem.audit_loginvalues：se£sion_\妃^叽U5er_var,ip_add_var,t已「miri己1_va.ry；-hostjA^ar,user,sysdate,nul1,nullCOMMIT;END;创他记束注■借触发捋 _createorreplace±riggersys.1ogoH=-F_aud_it_triggerBEFORELOGOFFONDATABASE ' 'BEGINupdatesystem.aLidit_loginsetlogoff_date=sysdatewhere5y5_cor!textC'USERENV^,'EESSIONID'J=session_-id;updatesystem.aLid]±_.log'insetelapsed_nTinutes=round((logoff-date-logon_dat£^*1440)wheresys_cont^x-t(1USERENV','SESSIONIEj')=session_id;COMMIT；END:相应的审计表Que#1|select*fromsystem.auditlogin;uIP_ADDRE5S\USER_NAMELOGON_DATELOGOFF_DATEEU\PSED_MINUTES►[192168.75；96,SYSTEM□1/18/2005^3:47:57□1/WOT5^3:50:383图4-1防止误操作的触发器剑建对桦定表替止拥作的摭窟雄createorreplacetriggersys.ha匚keF_te5t_drizipbeforedropondataba^ebegin1f1ower（ora_d1匚已〔）〕 = 'test'1ower（ora_di匚j_。呻n已「［］）=1rrt'thenralse_applicatlon_error（num=>-20000,rri5g=>'yoljcannotdropthetab!e1||ora_dict_obj_nciirendifend>''记录数据库开启/关闭的触发器创建记录开棺的触反髀createorreplacetriggerDB_startup_trigerafterstartupondatabasebegin1nsert1ntosystem.audlt_DBlogVaiuesCs'Vsdate,^1startup1房end;/.创建记录美阳的触发器createorreplacetriggerDB_5hutdown_trigerbeforeshutdownondatabasebegin1nsert1ntotem.audlt_DblogvaluesCs'Vsdate,；?1shutdown'J;end;总结通过基于触发器的审计，可以得到一份详尽的数据库历史操作的记录，合理的使用和配置会大大的提高数据库本身的安全性，同时为管理者提供下列的功能：•查明数据库的逻辑更改；踉•侦察并更正用户的误操作；踉•执行事后审计；*•执行变化分析。*以上讨论了在Oracle8i/9i中如何使用多种触发器实现对数据的细粒度的跟踪审计，填补了Oracle8i/9i自带审计功能的不足。虽然触发器的使用会在一定的程度上影响程序的性能，但是到目前为止，这是一种较好地在上述两个版本的数据库中做到细粒度审计的方法。参考文献oracle.oracleOCPbookDBA1.pdf:7-33胡欣杰.Oracle9i数据库管理员指南，北京：北京希望电子出版社，2002：417[美]KevinLoney,GeorgeKoch.Oracle9i参考手册.第一版（译者：钟鸣等），机械工业出版社，2003：410又一审计DML在生产环境中，总是可能出现这样的情况：某张或者某些表的数据被莫名其妙的修改了，但是很难定位出是哪个用户、哪个过程修改的。这是一个很让DBA头痛的事情(往往DBA对于整个代码逻辑并不是非常了解)。要定位出“问题”语句，有几种方法可以选择：logminer；细节粒度审计；触发器。Logminer要求要有归档日志(这个并非所有系统都可以做)，而且需要有相当的磁盘空间，好处就是可以离线做；细节粒度升级能够根据条件记录下表的DML操作(9i及之前只能记录SELECT语句)，比较复杂的FGA需要较高权限的用户来实现；触发器比较灵活，能够按照比较复杂的条件来记录需要的信息。下面介绍触发器如何实现。要建立这样的触发器，需要利用到几张系统视图：v$session,v$sql,v$cursor，(10g,中可以，,9.2.0.之前存在bug)SQL>connect"/assysdba"grantselectonSYS.V_$SQLtodemo;grantselectonSYS.V_$SQL_BIND_DATAtodemo;grantselectonSYS.V_$SQL_CURSORtodemo;grantselectonSYS.V_$SESSIONtodemo;grantcreatetriggertodemo;CREATETABLEtrig_sql(ltDATE,sidNUMBER,SERIAL#NUMBER,USERNAMEVARCHAR2(30),OSUSERVARCHAR2(64),MACHINEVARCHAR2(32),TERMINALVARCHAR2(16),PROGRAMVARCHAR2(64),sqlTextVARCHAR2(2000),statusVARCHAR2(30));方法1：createorreplacetriggerttt_trigafterinsertorupdateonpga_tttDECLAREPRAGMAAUTONOMOUS_TRANSACTION;beginINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,NONE,fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;end; 2008-04-22——改进版一—createtableTRIG_SQL(LT DATE,SID NUMBER,SERIAL#NUMBER,USERNAMEVARCHAR2(30),OSUSERVARCHAR2(64),MACHINEVARCHAR2(32),TERMINALVARCHAR2(16),PROGRAMVARCHAR2(64),SQLTEXTVARCHAR2(2000),STATUSVARCHAR2(30),CLIENT_IPVARCHAR2(60))tablespaceSYSTEMpctfree10pctused40initrans1maxtrans255storage(initial64minextents1maxextentsunlimited); 触发器createorreplacetriggerttt_trigafterinsertorupdateordeleteonscott.schemaDECLAREPRAGMAAUTONOMOUS_TRANSACTION;BEGINIFinsertingTHENINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,INSERT,fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ELSIFdeletingthenINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,DELETE,fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ELSIFupdatingthenINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,UPDATE,fromv$sqlq,v$sessionswheres.audsid=(selectuserenv(,SESSIONID,)fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ENDIF;END; 改进版二加ClientIP地址createorreplacetriggerttt_trigafterinsertorupdateordeleteonscott.empDECLAREPRAGMAAUTONOMOUS_TRANSACTION;BEGINIFinsertingTHENINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,INSERT,,sys_context('userenv','ip_address')fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ELSIFdeletingthenINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,DELETE',sys_context('userenv','ip_address')fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ELSIFupdatingthenINSERTINTOtrig_sqlselectsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,,UPDATE',sys_context('userenv','ip_address')fromv$sqlq,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;ENDIF;END; 简化版3createorreplacetriggerttt_trigafterinsertorupdateordeleteonscott.empDECLAREPRAGMAAUTONOMOUS_TRANSACTION;DML_TYPEVARCHAR2(20);BEGINIFinsertingTHENDML_TYPE:='INSERT';ELSIFdeletingTHENDML_TYPE:='DELETE';ELSIFupdatingTHENDML_TYPE:='UPDATE';ENDIF;INSERTINTOtrig_sqlSELECTsysdate,s.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,DML_TYPE,sys_context('userenv','ip_address')FROMv$sqlq,v$sessionsWHEREs.audsid=(SELECTuserenv('SESSIONID')FROMdual)ANDs.prev_sql_addr=q.addressANDs.PREV_HASH_VALUE=q.hash_value;COMMIT;END; 20080421createsequencemtt_num_seq;createsequencemember_row_track_seq;createorreplacepackagedml_countisv_mtt_countMember_Tran_Trace.Mtt_Count%TYPE:=0;end;第一个触发器：createorreplacetriggertrig_member_rowafterinsertordeleteorupdateonmemberforeachrowbeginifinsertingtheninsertintomember_row_track(member_id,name,hire,jikwi,sal,bonus,mgr,dept_id,op_time,mttnum)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;ELSIFdeletingtheninsertintomember_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);dml_count.v_mtt_count:二dml_count.v_mtt_count+1;ELSIFupdatingtheninsertintomember_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);dml_count.v_mtt_count:=dml_count.v_mtt_count+1;ENDIF;end;第二个触发器代码：createorreplacetriggertrig_member_statementafterinsertordeleteorupdateonmemberbeginifinsertingTHENINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'insert',dml_count.v_mtt_count);ELSIFdeletingthenINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'delete',dml_count.v_mtt_count);ELSIFupdatingthenINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'update',dml_count.v_mtt_count);ENDIF;end;3,wanjiasheng:memberwanjiasheng:member_tran_tracewanjiasheng:member_row_track方法2：createorreplacetriggerttt_trigafterinsertorupdateonpga_tttDECLAREPRAGMAAUTONOMOUS_TRANSACTION;beginforcrin(selects.SID,s.SERIAL#,s.USERNAME,s.OSUSER,s.MACHINE,s.TERMINAL,s.PROGRAM,q.sql_textline,c.statusstatfromv$sqlq,v$sql_cursorc,v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressANDc.STATUS='CURFETCH')loopINSERTINTOtrig_sqlVALUES(SYSDATE,cr.sid,cr.SERIAL#,cr.USERNAME,cr.OSUSER,cr.MACHINE,cr.TERMINAL,cr.PROGRAM,cr.line,cr.stat);endloop;COMMIT;end;第一种方法是通过前一SQL的地址(pre_sql_addr)和HASH(prev_hash_value)值来定位出发trigger的语句的，不能用sql_address和hash_value来定位，否则获取到是触发器里面向日志表插入记录数据的语句本身了。第二个方法是通过通过地址加游标的方法，按照视图各个字段的解释，应该是可以通过v$sql_cursor.parent_handle来定位的。但是通过测试发现，只有当前一条语句和查找前一条语句的语句在一个PLSQL块中的时候才有效，SQL>setserveroutputonSQL>declarev_datedate;v_sqlvarchar2(2000);beginselectsysdateintov_date from dual;6selectq.sql_textintov_sqlfromv$sqlq,v$sql_cursor c, v$sessionswheres.audsid=(selectuserenv('SESSIONID')fromdual)ands.prev_sql_addr=q.addressandq.address二c.parent_handle;11dbms_output.put_line(v_sql);end;/SELECTSYSDATEFROMDUALPL/SQLproceduresuccessfullycompleted.因为触发器本身是一个PLSQL块，所以总是无法获得正确语句，最后只有通过cursor的状态来获取。下面简单了解一下CURSOR各个状态的含义：CURNULL:游标已经存在，但没有任何SQL语句在使用它(即cache在每个session内存中的游标)CURSYNTAX:解析SQL语句过程的一个游标状态，说明调用游标的SQL语句语法正确，但是没有解析完成。CURPARSE：调用游标的语句解析完毕・ CURBOUND:游标使用了帮定变量，并定义好了帮定变量CURFETCH：游标执行完毕，并fetch了数据CURROW:游标正指向某一行ERROR:游标错误，一般是有BUG了。当一条INSERT或者UPDATE语句执行以后才会触发触发器，所以这时候的游标状态是CURFETCH，我们这就通过状态为CURFETCH来定位。比较郁闷的是，我的系统是的，因为存在BUG，这样的触发器做不了,FGA又无法监控INSERT和UPDATE语句。还没找到好的办法createsequencemtt_num_seq;createsequencemember_row_track_seq;createorreplacepackagedml_countisv_mtt_countMember_Tran_Trace.Mtt_Count%TYPE:=0;end;第一个触发器：createorreplacetriggertrig_member_rowafterinsertordeleteorupdateonmemberforeachrowbeginifinsertingtheninsertintomember_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;ELSIFdeletingtheninsertintomember_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);dml_count.v_mtt_count:=dml_count.v_mtt_count+1;ELSIFupdatingtheninsertintomember_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);dml_count.v_mtt_count:=dml_count.v_mtt_count+1;ENDIF;end;第二个触发器代码：createorreplacetriggertrig_member_statementafterinsertordeleteorupdateonmemberbeginifinsertingTHENINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'insert',dml_count.v_mtt_count);ELSIFdeletingthenINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'delete',dml_count.v_mtt_count);ELSIFupdatingthenINSERTINTOmember_tran_trace(mtt_num,mtt_user,mtt_action,mtt_count)VALUES(member_row_track_seq.nextval,user,'update',dml_count.v_mtt_count);ENDIF;end;3,wanjiasheng:memberwanjiasheng:member_tran_tracewanjiasheng:member_row_track用来记录审计所有的DDL操作1：事先在SYS下建了一个表ddl$traceo2：创建tr_trace_ddl，创建时可以通过，创建后显示状态不正常，CREATEORREPLACETRIGGERtr_trace_ddlAFTERddlONdatabaseDECLAREsql_textora_name_list_t;state_sqlddl$trace.ddl_sql%TYPE;BEGINFORiIN1..ora_sql_txt(sql_text)LOOPstate_sql:=state_sql||sql_text(i);ENDLOOP;INSERTINTOddl$trace(login_user,audsid,ipaddress,schema_user,schema_object,ddl_time,ddl_sql)VALUES(ora_login_user,’0’，ora_client_ip_address,ora_dict_obj_owner,ora_dict_obj_name,SYSDATE,state_sql);EXCEPTIONWHENOTHERSTHENsp_write_log('捕获DDL语句异常错误：’||SQLERRM);ENDtr_trace_ddl;3：在TOAD里面重新编译时，提示出错，RIGGERSYS.TR_TRACE_DDLOnline: 2PLS-00103:出现符号"〃在需要下列之一时：beginfunctionpackagepragmaproceduresubtypetypeuse<anidentifier><adouble-quoteddelimited-identifier>formcurrentcursor那位知道为是什么4,以下两个过程呢，其实是辅助过程，一个是完成写日志，一个是完成发邮件，在以后的程序中，可能会经常用到。1、写日志过程name:sp_Write_logparameter:textContextinvarchar2日志内容createdate:2003-06-01creater:chenjipingdesc:•写日志，把内容记到服务器指定目录下•必须配置Utl_file_dir初始化参数，并保证日志路径与Utl_file_dir路径一致或者是其中一个createorreplacePROCEDUREsp_Write_log(textContextVARCHAR2)ISfile_handle UTL_FILE.file_type;Write_content VARCHAR2(1024);Write_file_nameVARCHAR2(50);BEGIN—打开文件--Write_file_name:=rtrim(to_char(SYSDATE,'YYYY-MM-DD'))||'.log';Write_file_name:='db108_alert.log';file_handle:=UTL_FILE.FOPEN('/u01/product/admin/ora81/logs',Write_file_name,'a');Write_content:=to_char(SYSDATE,'yyyy-mm-ddhh24:mi:ss')||'||'||textContext;--写文件IFUTL_FILE.IS_OPEN(file_handle)THENUTL_FILE.PUT_LINE(file_handle,Write_content);ENDIF;--关闭文件UTL_FILE.Fclose(file_handle);EXCEPTIONWHENOTHERSTHENIFUTL_FILE.IS_OPEN(file_handle)THENUTL_FIL