CREATEOR REPLACE PACKAGE SYS.dbms_fgaAS
--------------------------------------------------------------------------
-- CONSTANTS
--
EXTENDED CONSTANT PLS_INTEGER := 1 ;
DB CONSTANT PLS_INTEGER := 2 ;
DB_EXTENDED CONSTANTPLS_INTEGER := 3; -- (default)
XML CONSTANT PLS_INTEGER := 4 ;
ALL_COLUMNS CONSTANTBINARY_INTEGER := 1 ;
ANY_COLUMNS CONSTANTBINARY_INTEGER := 0; -- (default)
-- add_policy- add a fine grained auditing policy to a tableor view
--
-- INPUT PARAMETERS
-- object_schema - schema owningthe table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be added
-- audit_column - column to be audited
-- audit_condition -predicates for this policy
-- handler_schema - schema where the event handlerprocedure is
-- handler_module - name of the eventhandler
-- enable - policy is enabled by DEFAULT
-- statement_type - statement type a policy appliesto (default SELECT)
-- audit_trail - Write sqltext and sqlbind into audit trail by default(DB_EXTENDED)
-- audit_column_options -option of using 'Any' or 'All' on audit columns for thepolicy
PROCEDUREadd_policy(object_schema IN VARCHAR2 := NULL ,
object_name IN VARCHAR2 ,
policy_name IN VARCHAR2 ,
audit_condition IN VARCHAR2 := NULL ,
audit_column IN VARCHAR2 := NULL ,
handler_schema IN VARCHAR2 :=NULL ,
handler_module IN VARCHAR2 :=NULL ,
enable IN BOOLEAN := TRUE ,
statement_typesIN VARCHAR2 := 'SELECT' ,
audit_trail IN PLS_INTEGER := 3 ,
audit_column_opts IN BINARY_INTEGER DEFAULT 0);
-- drop_policy - drop a finegrained auditing policy from a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owningthe table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be dropped
PROCEDUREdrop_policy(object_schema IN VARCHAR2 := NULL,
object_name INVARCHAR2 ,
policy_name INVARCHAR2 );
-- enable_policy - enable asecurity policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owningthe table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
PROCEDUREenable_policy(object_schema IN VARCHAR2 :=NULL ,
object_name IN VARCHAR2 ,
policy_name INVARCHAR2 ,
enable IN BOOLEAN := TRUE );
-- disable_policy - disable asecurity policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owningthe table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
PROCEDUREdisable_policy(object_schema IN VARCHAR2 :=NULL ,
object_name INVARCHAR2 ,
policy_name INVARCHAR2 );
END dbms_fga;
基本上每个部分的功能上面就已经说了，简单得介绍一下需要注意的几个地方：
1、在审计策略生效之前，必须对表进行分析，因为只有在CBO模式，DBMS_FGA才能正确的工作
2、指定audit_condition可以设定监控条件(例如select某部分记录)
3、可以指定audit_column来审计专门的字段
4、statement_types包括“SELECT,UPDATE,INSERT.DELETE”四种，如需多种以','分开
举例：
begin
dbms_fga.add_policy(object_schema =>'wangxiaoqi', --schema名(默认当前操作用户)
object_name =>'t_check', --被操作object对象
policy_name => 't_check_audit',--policy名(唯一)
audit_condition => NULL,
audit_column => 'tno,type,modifydate',--监视的字段(默认为全部)
handler_schema => NULL,
handler_module => NULL,
enable => TRUE,
statement_types => 'insert,update,delete',--受影响的操作
audit_trail =>dbms_fga.DB_EXTENDED, --默认值
audit_column_opts =>dbms_fga.ANY_COLUMNS);--默认值
end;
/
当然也可以不用 =>来指定每个参数，只需要列出前几个就可以了，有默认值的参数如果不需要改变默认值，可以不给出。 但是必须要按照定义的顺序来指定。例如：
begin
dbms_fga.add_policy('wangxiaoqi','T1','T1_AUDIT','B>=20','B');
end;
/
