Section 5.4. Troubleshooting RLS

5.4. Troubleshooting RLS

RLS is a complex feature that interacts with a variety of elements in the Oracle architecture. You may encounter errors, either as a result of problems in your design or through misuse by users. Fortunately, for most errors, RLS produces a detailed trace file in the directory specified by the database initialization parameter USER_DUMP_DEST. This section describes how you can trace RLS operations and resolve error conditions.

Upgrade Strategy for Oracle Database 10g Policy Types

When upgrading from Oracle9i Database to Oracle Database 10g, I recommend that you do the following:

1. Initially use the default type (dynamic).

2. Once the upgrade is complete, try to re-create the policy as context-sensitive and test the results thoroughly, with all possible scenarios, to eliminate any potential caching issues.

3. Finally, for those policies that can be made static, convert them to static and test thoroughly.

5.4.1. Interpreting Errors

The most common error you will encounter, and the easiest to deal with, is ORA-28110: Policy function or package has error. The culprit here is a policy function with one or more compilation errors. Fixing your compilation errors and recompiling the function (or the package containing the function) solves the problem.

You may also encounter runtime errors, such as an unhandled exception, a datatype mismatch, or a situation in which the fetched data is much larger than the variable fetched into. In these cases, Oracle raises the ORA-28112: failed to execute policy function error and produces a trace file. You can examine that file, which you will find in the directory specified by the USER_DUMP_DEST database initialization parameter, to find out the nature of the error. Here is an excerpt from a trace file.

     Policy function execution error:     Logon user     : MARTIN     Table/View     : HR.EMP     Policy name    : EMP_DEPT_POLICY     Policy function: RLSOWNER.AUTHORIZED_EMPS     ORA-01422: exact fetch returns more than requested number of rows     ORA-06512: at "RLSOWNER.AUTHORIZED_EMPS", line 14     ORA-06512: at line 1 

The trace file shows that Martin was executing the query when this error occurred. Here the policy function simply fetched more than one row. Examining the policy function, you notice that the policy function has a segment as follows.

     SELECT deptno     INTO l_deptno     FROM hr.emp     WHERE ename = USER; 

It seems there is more than one employee with the name Martinhence, the number of rows fetched is more than one, causing this problem. The solution is to either handle the error via an exception or just use something else as a predicate to get the department number.

Another error, ORA-28113: policy predicate has error, occurs when the policy function does not construct the predicate clause correctly. Like the previous error, it produces a trace file. Here is an excerpt from the trace file.

     Error information for ORA-28113:     Logon user     : MARTIN     Table/View     : HR.EMP     Policy name    : EMP_DEPT_POLICY     Policy function: RLSOWNER.AUTHORIZED_EMPS     RLS predicate  :     DEPTNO = 10,     ORA-00907: missing right parenthesis 

It shows that the predicate returned by the policy function is:

     DEPTNO = 10, 

This string results in a syntactically incorrect SQL query, so the policy application and Martin's query failed. This can be fixed by correcting the policy function logic to return a valid string as the predicate.

5.4.2. Direct-Path Operations

If you are using direct-path operationsfor 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 tablethat is, the user can export only the rows he is authorized to see, not all of them.

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  FROM hr.emp       5  WHERE rownum < 2;          FROM hr.emp             *     ERROR at line 4:     ORA-28113: policy predicate has error 

The error is self-explanatory; I can fix this situation either by temporarily disabling the policy on the table EMP or by exporting through a user who has the EXEMPT ACCESS POLICY system privilege .

5.4.3. Checking the Query Rewrite

During debugging, it may be necessary to see the exact SQL statement rewritten by Oracle when an RLS policy is applied. In this way, you will leave nothing to chance or interpretation. You can see the rewritten statement either via a data dictionary view or by setting an event.

5.4.3.1. Data dictionary view

One option is to use the V$VPD_POLICY dictionary view . VPD in the name stands for Virtual Private Database, another name for row-level security. This view shows all the query transformations made by the RLS policy.

     SQL> SELECT sql_text, predicate, policy, object_name       2  FROM v$sqlarea , v$vpd_policy       3  WHERE hash_value = sql_hash       4  /     SQL_TEXT                      PREDICATE     ----------------------------- --------------------------------     POLICY                         OBJECT_NAME     ----------------------------- ------------------------------     select count(*) from hr.emp    DEPTNO = 10     EMP_DEPT_POLICY                EMP 

The column SQL_TEXT shows the exact SQL statement issued by the user, while the column PREDICATE shows the predicate generated by the policy function and applied to the query. Using this view, you can identify the statements issued by the users and the predicates applied to them.

5.4.3.2. Event-based tracing

The other option is to set an event in the session and examine the trace file . When Martin issues the query, he specifies an additional command to set the event before issuing the query.

     SQL> ALTER SESSION SET EVENTS         '10730 trace name context forever, level 12';     Session altered.     SQL> SELECT COUNT(*) FROM hr.emp; 

After the query finishes, you will see a trace file generated in the directory specified by the database initialization parameter USER_DUMP_DEST. Here is what the trace file shows.

     Logon user     : MARTIN     Table/View     : HR.EMP     Policy name    : EMP_DEPT_POLICY     Policy function: RLSOWNER.AUTHORIZED_EMPS     RLS view :     SELECT  "EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO" FROM "HR"."EMP"     "EMP" WHERE (DEPTNO = 10) 

Using either of these methods you will be able to see the exact way that the user queries are rewritten.