Section 5.3. RLS in Oracle Database 10g

5.3. RLS in Oracle Database 10g

This section describes the new RLS features and enhancements introduced in Oracle Database 10g.

5.3.1. Column-Sensitive RLS

Let's revisit the example of the HR application used in earlier sections. I designed the policy with the requirement that no user except King should have permission to see all records. Any other user can see only data about the employees in her department. But there may be cases in which that policy is too restrictive.

Suppose that I want to protect the data so people can't snoop around for salary information. Consider the following two queries.

     SELECT empno, sal FROM emp;     SELECT empno FROM emp; 

The first query shows salary information for employees, the very information you want to protect. In this case, I want to show only the employees in the user's own department. But the second query shows only the employee numbers. Should I filter that as well so that it shows only the numbers for the employees in the user's own department?

The answer might vary depending upon the security policy in place within my organization. There may be a good reason to let the second query show all employees, regardless of the department to which they belong.

In Oracle9i Database, RLS would not have been able to help us with this requirement, but in Oracle Database 10g, a new ADD_POLICY parameter, sec_relevant_cols, makes it easy. In the above scenario, I want the filter to be applied only when the SAL and COMM columns are selected, not any other columns. I can write the policy as follows. Note the new parameter shown in bold.

     BEGIN        DBMS_RLS.drop_policy (object_schema      => 'HR',                              object_name        => 'EMP',                              policy_name        => 'EMP_POLICY'                             );        --        DBMS_RLS.add_policy (object_schema          => 'HR',                 object_name            => 'EMP',                 policy_name            => 'EMP_POLICY',                 function_schema        => 'RLSOWNER',                 policy_function        => 'AUTHORIZED_EMPS',                 statement_types        => 'INSERT, UPDATE, DELETE, SELECT',                 update_check           => TRUE,                                  sec_relevant_cols      => 'SAL, COMM'                 );     END; 

After this policy is put in place, Martin's queries perform differently.

     SQL> -- "harmless" query, only EMPNO is selected      SQL> SELECT empno FROM hr.emp;      ... data displayed ...      14 rows selected.     SQL> -- sensitive query: SAL is present      SQL> SELECT sal FROM hr.emp;      ... data displayed ...      6 rows selected. 

Note that when the column SAL is selected, the RLS policy kicks in, preventing the display of all rows; it filters out the rows where DEPTNO is something other than 30that is, the DEPTNO of the user (Martin) executing the query.

Column sensitivity does not apply just to being in the SELECT list, but applies whenever the column is referenced, either directly or indirectly. Consider the following query:

     SQL> SELECT   deptno, COUNT (*)       2      FROM hr.emp       3     WHERE sal > 0       4  GROUP BY deptno;         DEPTNO   COUNT(*)     ---------- ----------             30          6 

Here, the SAL column has been referenced in the WHERE clause, so the RLS policy applies, causing only the records from department 30 to be displayed. Consider another example, in which I try to display the value of SAL.

     SQL> SELECT *       2    FROM hr.emp       3   WHERE deptno = 10;     no rows selected 

Here the column SAL has not been referenced explicitly, but it is implicitly referenced by the "SELECT *" clause, so the RLS policy filters all but the rows from department 30. Because the query called for department 10, no rows were returned.

Let's examine a slightly different situation now. In the above case, we did protect the SAL column values from being displayed for those rows for which the user is not authorized. However, in the process, I suppressed the display of the entire row, not just the column. Suppose that the new requirements call for masking only the column, not the entire row, and for displaying all other non-sensitive columns. Can this be done?

It's easy with another ADD_POLICY parameter, sec_relevant_cols_opt. All I have to do is re-create the policy with the parameter set to the constant DBMS_RLS.ALL_ROWS, as follows.

     BEGIN        DBMS_RLS.drop_policy (object_schema      => 'HR',                              object_name        => 'EMP',                              policy_name        => 'EMP_POLICY'                             );        DBMS_RLS.add_policy (object_schema              => 'HR',                             object_name                => 'EMP',                             policy_name                => 'EMP_POLICY',                             function_schema            => 'RLSOWNER',                             policy_function            => 'AUTHORIZED_EMPS',                             statement_types            => 'SELECT',                             update_check               => TRUE,                             sec_relevant_cols          => 'SAL, COMM',                             sec_relevant_cols_opt      => DBMS_RLS.all_rows                            );     END; 

