Oracle PL/SQL for DBAs

RLS Interactions with Other Oracle Features

RLS, like any other powerful feature, presents its share of potential concerns, issues, and complexities. This section describes the interactions between RLS and several other Oracle features.

Referential integrity constraints

If a table under RLS has a referential integrity constraint pointing to a parent table that is also under RLS, then the way Oracle deals with errors can present a security concern. Suppose that the table DEPT has an RLS policy defined on it that lets a user see only her department’s information. Then an “all rows” query against DEPT reveals just a single row:

    SQL> CONN martin/martin
    Connected.

    SQL> SELECT * FROM hr.dept;

        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK

The EMP table, however, is not under any RLS policy, so the user can freely select from it. A user can, therefore, be made aware that there is more than one department.

    SQL> SELECT DISTINCT deptno FROM hr.emp;

        DEPTNO
    ----------
            10
            20
            30

Table EMP has a referential integrity constraint on the column DEPTNO that references the DEPTNO column in table DEPT.

The user can see only the details of department 10, the one to which he belongs, but he knows that there are others. Now suppose he tries to update the EMP table, and set the department number to 50.

    SQL> UPDATE hr.emp
      2  SET deptno = 50
      3  WHERE empno = 7369; update hr.emp * ERROR at line 1: ORA-02291: integrity constraint (HR.FK_EMP_DEPT) violated - parent key not found ...