使用DBMS_FGA包捕获dml语句

使用DBMS_FGA包捕获dml语句
一、原有捕获触发器的问题
在Oracle9i中，使用trigger来对某表DML操作的语句捕获，如下：
CMN_LOG脚本：
create table CMN_LOG
(
SERIAL_ID NUMBER(10) not null,
PROGRAM_ID VARCHAR2(30) not null,
OP_TYPE VARCHAR2(4) not null,
OP_DATE DATE not null,
OP_LOG VARCHAR2(2000) not null,
OP_DETAIL VARCHAR2(2000) not null,
OP_USER VARCHAR2(10) not null
)

插入数据为：
insert into CMN_LOG (SERIAL_ID, PROGRAM_ID, OP_TYPE, OP_DATE, OP_LOG, OP_DETAIL, OP_USER)
values (1096, 'F_ExchangeInit', '3', to_date('07-09-2008 13:25:34', 'dd-mm-yyyy hh24:mi:ss'),
'日初信息：日初日期：2008-09-08资产帐户代码是：271,交易市场代码是：OTC日初结果：', '--', '2');

catch_sql脚本：
create table catch_sql(
USERNAME VARCHAR2(30),
CLIENT_IP VARCHAR2(20),
SQL_TEXT VARCHAR2(4000),
TABLE_NAME VARCHAR2(30),
OWNER VARCHAR2(30)
)

Trigger脚本：
create or replace trigger CATCH_SQL
BEFORE DELETE OR INSERT OR UPDATE ON CMN_LOG
declare
n number;
stmt varchar2(4000);
sql_text ora_name_list_t;
begin
n := ora_sql_txt(sql_text);
FOR i IN 1..n LOOP
stmt := stmt || sql_text(i);
END LOOP;

insert into catch_sql(USERNAME,CLIENT_IP,SQL_TEXT,TABLE_NAME,OWNER)
values(user,sys_context('userenv','ip_address'),stmt,'T1','RAINY');

end;
/

插入语句后报错ORA-06502:PL/SQL: numeric or value error

相关资料显示：
从9206及以上版本，Oracle不再允许DML触发器中使用这个函数，只有在系统事件触发器中才能获取到SQL语句。
试图在DML触发器中使用ORA_SQL_TXT函数，这个函数返回NULL，而且传入的OUT变量将不会被初始化。

二、10G另有一个替换方法
DBMS_FGA包使用介绍
从Oracle9i开始，就可以使用DBMS_FGA对指定的表的SELECT语句进行审计，但是在9i中只能对select语句进行审计，在10g中可以实现对DML的审计功能。简单得看看这个包的使用方法：
首先按照惯例列出Oracle自带说明：
******************************************************************************
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”四种，如需多种以','分开

三、开始应用
1、设置捕获方法
begin
dbms_fga.add_policy(object_schema => 'cc', --schema名(默认当前操作用户)
object_name => 't1', --被操作object对象
policy_name => 't1_audit', --policy名(唯一)
audit_condition => NULL,
audit_column => 'f1', --监视的字段(默认为全部)
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(object_schema => 'cc', --schema名(默认当前操作用户)
object_name => 't1', --被操作object对象
policy_name => 't1_audit', --policy名(唯一)
statement_types => 'insert,update,delete', --受影响的操作
);
end;
/

2、查看捕获策略
select * from dba_audit_policies;

3、查看捕获信息
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;

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15357/viewspace-1024346/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15357/viewspace-1024346/