Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from 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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page