In 9iR2, I am attempting to create a policy with DBMS_FGA that includes
a complex join in the audit_condition. Of all the examples I have seen,
they include only simple where clauses, such as dept = 10, sal > 3000,
etc. Is it possible to have a complex join in the audit_condition? In
other words, the interested rows that Oracle identifies for the audited
table are based on a join to another table. I have been able to create
the policy with such a condition and enable it with no errors, but am
unable to trigger it. I suspect that it may be due to the aliases used
for the columns in the where clause.

Any input would be appreciated.

Los wrote:
> In 9iR2, I am attempting to create a policy with DBMS_FGA that includes
> a complex join in the audit_condition. Of all the examples I have seen,
> they include only simple where clauses, such as dept = 10, sal > 3000,
> etc. Is it possible to have a complex join in the audit_condition? In
> other words, the interested rows that Oracle identifies for the audited
> table are based on a join to another table. I have been able to create
> the policy with such a condition and enable it with no errors, but am
> unable to trigger it. I suspect that it may be due to the aliases used
> for the columns in the where clause.
>
> Any input would be appreciated.

I can't think of any reason why a complex join wouldn't be possible
but I no longer have 9i on any of my servers.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

To demonstrate what I'm attempting, I tried to simulate it with a
simple example.

Using the scott demo schema, I created the following scenario:

1) added a column "home" to the emp table

SQL> desc emp
Name Null? Type
-------------------- ------ -------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
HOME VARCHAR2(50)

2) updated all the employees to have the same home as their dept
location
3) updated two employees to have different home values than their dept
location

When I run the following query, as scott, before any policy is created,
I get:

SQL> select ename,sal,dept.deptno
2 from emp,dept
3 where dept.loc != home
4 and emp.deptno = dept.deptno;

ENAME SAL DEPTNO
---------- ---------- ----------
SMITH 800 20
KING 5000 10


As SYS, I created the following policy on the emp table using
dbms_fga.add_policy:

exec dbms_fga.add_policy( -
> object_schema => 'SCOTT', -
> object_name => 'EMP', -
> policy_name => 'commuter', -
> audit_condition => 'home != dept.loc and deptno = dept.deptno', -
> enable => TRUE)

The policy was created successfully and enabled.

When I run the query again, as scott, I receive the error shown:

select ename,sal,dept.deptno
from emp,dept
where dept.loc != home
and emp.deptno = dept.deptno
SQL> /
and emp.deptno = dept.deptno
*
ERROR at line 4:
ORA-28112: failed to execute policy function

The udump trace file shows:
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++
*** 2006-03-08 18:45:56.995
*** SESSION ID34.5191) 2006-03-08 18:45:56.941
FGA: Query parsing returned with error# 904
SELECT
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM ","DEPTNO","HOME"
FROM "SCOTT"."EMP" "EMP"
WHERE (CASE WHEN (home != dept.loc and deptno = dept.deptno)
THEN SYS_AUDIT( 'SCOTT','EMP','COMMUTER' )
ELSE NULL END) IS NULL
++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++

Of course 904 is "invalid identifier". Is it because of the use of the
table references in the query or policy or lack of a schema reference?
Or neither?

Any help is appreciated.

Los wrote:
>
>
> As SYS, I created the following policy on the emp table using
> dbms_fga.add_policy:
>
> exec dbms_fga.add_policy( -
>> object_schema => 'SCOTT', -
>> object_name => 'EMP', -
>> policy_name => 'commuter', -
>> audit_condition => 'home != dept.loc and deptno = dept.deptno', -
>> enable => TRUE)
From the manual:

The audit_condition must be a boolean expression that can be evaluated
using the values in the row being inserted, updated, or deleted. This
condition can be NULL (or omitted), which is interpreted as TRUE, but it
cannot contain the
following elements:

Subqueries or sequences

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.

Any use of the pseudocolumns LEVEL, PRIOR, or ROWNUM.

You violate the fact that your data should be in the row - it is
not; you also require the dept table.
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

Thanks for the input.

I searched the documentation for "audit_condition" and "DBMS_FGA" to
find the restrictions for the policy definition. I have not seen the
text you have included. Matter of fact, I have not found much about
this feature in 9i. Is that excerpt from the 9iR2 documentation?

I'm not sure we're talking about the same thing. My understanding of
DBMS_FGA is that it will only allow you to audit SELECT operations.

Text was found in the "PL/SQL supplied packages and types" manual.

The default is to audit select, but other operations are possible - see
the manual

I found the text you have posted in the 10g documentation. Apparently,
the options are different in 10g than in 9i. Oracle must have expanded
the use of DBMS_FGA in 10g. I'm guessing what I am trying is not
possible, but it's not specifically stated in the documentation.