使用觸發(fā)器實現對Oracle 8i 9i的細粒度審計

使用觸發(fā)器實現對Oracle8i/9i的細粒度審計摘要對于Oracle8i/9i來說，自帶的審計手段只能捕獲到“誰”執(zhí)行此操作，而不能捕獲執(zhí)行了“什么”，審計的內容無法顯示數據的變化情況，很難滿足細粒度審計的要求。所以目前來講，在特定的位置配置相應的觸發(fā)器用來記錄和跟蹤數據前后的變化，成為在這兩個版本的Oracle數據庫中做到細粒度審計的唯一可靠的方法。本文介紹了使用觸發(fā)器的基本的思路，以及幾種典型的觸發(fā)器，如對DDL，DML操作和其他的幾種觸發(fā)器的編寫。從技術和實踐的角度闡述了此種審計手段的功能和可行性。AuditingOracle8i/9iinParticularbyTriggerNetworkSecurityDivisionNeusoftCo.,Ltd.SummaryIntheOracle8i/9i,somecommonauditmethodscanonlylogthatwhohavedonewhichoperationinatable.Forexample,theadministratorcanfindthatausercalled"hacker"haddonesomeupdateinaimportanttable"boss”,butnothingwasloggedaboutwhatthedatahadbeenchangedto.Sowecanmakesome"trigger"inthesystem,whichworksasalogger,theycanrecordthechangesinparticular.Thisarticleisaboutwhatthetriggeris,howtowriteatriggerandhowitworks.關鍵詞：ORACLE數據庫，觸發(fā)器，審計；ORACLE，trigger，audit.緒論Oracle是以高級結構化查詢語言(SQL)為基礎的大型關系型數據庫，是目前最流行的客戶/服務器體系結構的數據庫之一。以其良好的性能和穩(wěn)定的表現，成為市場占有率最高的大型數據庫產品。隨著Oracle廣泛的應用，用戶對數據庫中數據保護和操作監(jiān)控的意識逐漸增強，越來越多的用戶提出了更高的安全方面的要求。Oracle也一直在加強數據庫產品在審計和日志記錄方面的能力，如在8i之后，加入了對歸檔日志的分析工具一logminer，在9i中加入了FGA(細粒度審計)的模塊，最終在Oracle10g產品中實現了對整體數據的詳細審計功能。雖然如此，在Oracle8i/9i的審計功能還不是很完善，本文介紹了如何在上述版本的Oracle產品中實現對數據的及時和詳盡的審計跟蹤和日志記錄。l.l.Oracle8i/9i的日志和審計功能logminer分析工具Oracle自身具有很多的日志，如監(jiān)聽器的日志、管理員登陸的日志、每次啟動的配置日志和錯誤日志等等，分別存放在不同文件夾內。而對于數據的更改和數據定義等操作的記錄，Oracle提供了一個強大的日志分析工具logminer。[1]logminer是Oracle在8i之后推出的一個對數據庫的歸檔文件的分析器。歸檔文件記錄了Oracle所有的數據變化的情況，只有在數據庫工作在歸檔模式（archive）下的時候才會生成，而默認Oracle是在非歸檔（noarchive）模式下的。工作在歸檔模式的好處是，只要有相應的歸檔文件，就可以把數據恢復到任意時刻的狀態(tài)。logminer就是對這些歸檔文件進行分析，就可以得到數據在過去時間的所有的更改情況。logminer分析之后會得到一張表v$logmnr_contents，記錄了詳盡信息，可以使用SQL語句進行查詢。如查詢某段時間里，特定的表的數據有那些變化等。使用logminer工具分析的過程比較復雜，8i與9i之間也有一些細節(jié)上的差別。對具體的配置和實現的方法，請查閱相關Oracle的管理文檔。審計功能Oracle8i/9i自帶的審計（audit）功能在默認的狀態(tài)下也是不被開啟的。可以根據需要，設置對不同的數據庫操作進行審計記錄。有三種類型的審計操作：登錄嘗試、對象存取（具體對象上的具體語句）和數據庫操作（具體的系統(tǒng)特權和語句，不考慮對象）。FGA在Oracle9i之中，引入了一個新的概念FGA（細粒度審計），從Oracle9i開始，提供了一個DBMS_FGA包，可以在線對單個的表進行審計并查詢審計資料。但是這個包的審計過程要求數據庫運行在CBO優(yōu)化模式下，如果不是，可能會有意想不到的結果。Oracle8i/9i自帶功能的不足以上提到的Oracle8i/9i自帶的日志和審計功能，似乎已經可以很好的滿足需要了，其實不然，以上的功能的都存在各自的缺陷，配合使用也在一定程度上無法滿足需要?；趌ogminer的分析要求數據庫工作在歸檔模式下，而此種工作模式會記錄整個數據庫每次數據的變化，會很大的增加服務器負擔，如果數據的變更比較頻繁，歸檔的文件也就越大。即時在允許這種模式的情況下，每一次的分析過程都要生成新的字典文件，對歸檔的二進制文件進行分析，這個過程是很占系統(tǒng)資源的。雖然可以異地分析，但是需要復制數據字典，歸檔文件到另一臺相同版本的Oracle服務器上再進行分析。所以說，使用logminer工具，側重于對整個數據庫變更的全面的事后分析，操作比較復雜，對服務器的負擔比較重，而且很難滿足對審計功能的實時性的要求?；趯徲嫷姆椒ㄏ鄬碚f要簡單的多，系統(tǒng)會把需要的日志實時記錄在特定的表中，管理員只要查詢相應的表或視圖，就可以了解審計信息。但是，在Oracle9i數據庫及其較低版本中，審計只能捕獲“誰”執(zhí)行此操作，而不能捕獲執(zhí)行了“什么"內容。如圖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用戶在表中執(zhí)行過刪除和插入操作，但是無法顯示出具體的數據變化情況，無法滿足我們對數據的細粒度審計的要求。而9i中的FGA也只可以跟蹤到selelct語句的數據情況，而不能用于DML，如update、insert和delete語句。因此，對于Oracle數據庫10g之前的版本，使用自帶的功能很難滿足對細粒度審計的實時性和準確性的要求。于是手動的設置觸發(fā)器跟蹤用戶的行級的更改，成為一種在8i和9i中可靠的審計手段。觸發(fā)器觸發(fā)器（trigger）是存儲過程，當發(fā)生特定的動作時，就會激活它。觸發(fā)器可以被編碼，當針對一個表進行插入、更新、刪除或三種操作的結合時，激活觸發(fā)器，也可以在某行被影響或某條語句出現時被激活。觸發(fā)器經常用于加強數據完整性約束和業(yè)務規(guī)則，這些業(yè)務規(guī)則對于內建的Oracle引用完整性約束來說實在是太復雜了。關于數據庫觸發(fā)器的信息可以在DBA_TRIGGERS視圖中找到。觸發(fā)器的功能?安全性可以基于數據庫的值使用戶具有操作數據庫的某種權利?？梢曰跁r間限制用戶的操作，例如不允許下班后和節(jié)假日修改數據庫數據。可以基于數據限制用戶的操作，例如不允許刪除特定的表或表中的記錄。?審計可以跟蹤用戶對數據庫的操作。審計用戶操作數據庫的語句。把用戶對數據庫的更新寫入審計表。?實現復雜的數據完整性規(guī)則*在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行。在修改或刪除時把其它表中的與之匹配的行設成NULL值。在修改或刪除時把其它表中的與之匹配的行級聯(lián)設成缺省值。觸發(fā)器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。*?自動計算數據值如果數據的值達到了一定的要求，則進行特定的處理。*?同步實時地復制表中的數據觸發(fā)器的分類觸發(fā)器的類型是由觸發(fā)事務處理的類型和執(zhí)行該觸發(fā)器的級別來定義的。[3]分為：?行級觸發(fā)器(Row-Leveltrigger)對DML語句影響的每個行執(zhí)行一次。?語句級觸發(fā)器(statement-Leveltrigger)觸發(fā)器對每個DML語句執(zhí)行一次。如果一條insert語句在表中插入了50行，那么這個表上的語句級觸發(fā)器只執(zhí)行一次。?BEFORE和AFTER觸發(fā)器由于觸發(fā)器是事件驅動的，因此可以設置觸發(fā)器在這些事件之前或之后立即執(zhí)行。在觸發(fā)器種可以引用DML語句中涉及的舊值或新值。如對update操作，可以記錄下把原來的記錄更改成什么樣子。*?INSTEADOF觸發(fā)器可以指定Oracle要做的事情，而不是執(zhí)行原來的操作。比如將數據重定向到其它的表中。*?模式觸發(fā)器可以在模式級的操作上建立觸發(fā)器，如createtable、altertable和droptable等。甚至可以用來防止特定的表被刪除。此類觸發(fā)器主要提供兩種功能：阻止DDL操作以及在發(fā)生DDL操作時提供額外的安全監(jiān)控。*?數據庫級觸發(fā)器可以創(chuàng)建在數據庫事件上的觸發(fā)器，包括錯誤、注冊、注銷、關閉和啟動3.Oracle中審計觸發(fā)器的設計主要介紹在Oracle審計中最重要的兩個觸發(fā)器的編寫DDL操作觸發(fā)器的設計第一步，創(chuàng)建存放日志的審計表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第二步，編寫觸發(fā)器匚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邛業(yè),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;在觸發(fā)器創(chuàng)建好之后，如果執(zhí)行了DDL操作，如:CREATEUSERrrtIDENTIFIEDBYpass;觸發(fā)器就會把相關的記錄寫入到審計表中，如：(3如呵』select*fromsystem.auditddlttTIMEIP_ADDRESSIJSER_NAMEDDL_TYPE□BJECT_TYPE□WN□BJECT_NAME?101/16/200522:07:336|SYSTEMCreateUSERRRTDML操作觸發(fā)器的設計第一步，創(chuàng)建相應的審計表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）■嬰蝠主戰(zhàn)名USER_NAMEVARCHARZC^O),//0raclACTION渺用CHARW〔1。），//幼作TABLE_NAMEVARCHAR迎如），〃表名□LD_NAMEVARCHAR2(2oo),//S"name"字段數棍□LD.JkGENUMBER,77原￥g葉字段數棍NEW_NAf^lE■:VARCHAR2E□口），//魅改后的"name"字段NEWWENUMBER）；//慘改后的哈字段敷椎第二步，創(chuàng)建DML觸發(fā)器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多種觸發(fā)器的配合使用以上的兩個觸發(fā)器記錄了審計工作中最重要的部分，跟蹤了數據庫的結構及數據的變化情況，另外還有一些事件也可以通過觸發(fā)器來實現，如用戶登陸的日志，對特定表的保護，以及數據庫的開啟和關閉的記錄等。會話記錄的觸發(fā)器下面是一個自動記錄用戶登陸和會話時間的觸發(fā)器創(chuàng)建記乖竟俗舶震器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;創(chuàng)他記束注■借觸發(fā)捋 _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防止誤操作的觸發(fā)器劍建對樺定表替止擁作的摭窟雄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>''記錄數據庫開啟/關閉的觸發(fā)器創(chuàng)建記錄開棺的觸反髀createorreplacetriggerDB_startup_trigerafterstartupondatabasebegin1nsert1ntosystem.audlt_DBlogVaiuesCs'Vsdate,^1startup1房end;/.創(chuàng)建記錄美陽的觸發(fā)器createorreplacetriggerDB_5hutdown_trigerbeforeshutdownondatabasebegin1nsert1ntotem.audlt_DblogvaluesCs'Vsdate,；?1shutdown'J;end;總結通過基于觸發(fā)器的審計，可以得到一份詳盡的數據庫歷史操作的記錄，合理的使用和配置會大大的提高數據庫本身的安全性，同時為管理者提供下列的功能：?查明數據庫的邏輯更改；踉?偵察并更正用戶的誤操作；踉?執(zhí)行事后審計；*?執(zhí)行變化分析。*以上討論了在Oracle8i/9i中如何使用多種觸發(fā)器實現對數據的細粒度的跟蹤審計，填補了Oracle8i/9i自帶審計功能的不足。雖然觸發(fā)器的使用會在一定的程度上影響程序的性能，但是到目前為止，這是一種較好地在上述兩個版本的數據庫中做到細粒度審計的方法。參考文獻oracle.oracleOCPbookDBA1.pdf:7-33胡欣杰.Oracle9i數據庫管理員指南，北京：北京希望電子出版社，2002：417[美]KevinLoney,GeorgeKoch.Oracle9i參考手冊.第一版（譯者：鐘鳴等），機械工業(yè)出版社，2003：410又一審計DML在生產環(huán)境中，總是可能出現這樣的情況：某張或者某些表的數據被莫名其妙的修改了，但是很難定位出是哪個用戶、哪個過程修改的。這是一個很讓DBA頭痛的事情(往往DBA對于整個代碼邏輯并不是非常了解)。要定位出“問題”語句，有幾種方法可以選擇：logminer；細節(jié)粒度審計；觸發(fā)器。Logminer要求要有歸檔日志(這個并非所有系統(tǒng)都可以做)，而且需要有相當的磁盤空間，好處就是可以離線做；細節(jié)粒度升級能夠根據條件記錄下表的DML操作(9i及之前只能記錄SELECT語句)，比較復雜的FGA需要較高權限的用戶來實現；觸發(fā)器比較靈活，能夠按照比較復雜的條件來記錄需要的信息。下面介紹觸發(fā)器如何實現。要建立這樣的觸發(fā)器，需要利用到幾張系統(tǒng)視圖：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); 觸發(fā)器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;第一個觸發(fā)器：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;第二個觸發(fā)器代碼：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)值來定位出發(fā)trigger的語句的，不能用sql_address和hash_value來定位，否則獲取到是觸發(fā)器里面向日志表插入記錄數據的語句本身了。第二個方法是通過通過地址加游標的方法，按照視圖各個字段的解釋，應該是可以通過v$sql_cursor.parent_handle來定位的。但是通過測試發(fā)現，只有當前一條語句和查找前一條語句的語句在一個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.因為觸發(fā)器本身是一個PLSQL塊，所以總是無法獲得正確語句，最后只有通過cursor的狀態(tài)來獲取。下面簡單了解一下CURSOR各個狀態(tài)的含義：CURNULL:游標已經存在，但沒有任何SQL語句在使用它(即cache在每個session內存中的游標)CURSYNTAX:解析SQL語句過程的一個游標狀態(tài)，說明調用游標的SQL語句語法正確，但是沒有解析完成。CURPARSE：調用游標的語句解析完畢? CURBOUND:游標使用了幫定變量，并定義好了幫定變量CURFETCH：游標執(zhí)行完畢，并fetch了數據CURROW:游標正指向某一行ERROR:游標錯誤，一般是有BUG了。當一條INSERT或者UPDATE語句執(zhí)行以后才會觸發(fā)觸發(fā)器，所以這時候的游標狀態(tài)是CURFETCH，我們這就通過狀態(tài)為CURFETCH來定位。比較郁悶的是，我的系統(tǒng)是的，因為存在BUG，這樣的觸發(fā)器做不了,FGA又無法監(jiān)控INSERT和UPDATE語句。還沒找到好的辦法createsequencemtt_num_seq;createsequencemember_row_track_seq;createorreplacepackagedml_countisv_mtt_countMember_Tran_Trace.Mtt_Count%TYPE:=0;end;第一個觸發(fā)器：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;第二個觸發(fā)器代碼：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：創(chuàng)建tr_trace_ddl，創(chuàng)建時可以通過，創(chuàng)建后顯示狀態(tài)不正常，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,以下兩個過程呢，其實是輔助過程，一個是完成寫日志，一個是完成發(fā)郵件，在以后的程序中，可能會經常用到。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