Section 5.5. RLS Interactions with Other Oracle Features

5.5. 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 

The error indicates that the integrity constraint is violated; this makes sense because the DEPT table does not have a row with DEPTNO equal to 50. The Oracle database is doing its job, but now the user knows more about the DEPT table than was intended by the security policy.

Revealing the absence of data can, under some circumstances, be as severe a security breach as showing the data that is in the table.

Replication

In multi-master replication , the receiver and propagator schemas have to be able to select data from tables in an unrestricted manner. Hence, you will need to either modify the policy function to return a NULL predicate for these users or grant the EXEMPT ACCESS POLICY system privilege to them.

Materialized views

When defining materialized views, you should be careful to make sure that the schema owner of the materialized view has unrestricted access to the underlying tables. Otherwise, only the rows satisfied by the predicate will be returned to the query defining the materialized view, and that will be incorrect. As in the case of replication, you can either modify the policy function to return a NULL predicate or grant the EXEMPT ACCESS POLICY system privilege to the schemas.