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 FEBRUARY 23, 2023

Applies to:

Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
 


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

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.