ORACLE 10.2.0.5中DBMS_FGA报错

kylefree

报错环境为ORACLE 10.2.0.5   64位  on AIX5.3上
为了审计人为DML操作，想利用DBMS_FGA来实现，利用audit_condition可以用自定义函数功能，来判断连接是由程序连接还是人工工具连接，如果是人工工具连接，则进行DML审计。这个方式在oracle11.2.0.3中成功，但是在10.2.0.5中失败，请求指点具体错误原因。
编写的判断函数如下：
CREATE OR REPLACE FUNCTION sys.auditwhat
RETURN number
IS
v_count number;
BEGIN
select count(1) into v_count from dual where upper(sys_context('USERENV','MODULE')) like '%PL%SQL%' or upper(sys_context('USERENV','MODULE')) like '%TOAD%' or upper(sys_context('USERENV','MODULE')) like '%SQL%PLUS%';
if (v_count>0) then
RETURN 1;
else
RETURN 0;
END IF;
END;
/

该函数经过放在存储过程中验证，是可以正确执行，判断函数本身不应该有问题。
set serverout on
declare
v_b int;
begin
v_b :=sys.auditwhat;
if v_b = 1 then
dbms_output.put_line('true');
else
dbms_output.put_line('false');
end if;
end;
/

添加FGA策略
begin
  DBMS_FGA.ADD_POLICY(object_schema     => 'monitor',
                      object_name       => 'test',
                      policy_name       => 'monpolicy1',
                      audit_condition   => 'auditwhat=1',
                      audit_column      => null,
                      handler_schema    => NULL,
                      handler_module    => NULL,
                      enable            => TRUE,
                      statement_types   => 'SELECT,INSERT,UPDATE,DELETE',
                      audit_trail       => DBMS_FGA.DB + DBMS_FGA.EXTENDED,
                      audit_column_opts => DBMS_FGA.ANY_COLUMNS);
end ;
/

以上执行内容无报错，对象状态也正常。
然后切换到monitor用户执行select、delete都报错，过程如下：
SQL> show user
USER is "MONITOR"
SQL> alter session set tracefile_identifier=ck;
alter session set events '10046 trace name context forever, level 12';
Session altered.

SQL>

Session altered.

SQL>
SQL> select count(*) from test;
select count(*) from test
                        *
ERROR at line 1:
ORA-28112: failed to execute policy function


SQL> delete test;
delete test
*
ERROR at line 1:
ORA-28138: Error in Policy Predicate


SQL> alter session set events '10046 trace name context off';

然后找到了(oracle10G失败)riskdb_ora_6586410_CK.trc
出现FGA: Query parsing returned with error# 904错误
同时也取了ORACLE11G中成功执行的(oracle11G成功)ywwhdb_ora_13454_CK.trc

trc内容均上传再附件中。求指点具体什么原因，在oracle10g中，这个方式就报错。 (oracle10G失败)riskdb_ora_6586410_CK.rar (1.22 KB, 下载次数: 1049)

2014-3-28 14:09 上传

点击文件名下载附件

(oracle11G成功)ywwhdb_ora_13454_CK.rar (2.63 KB, 下载次数: 1047)

2014-3-28 14:09 上传

点击文件名下载附件

Liu Maclean(刘相兵

ODM FINDING FYI

Hdr: 8967157 10.2.0.4 RDBMS 10.2.0.4 SECURITY PRODID-5 PORTID-23
Abstract: FGA DOES NOT WORK WELL WITH FUNCTION USING USERENV.

AUDIT_CONDITION of the policy is set
@ depending on the return value from the function using USERENV.
@ .
@ The function returns NULL for particular user judged by USERENV,
@ and the audit behavior depends on the result of this function.
@ .
@ However, the result of USERENV through FGA policy seems to be
@ dependent on user who create the function not who execute the function.
@ .
@ So the behavior of FGA is unexpected.
@ .
@ According to manual, USERENV can not be used directly for AUDIT_CONDITION.
@ But in this case, USERENV is used through user defined function
@ and I feel this is not out of usage.
@ .
@ Oracle Database Security Guide 10g Release 2 (10.2)
@ B14266-04 ("Usage Notes" sction of ADD_POLICY)
@ http://st-doc.us.oracle.com/10/1 ... gaudit.htm#i1011783
@ -----
@ Any direct use of SYSDATE, UID, USER or USERENV functions.
@ However, a user-defined function and other SQL functions can use
@ these functions to return the desired information.
@ -----
@ .
@ WORKAROUND:
@ -----------
@ N/A
@ .
@ RELATED BUGS:
@ -------------
@ BUG 8925046 - AUDIT RECORD IS NOT BE OUTPUT WITH FGA AND VPD.
@ .
@ REPRODUCIBILITY:
@ ----------------
@ Version            OS                 Reproducibility
@ -----------------  -----------------  -----------------
@ 10.2.0.4           Solaris X86        Y
@ 11.1.0.7           Solaris X86        Y
@ 11.2.0.1           Linux x86-64       Y
@ -----------------  -----------------  -----------------

kylefree

问题解决了，谢谢刘大，谢谢各位。
在10g里面，audit_condition里要把function的schema加上，并且grant execute on sys.auditwhat to public， 而11g里面这些都不用= =
似乎10g和11g里fga触发后执行的机制有些不一样。