Oracle審計例子

文章版權所有 Jusin Hao（luckyfriends） ，支援原創，轉載請註明

1. Oracle審計例子

1.1. 建立delete審計

--建立審計NC57模式下面bd_psndoc表的delete操作

Begin

Dbms_Fga.Add_Policy(Object_Schema => 'SCOTT',

Object_Name => 'T1',

Policy_Name => 'T1_audit_delete',

Statement_Types => 'delete');

End;

/

---檢視捕獲策略

Select * From Dba_Audit_Policies;

Order By Timestamp;

select * from t1 where id=4200;

delete t1 where id=4200;commit;

--檢視捕獲資訊

Select Timestamp, Userhost, Os_User, Db_User, Object_Schema, Object_Name, Statement_Type, Sql_Text, Policy_Name

From Dba_Fga_Audit_Trail

1.2. 建立insert升級

--建立審計scott模式下面bd_psndoc表的insert操作

Begin

Dbms_Fga.Add_Policy(Object_Schema => 'SCOTT',

Object_Name => 'T1',

Policy_Name => 'T1_audit_inset',

Statement_Types => 'insert');

End;

/

---檢視捕獲策略

Select a.object_schema,a.object_name,a.policy_name,a.Policy_Text,a.enabled,a.sel,a.ins,a.upd,a.del,a.audit_trail,a.policy_column_options From Dba_Audit_Policies a;

select * from t1 where id=4120;

select * from t1 where id=4120;

insert into t1 values ('4120',to_date('2002-12-25 16:26:12','YYYY-MM-DD hh24:mi:ss'),'aaaaaaaaaaaaaa');

commit;

--檢視捕獲資訊

Select Timestamp, Userhost, Os_User, Db_User, Object_Schema, Object_Name, Statement_Type, Sql_Text, Policy_Name

From Dba_Fga_Audit_Trail

Order By Timestamp;

1.3. 刪除審計

Begin

Dbms_Fga.drop_policy(Object_Schema => 'SCOTT',

Object_Name => 'T1',

Policy_Name => 'T1_audit_inset');

End;

/