If Martin issues the same type of query now, the results will be different (in the following output, I request that a "?" be shown for NULL values):

     SQL> SET NULL ?     SQL> SELECT * FROM hr.emp ORDER by deptno;     EMPNO ENAME      JOB          MGR HIREDATE     SAL   COMM DEPTNO     ------ ---------- --------- ------ --------- ------ ------ ------       7782 CLARK      MANAGER     7839 09-JUN-81 ?      ?          10       7839 KING       PRESIDENT ?      17-NOV-81 ?      ?          10       7934 MILLER     CLERK       7782 23-JAN-82 ?      ?          10       7369 SMITH      CLERK       7902 17-DEC-80 ?      ?          20       7876 ADAMS      CLERK       7788 12-JAN-83 ?      ?          20       7902 FORD       ANALYST     7566 03-DEC-81 ?      ?          20       7788 SCOTT      ANALYST     7566 09-DEC-82 ?      ?          20       7566 JONES      MANAGER     7839 02-APR-81 ?      ?          20       7499 ALLEN      SALESMAN    7698 20-FEB-81  1,600    300     30       7698 BLAKE      MANAGER     7839 01-MAY-81  2,850 ?          30       7654 MARTIN     SALESMAN    7698 28-SEP-81  1,250  1,400     30       7900 JAMES      CLERK       7698 03-DEC-81    950 ?          30       7844 TURNER     SALESMAN    7698 08-SEP-81  1,500      0     30       7521 WARD       SALESMAN    7698 22-FEB-81  1,250    500     30     14 rows selected. 

Notice how all 14 rows are shown, along with all the columns, but the values for SAL and COMM are NULL for the rows that the user is not supposed to seethat is, the employees of departments other than 30.

With these new ADD_POLICY parameters, RLS lets me meet requirements in which rows must be displayed but sensitive values must be hidden. Prior to Oracle Database 10g, I would have had to use views to accomplish the same thing, and the operations would have been a good deal more complicated.

In Oracle Database 10g Release 2, you can even apply RLS to CREATE INDEX statements. In that case, use INDEX as the value in the statement_types parameter in the ADD_POLICY procedure.

Use this feature with extreme caution: in certain cases, it may produce unexpected results. Consider the following query issued by user Martin.

     SQL> SELECT COUNT(1), AVG(sal) FROM hr.emp;     COUNT(SAL)   AVG(SAL)     ---------- ----------              14 1566.66667 

The result shows 14 employees, and the average salary is 1,566, but that salary is actually the average of only the 6 employees Martin is authorized to see, not all 14 employees. This may create some confusion as to which values are correct. When the schema owner, HR, issues the same query, we see a different result.

     SQL> CONN hr/hr     Connected.     SQL> SELECT COUNT(1), AVG(sal) FROM hr.emp;     COUNT(SAL)   AVG(SAL)     ---------- ----------             14 2073.21429 

Because results vary by the user issuing the query, you need to be very careful to interpret the results accordingly; otherwise, this feature may introduce difficult-to-trace bugs into your application.

5.3.2. Other Classes of Dynamism

Perhaps the most important enhancement to RLS in Oracle Database 10g is the support of new levels of dynamism, implemented as policy types and intended to improve performance.

First, let's review the difference between static and dynamic policies . With a dynamic policy type, the policy function is executed to create a predicate string every time the policy places filters on access to the table. Although using a dynamic policy guarantees a fresh predicate every time it is called, the additional overhead resulting from multiple executions of the policy function can be quite substantial. The fact is that, in most cases, the policy function does not need to be re-executed, because the predicate will never change inside a session, as we showed earlier in the discussion of static policies .

The best approach, from a performance point of view, would be to design the policy function so that if some specific value changes, the policy function will be re-executed. Oracle Database 10g offers such a feature: if an application context on which the program depends is changed, the policy forces re-execution of the function; otherwise, the function will not be run again. We'll see how this works in the following sections.

As with Oracle9i Database, in Oracle Database 10g, you can set the static_policy parameter in the ADD_POLICY procedure to TRUE (indicating a static policy) or FALSE (indicating a dynamic policy). If this parameter is TRUE, then the value of a new Oracle Database 10g parameter, policy_type, is set to DBMS_RLS.STATIC. If static_policy is FALSE, then policy_type is set to DBMS_RLS. DYNAMIC. The default for static_policy is TRUE.

