利用Oracle審計(jì)功能記錄數(shù)據(jù)庫(kù)操作

1、1、什么是審計(jì)審計(jì)（Audit)用于監(jiān)視用戶所執(zhí)行的數(shù)據(jù)庫(kù)操作，并且Oracle會(huì)將審計(jì)跟蹤結(jié)果存放到OS文件（默認(rèn)位置為$ORACLE_BASE/admin/$ORACLE_SID/adump/）或數(shù)據(jù)庫(kù)（存儲(chǔ)在system表空間中的SYS.AUD$表中，可通過(guò)視圖dba_audit_trail查看）中。默認(rèn)情況下審計(jì)是沒(méi)有開(kāi)啟的。不管你是否打開(kāi)數(shù)據(jù)庫(kù)的審計(jì)功能，以下這些操作系統(tǒng)會(huì)強(qiáng)制記錄：用管理員權(quán)限連接Instance；啟動(dòng)數(shù)據(jù)庫(kù)；關(guān)閉數(shù)據(jù)庫(kù)。2、和審計(jì)相關(guān)的兩個(gè)主要參數(shù)Audit_sys_operations：默認(rèn)為false，當(dāng)設(shè)置為true時(shí)，所有sys用戶（包括以sysdba,s

2、ysoper身份登錄的用戶）的操作都會(huì)被記錄，audit trail不會(huì)寫(xiě)在aud$表中，這個(gè)很好理解，如果數(shù)據(jù)庫(kù)還未啟動(dòng)aud$不可用，那么像conn /as sysdba這樣的連接信息，只能記錄在其它地方。如果是windows平臺(tái)，audti trail會(huì)記錄在windows的事件管理中，如果是linux/unix平臺(tái)則會(huì)記錄在audit_file_dest參數(shù)指定的文件中。Audit_trail：None：是默認(rèn)值，不做審計(jì)；DB：將audit trail 記錄在數(shù)據(jù)庫(kù)的審計(jì)相關(guān)表中，如aud$，審計(jì)的結(jié)果只有連接信息；DB_Extended：這樣審計(jì)結(jié)果里面除了連接信息還包含了當(dāng)時(shí)執(zhí)行

3、的具體語(yǔ)句；OS：將audit trail 記錄在操作系統(tǒng)文件中，文件名由audit_file_dest參數(shù)指定；XML：10g里新增的。注：這兩個(gè)參數(shù)是static參數(shù)，需要重新啟動(dòng)數(shù)據(jù)庫(kù)才能生效。3、審計(jì)級(jí)別當(dāng)開(kāi)啟審計(jì)功能后，可在三個(gè)級(jí)別對(duì)數(shù)據(jù)庫(kù)進(jìn)行審計(jì)：Statement(語(yǔ)句)、Privilege（權(quán)限）、object（對(duì)象）。Statement：按語(yǔ)句來(lái)審計(jì)，比如audit table 會(huì)審計(jì)數(shù)據(jù)庫(kù)中所有的create table,drop table,truncate table語(yǔ)句，alter session by cmy會(huì)審計(jì)cmy用戶所有的數(shù)據(jù)庫(kù)連接。Privilege：按權(quán)

4、限來(lái)審計(jì)，當(dāng)用戶使用了該權(quán)限則被審計(jì)，如執(zhí)行g(shù)rant select any table to a，當(dāng)執(zhí)行了audit select any table語(yǔ)句后，當(dāng)用戶a 訪問(wèn)了用戶b的表時(shí)（如select * from b.t）會(huì)用到select any table權(quán)限，故會(huì)被審計(jì)。注意用戶是自己表的所有者，所以用戶訪問(wèn)自己的表不會(huì)被審計(jì)。Object：按對(duì)象審計(jì)，只審計(jì)on關(guān)鍵字指定對(duì)象的相關(guān)操作，如aduit alter,delete,drop,insert on cmy.t by scott; 這里會(huì)對(duì)cmy用戶的t表進(jìn)行審計(jì)，但同時(shí)使用了by子句，所以只會(huì)對(duì)scott用戶發(fā)起的操作進(jìn)行

