Re: FGA based on a complex join

From: Los <carlos.caulder_at_gmail.com>
Date: 8 Mar 2006 16:13:39 -0800
Message-ID: <1141863219.001203.206580@z34g2000cwc.googlegroups.com>

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 ID:(34.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. Received on Wed Mar 08 2006 - 18:13:39 CST