Oracle PL/SQL for DBAs

Direct-Path Operations

If you are using direct-path operations—for example, SQL*Loader Direct Path Load; Direct Path Inserts using the APPEND hint (INSERT /*+ APPEND */ INTO ...); or Direct Path Export —you may run into trouble when using RLS. Because these operations bypass the SQL layer, the RLS policy on these tables is not invoked, and hence the security is bypassed. How do you deal with this problem?

In the case of exports, it’s rather easy. Here is what happens when I export the table EMP, which is protected by one or more RLS policies, with the DIRECT=Y option.

    About to export specified tables via Direct Path ...
    EXP-00080: Data in table "EMP" is protected. Using conventional mode.
    EXP-00079: Data in table "EMP" is protected. Conventional path may only be exporting 

    partial table.

The export is successfully done, but as you can see, the output is conventional path, not the direct path I wanted it to be. And in the process of performing the operation, the export still applied the RLS policies to the table—that is, the user can export only the rows he is authorized to see, not all of them.

Warning

Because exporting a table under RLS may still successfully complete, you might get a false impression that all rows have been exported. However, be aware that only the rows the user is allowed to query are exported.

When I try to do a direct path load to the table under RLS, using SQL*Loader or Direct Path Insert , I get an error.

    SQL> INSERT /*+ APPEND */
      2  INTO hr.EMP
      3  SELECT *
      4