5、審計(jì)。注意Oracle沒(méi)有提供對(duì)schema中所有對(duì)象的審計(jì)功能，只能一個(gè)一個(gè)對(duì)象審計(jì)，對(duì)于后面創(chuàng)建的對(duì)象，Oracle則提供on default子句來(lái)實(shí)現(xiàn)自動(dòng)審計(jì)，比如執(zhí)行audit drop on default by access;后， 對(duì)于隨后創(chuàng)建的對(duì)象的drop操作都會(huì)審計(jì)。但這個(gè)default會(huì)對(duì)之后創(chuàng)建的所有數(shù)據(jù)庫(kù)對(duì)象有效，似乎沒(méi)辦法指定只對(duì)某個(gè)用戶創(chuàng)建的對(duì)象有效，想比trigger可以對(duì)schema的DDL進(jìn)行“審計(jì)”，這個(gè)功能稍顯不足。4、審計(jì)的一些其他選項(xiàng)by access / by session：by access 每一個(gè)被審計(jì)的操作都會(huì)生成一條audit trail。

6、 by session 一個(gè)會(huì)話里面同類型的操作只會(huì)生成一條audit trail，默認(rèn)為by session。whenever not successful：whenever successful 操作成功(dba_audit_trail中returncode字段為0) 才審計(jì),whenever not successful 反之。省略該子句的話，不管操作成功與否都會(huì)審計(jì)。5、和審計(jì)相關(guān)的視圖dba_audit_trail：保存所有的audit trail，實(shí)際上它只是一個(gè)基于aud$的視圖。其它的視圖dba_audit_session,dba_audit_object,dba_audit_

7、statement都只是dba_audit_trail的一個(gè)子集。dba_stmt_audit_opts：可以用來(lái)查看statement審計(jì)級(jí)別的audit options，即數(shù)據(jù)庫(kù)設(shè)置過(guò)哪些statement級(jí)別的審計(jì)。dba_obj_audit_opts,dba_priv_audit_opts視圖功能與之類似all_def_audit_opts：用來(lái)查看數(shù)據(jù)庫(kù)用on default子句設(shè)置了哪些默認(rèn)對(duì)象審計(jì)。6、取消審計(jì)將對(duì)應(yīng)審計(jì)語(yǔ)句的audit改為noaudit即可，如audit session whenever successful對(duì)應(yīng)的取消審計(jì)語(yǔ)句為noaudit session w

8、henever successful;8、實(shí)例講解8.1、激活審計(jì)sqlplus / as sysdba SQL> show parameter auditNAME TYPE VALUE- - -audit_file_dest string /u01/app/oracle/admin/ORCL/adumpaudit_sys_operations boolean FALSEaudit_syslog_level stringaudit_trail string NONESQL> alter system set audit_sys_operations=TRUE scope=spfil

9、e; -審計(jì)管理用戶(以sysdba/sysoper角色登陸)SQL> alter system set audit_trail=db_extended scope=spfile;SQL> startup force;SQL> show parameter auditNAME TYPE VALUE- - -audit_file_dest string /u01/app/oracle/admin/ORCL/adumpaudit_sys_operations boolean TRUEaudit_syslog_level stringaudit_trail string DB_EX

10、TENDED 8.2、開(kāi)始審計(jì)sqlplus / as sysdba-記錄對(duì)一個(gè)表的所有操作SQL> audit all on t_test;SQL> conn u_testSQL> select * from t_test;SQL> insert into u_test.t_test (c2,c5) values ('test1','2');SQL> commit;SQL> delete from u_test.t_test;SQL> commit;SQL> conn /as sysdbaSQL> sele

