My Oracle Support Banner

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

Last updated on MARCH 05, 2019

Applies to:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
Checked for relevance on 02-Sep-2010


Symptoms

NOTE: In the images and/or the document content below, the user information and data used represents fictitious data from the Oracle sample schema(s) or Public Documentation delivered with an Oracle database product. Any similarity to actual persons, living or dead, is purely coincidental and not intended in any manner. 

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 ('USER1','child','child_SEL','USER1','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

Changes

 

Cause

To view full details, 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 a vibrant support community of peers and Oracle experts.