The static and dynamic policy choices behave just as they would in Oracle9i Database, but Oracle Database 10g supports some additional policy types, as well. You select these by specifying the appropriate value for the policy_type parameter in the ADD_POLICY procedure. The list below shows the new values you can specify for this parameter.

Context sensitive

DBMS_RLS.CONTEXT_SENSITIVE

Shared context sensitive

DBMS_RLS.SHARED_CONTEXT_SENSITIVE

Shared static

DBMS_RLS.SHARED_STATIC

These new policy types provide excellent performance benefits but share some of the same side effects described earlier for static policies.

5.3.2.1. Shared static policy

The shared static policy type is similar to the static type, except the same policy function is used in policies on multiple objects. In a previous example, you saw how the function authorized_emps was used as the policy function in the policies on both the DEPT and the EMP tables. Similarly, you can have the same policy defined on both tables, not merely the same function. This is known as a shared policy. If it can also be considered static, then the policy is known as a shared static policy , and the policy_type parameter is set to the constant DBMS_RLS.SHARED_STATIC. Using this policy type, here is how I can create the same policy on our two tables.

     BEGIN        DBMS_RLS.drop_policy (object_schema      => 'HR',                              object_name        => 'DEPT',                              policy_name        => 'EMP_DEPT_POLICY'                             );        DBMS_RLS.add_policy (object_schema  => 'HR',               object_name          => 'DEPT',               policy_name          => 'EMP_DEPT_POLICY',               function_schema      => 'RLSOWNER',               policy_function      => 'AUTHORIZED_EMPS',               statement_types      => 'SELECT, INSERT, UPDATE, DELETE',               update_check         => TRUE,               policy_type          => DBMS_RLS.shared_static               );        DBMS_RLS.add_policy (object_schema => 'HR',               object_name          => 'EMP',               policy_name          => 'EMP_DEPT_POLICY',               function_schema      => 'RLSOWNER',               policy_function      => 'AUTHORIZED_EMPS',               statement_types      => 'SELECT, INSERT, UPDATE, DELETE',               update_check         => TRUE,               policy_type          => DBMS_RLS.shared_static               );     END; 

By declaring a single policy on both tables, I am effectively instructing the database to cache the outcome of the policy function once and then use it multiple times.

5.3.2.2. Context-sensitive policy

As you saw earlier, static policies, although quite efficient, can be dangerous: because they do not re-execute the function every time, they may produce unexpected and unwanted results. Hence, Oracle provides another type of policythe context-sensitive policy, which re-executes the policy function only when the application context changes in the session. (See the "Application Contexts" section later in this chapter.) Here is a block of code that defines such a policy:

     BEGIN        DBMS_RLS.drop_policy (object_schema      => 'HR',                              object_name        => 'EMP',                              policy_name        => 'EMP_DEPT_POLICY'                             );        DBMS_RLS.add_policy (object_schema => 'HR',               object_name          => 'EMP',               policy_name          => 'EMP_DEPT_POLICY',               function_schema      => 'RLSOWNER',               policy_function      => 'AUTHORIZED_EMPS',               statement_types      => 'SELECT, INSERT, UPDATE, DELETE',               update_check         => TRUE,               policy_type          => DBMS_RLS.context_sensitive             );     END; 

When you use a context-sensitive policy type (DBMS_RLS.CONTEXT_SENSITIVE), performance can increase dramatically. In the following block of code, the built-in function, DBMS_UTILITY.GET_TIME, helps calculate elapsed time down to the hundredth of a second.

     DECLARE        l_start PLS_INTEGER;        l_count PLS_INTEGER;     BEGIN        l_start := DBMS_UTILITY.get_time;        SELECT COUNT (*)          INTO l_count          FROM hr.emp;        DBMS_OUTPUT.put_line (          'Elapsed time = '          || TO_CHAR (DBMS_UTILITY.get_time - l_start)          );     END; 

We then apply each of the types of policies shown in the table below and run the block of code. As you can see from this table, the purely static policy results in the fastest time (just a single execution of the policy function), but the context-sensitive policy is significantly faster than the 100% dynamic version.

Policy type	Response time (cs)
Dynamic	133
Context -sensitive	84
Static	37

5.3.2.3. Shared context-sensitive policy

Shared context-sensitive policies are similar to context-sensitive policies, except that the same policy is used for multiple objects, as we saw with shared static policies.