Oracle PL/SQL for DBAs

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