FGAC Policy Causes Ora-00903 When Using A Function With UNION Operator And PK On Function Tables (Doc ID 278577.1)

Last updated on SEPTEMBER 03, 2010

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 and later   [Release: 10.1 and later ]
Information in this document applies to any platform.
Checked for relevance on 02-Sep-2010

Symptoms

A select from a table with a RLS policy applied with a function that selects from 2 other tables with UNION operator, each table owning each a PK, fails with ORA-00903 error :

Code:
SQL> create table parent(
  2  id   number(7),
  3  area_code number(7))
  4  /

Table created.

SQL> insert into parent values(1,1);
1 row created.

SQL> insert into parent values(2,2);
1 row created.

SQL> create table child(
  2  p_id number(7),
  3  seq  number(7));

Table created.

SQL> insert into child values(1,1);
1 row created.

SQL> insert into child values(1,2);
1 row created.

SQL> insert into child values(2,1);
1 row created.

SQL> insert into child values(2,2);
1 row created.

SQL> create table privs(
  2  p_id         number(7),
  3  area_code number(7));

Table created.

SQL> CREATE OR REPLACE FUNCTION child_SEL (D1 VARCHAR2, D2 VARCHAR2) RETURN VARS
  2  BEGIN
  3    RETURN 'p_id in (SELECT id FROM parent where area_code=1 UNION SELECT p_;
  4  END child_SEL;
  5  /

Function created.

SQL> prompt select the 4 rows from the child table before adding the policy
select the 4 rows from the child table before adding the policy

SQL> select *
  2  from child;

      P_ID        SEQ
---------- ----------
         1          1
         1          2
         2          1
         2          2

SQL> prompt add the policy

SQL> execute DBMS_RLS.ADD_POLICY ('SCOTT','child','child_SEL','SCOTT','child_SE)
PL/SQL procedure successfully completed.

SQL> prompt now the select only returns 2 rows

SQL> select *
  2  from child
  3  /

      P_ID        SEQ
---------- ----------
         1          1
         1          2


SQL> prompt add a PK to each table

SQL> ALTER TABLE parent ADD CONSTRAINT parent_PK PRIMARY KEY (ID);
Table altered.

SQL> ALTER TABLE PRIVS  ADD CONSTRAINT PRIVS_PK  PRIMARY KEY (p_id,AREA_CODE);
Table altered.

SQL> prompt run the query again and it generates an error
run the query again and it generates an error
SQL> select *
  2  from child;

from child
      *
ERROR at line 2:
ORA-00903: invalid table name

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms