CREATE OR REPLACE PACKAGE SYS.dbms_fga AS
-- ------------------------------------------------------------------------
-- CONSTANTS
--
EXTENDED CONSTANT PLS_INTEGER := 1 ;
DB CONSTANT PLS_INTEGER := 2 ;
DB_EXTENDED CONSTANT PLS_INTEGER := 3 ; -- (default)
XML CONSTANT PLS_INTEGER := 4 ;
ALL_COLUMNS CONSTANT BINARY_INTEGER := 1 ;
ANY_COLUMNS CONSTANT BINARY_INTEGER := 0 ; -- (default)
-- add_policy - add a fine grained auditing policy to a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the 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 handler procedure is
-- handler_module - name of the event handler
-- enable - policy is enabled by DEFAULT
-- statement_type - statement type a policy applies to (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 the policy
PROCEDURE add_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_types IN VARCHAR2 := 'SELECT' ,
audit_trail IN PLS_INTEGER := 3 ,
audit_column_opts IN BINARY_INTEGER DEFAULT 0 );
-- drop_policy - drop a fine grained auditing policy from a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be dropped
PROCEDURE drop_policy(object_schema IN VARCHAR2 := NULL ,
object_name IN VARCHAR2 ,
policy_name IN VARCHAR2 );
-- enable_policy - enable a security policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
PROCEDURE enable_policy(object_schema IN VARCHAR2 := NULL ,
object_name IN VARCHAR2 ,
policy_name IN VARCHAR2 ,
enable IN BOOLEAN := TRUE );
-- disable_policy - disable a security policy for a table or view
--
-- INPUT PARAMETERS
-- object_schema - schema owning the table/view, current user if NULL
-- object_name - name of table or view
-- policy_name - name of policy to be enabled or disabled
PROCEDURE disable_policy(object_schema IN VARCHAR2 := NULL ,
object_name IN VARCHAR2 ,
policy_name IN VARCHAR2 );
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;
/