ORACLE 10.2.0.5中DBMS_FGA报错
报错环境为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中，这个方式就报错。
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/102/network.102/b14266/cfgaudit.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
@ ----------------- ----------------- ----------------- 问题解决了，谢谢刘大，谢谢各位。
在10g里面，audit_condition里要把function的schema加上，并且grant execute on sys.auditwhat to public， 而11g里面这些都不用= =
似乎10g和11g里fga触发后执行的机制有些不一样。
页:
[1]