11、ct OS_USERNAME,username,USERHOST,TERMINAL,TIMESTAMP,OWNER,obj_name,ACTION_NAME,sessionid,os_process,sql_bind,sql_text from dba_audit_trail;sql> audit select table by u_test by access;如果在命令后面添加by user則只對(duì)user的操作進(jìn)行審計(jì),如果省去by用戶,則對(duì)系統(tǒng)中所有的用戶進(jìn)行審計(jì)(不包含sys用戶).例：AUDIT DELETE ANY TABLE; -審計(jì)刪除表的操作AUDIT DELETE A

12、NY TABLE WHENEVER NOT SUCCESSFUL; -只審計(jì)刪除失敗的情況AUDIT DELETE ANY TABLE WHENEVER SUCCESSFUL; -只審計(jì)刪除成功的情況AUDIT DELETE,UPDATE,INSERT ON user.table by test; -審計(jì)test用戶對(duì)表user.table的delete,update,insert操作8.3、撤銷審計(jì)SQL> noaudit all on t_test;9、審計(jì)語(yǔ)句多層環(huán)境下的審計(jì)：appserve-應(yīng)用服務(wù)器，jackson-clientAUDIT SELECT TABLE BY ap

13、pserve ON BEHALF OF jackson;審計(jì)連接或斷開(kāi)連接：AUDIT SESSION;AUDIT SESSION BY jeff, lori; - 指定用戶審計(jì)權(quán)限(使用該權(quán)限才能執(zhí)行的操作)：AUDIT DELETE ANY TABLE BY ACCESS WHENEVER NOT SUCCESSFUL;AUDIT DELETE ANY TABLE;AUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;對(duì)象審計(jì)：AUDIT DEL

14、ETE ON jeff.emp;AUDIT SELECT, INSERT, DELETE ON jward.dept BY ACCESS WHENEVER SUCCESSFUL;取消審計(jì)：NOAUDIT session;NOAUDIT session BY jeff, lori;NOAUDIT DELETE ANY TABLE;NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE,EXECUTE PROCEDURE;NOAUDIT ALL; - 取消所有statement審計(jì)NOAUDIT ALL PRIVILEGES; - 取消所有權(quán)限審計(jì)NOAU

15、DIT ALL ON DEFAULT; - 取消所有對(duì)象審計(jì)10、清除審計(jì)信息DELETE FROM SYS.AUD$;DELETE FROM SYS.AUD$ WHERE obj$name='EMP'11、審計(jì)視圖STMT_AUDIT_OPTION_MAP - 審計(jì)選項(xiàng)類型代碼AUDIT_ACTIONS - action代碼ALL_DEF_AUDIT_OPTS - 對(duì)象創(chuàng)建時(shí)默認(rèn)的對(duì)象審計(jì)選項(xiàng)DBA_STMT_AUDIT_OPTS - 當(dāng)前數(shù)據(jù)庫(kù)系統(tǒng)審計(jì)選項(xiàng)DBA_PRIV_AUDIT_OPTS - 權(quán)限審計(jì)選項(xiàng)DBA_OBJ_AUDIT_OPTS USER_OBJ_AUDI

16、T_OPTS ; - 對(duì)象審計(jì)選項(xiàng)DBA_AUDIT_TRAIL USER_AUDIT_TRAIL - 審計(jì)記錄DBA_AUDIT_OBJECT USER_AUDIT_OBJECT - 審計(jì)對(duì)象列表DBA_AUDIT_SESSION USER_AUDIT_SESSION - session審計(jì)DBA_AUDIT_STATEMENT USER_AUDIT_STATEMENT - 語(yǔ)句審計(jì)DBA_AUDIT_EXISTS - 使用BY AUDIT NOT EXISTS選項(xiàng)的審計(jì)DBA_AUDIT_POLICIES - 審計(jì)POLICIESDBA_COMMON_AUDIT_TRAIL - 標(biāo)準(zhǔn)審計(jì)+精細(xì)審計(jì)12、將審計(jì)結(jié)果表從system表空間里移動(dòng)到別的表空間上實(shí)際上sys.aud$表上包含了兩個(gè)