Home arrow Oracle arrow Page 2 - Row-Level Security with Virtual Private Database

RLS In-Depth - Oracle

Oracle's Virtual Private Database technology provides a logical and elegant method for applying security to the data within database tables. This article explains how VPD can be used to provide row-level security. It was excerpted from chapter 11 of Effective Oracle Database 10g Security by Design, written by David C. Knox (McGraw-Hill/Osborne, 2004; ISBN: 0072231300).

  1. Row-Level Security with Virtual Private Database
  2. RLS In-Depth
  3. Creating the Policy Function
  4. The RLS Layer of Security
  5. Debugging RLS Policies
  6. Invalid SQL
  7. Null Application Context Values and Recursive Lookups
  8. VPD Performance
  9. SHARED_STATIC Caching
  10. Caching Caution
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 33
October 20, 2005

print this article



The preceding example can help you get started, but there is much more to RLS than this. The security policies can be much more complex, and the policy functions typically return dramatically different strings based on user authorizations.

There are some similarities between RLS and some of the view examples you read about in Chapter 10. Row-level security is enforced by a PL/SQL function, and the role of the PL/SQL function is the same as it was when used in the view examples. The difference between RLS and views is how the PL/SQL is invoked.

The policy functionís job is to return a string (varchar2) that will serve as a predicate or a where clause to the original query. In effect, the original query is modified, the predicate string is attached, and the query is executed. For example, a simple query select * from EMP might be augmented by an RLS policy function that returns the predicate ename = USER. The effective SQL that is then executed will be select * from EMP where ename = USER. Youíll see a diagram of this process later in Figure 11-1.

The PL/SQL functions are registered to tables, views, or synonyms by invoking the DBMS_ RLS.ADD_POLICY procedure. The DBMS_RLS package is not granted to everyone; administrators will require direct execute privileges on the package. The ADD_POLICY procedure requires, at minimum, the name of the object to which the policy will be applied, a name for the policy, and the name of a PL/SQL function that will implement the security policy.

The policies can be applied to SELECT, INSERT, UPDATE, DELETE, and INDEX statements. The index affects CREATE INDEX and ALTER INDEX DDL commands. Whenever a user directly or indirectly accesses a protected table, view, or synonym, the RLS engine is transparently invoked, the PL/SQL function registered will execute, and the SQL statement will be modified and executed.


RLS is very flexible and very granular. By default, the policy applies to all DML statements. The ADD_POLICY procedure accepts a STATEMENT_TYPES parameter that allows the administrator to specify which DML operations the policy is to apply. This granularity also allows the database to apply separate policies based on the DML type. For example, the database can easily support a policy to allow all records for SELECT statements; an INSERT, UPDATE policy to restrict records to a userís department on insert and update operations; and a DELETE policy that restricts DELETE operations to only the userís record.

Multiple policies also can be applied to the same object: the database logically ANDs the policies together. That is, if there is one policy that returns ename = USER and another policy (on the same object for the same DML) that returns sal > 2000, the database will automatically add both policies, effectively generating where ename = USER and sal > 2000.

The security from VPD derives from the fact that the predicates are used to restrict records returned by the original query, regardless of how the query was issued or who issued the query. This record filtering provides consistent row-level security that is guaranteed to work irrespective of the applications interacting with the data. The entire process is transparent to the application originally issuing the query. One of the strongest arguments for VPD is that the security is tightly fixed to the data it protectsóitís consistent, centrally managed, and it canít be bypassed.

To understand why this is desirable, look at an alternative security model in which the application implements the record filtering. A particular challenge arises when the same data is required by multiple applications. In this case, the security about the data has to be replicated to all the applications. Varying programming languages, COTS applications, and design models often make this an arduous job at best.

The databaseís ability to support security for the data at both an object level and within the object (intra-object) are crucial for ensuring consistent and constant security. Programming languages are born and die within a few years. Applications change in functionality, design, and use even more frequently than that. A well-defined database schema will invariably outlive both. Therefore, a proper security model at the database is paramount to ensuring overall data security. By using features like VPD, the database implements the security policies and thus any application using the data will have the security policies automatically applied.


You now have the background to understand why VPD is a good tool, now you will see more examples of how to use it. In the next example, youíll solve the challenge posed in the ďViewing ProblemsĒ section in Chapter 10: a user is allowed to see all records; to insert and update records only within their department; and to delete only their individual record. You could build this with three views, but your application code would have to switch between views based on the type of operation it wishes to perform, and you would have to write Instead-of triggers. VPD will allow you to enforce two policies. Thus, any currently written SQL doesnít have to be altered as the security policy is created, altered, or deleted.

Setting the Application Context

For this example, the RLS policy will be applied to the PEOPLE table. To enforce the security just described, two policies will be needed: the first will manage the insert and update operations restricting records to the userís department; the second will manage delete operations to ensure the user can only delete their own records.

Restricting insert and updates to the userís department requires knowing what department the user belongs to. To make this as efficient as possible, the userís department number will be stored in an application context. While this is being done for illustrative purposes, application contexts are not required to implement RLS. A lookup table is created to support the population of the context values. The application context setup will involve three steps: creating an application context for the department number, building the namespace manager program to populate the context with the appropriate value, and invoking the namespace manager automatically when the user logs on.

The following code sample for setting up the application context is similar to an example given in Chapter 9. More details about why the code is written as it is are given in that chapter. The code is shown here again as a convenience.

The security manager will set the context values. As such, select privileges are required on the PEOPLE table. The security manager then creates the lookup table.

scott@KNOX10g> -- Recreate the people table to include all rows from EMP
scott@KNOX10g> DROP TABLE people;
Table dropped.
scott@KNOX10g> CREATE TABLE people
  2  AS SELECT ename username, job, sal salary, deptno
  3    FROM emp;
Table created.
scott@KNOX10g> GRANT SELECT ON people TO sec_mgr;
Grant succeeded.
scott@KNOX10g> CONN sec_mgr/oracle10g Connected.
sec_mgr@KNOX10g> -- Create table to populate application context values
sec_mgr@KNOX10g> CREATE TABLE lookup_dept
2  AS SELECT username, deptno FROM scott.people;
Table created.

The namespace manager program will set the context based on the userís department number as stored in the LOOKUP_DEPT table:

sec_mgr@KNOX10g> -- Create namespace for application context
sec_mgr@KNOX10g> CREATE CONTEXT people_ctx USING sec_mgr.people_ctx_mgr;
Context created.
sec_mgr@KNOX10g> -- Create namespace manager program for modifying context.
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE people_ctx_mgr
  2  AS
  3    PROCEDURE set_deptno;
  4    PROCEDURE clear_deptno;
  5  END;
  6  /
Package created.
sec_mgr@KNOX10g> CREATE OR REPLACE PACKAGE BODY people_ctx_mgr
  2  AS
  3  ---------------------------------------

PROCEDURE set_deptno
  5  AS
l_deptno NUMBER;
SELECT deptno
INTO l_deptno
FROM lookup_dept
WHERE username =
SYS_CONTEXT ('userenv',
 13                        'session_user'); 
 14    DBMS_SESSION.set_context
15          (namespace    => 'people_ctx',
 16          ATTRIBUTE     => 'deptno',
 17          VALUE         => l_deptno);
 18  END set_deptno;
19 ---------------------------
 20  PROCEDURE clear_deptno
 21  AS
 22  BEGIN
 23    DBMS_SESSION.clear_context
 24           (namespace   => 'people_ctx',
 25           ATTRIBUTE    => 'deptno');
 26  END clear_deptno;
 27 ----------------------------
 28 END people_ctx_mgr;
 29 /
Package body created.
sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager

To populate the context value automatically, a logon trigger will be used:

sec_mgr@KNOX10g> -- do NOT have to grant execute on namespace manager
sec_mgr@KNOX10g> CREATE OR REPLACE TRIGGER set_user_deptno
  3  BEGIN
  4    sec_mgr.people_ctx_mgr.set_deptno;
  7    THEN
  8      -- If user is not in table,
  9      -- a no_data_found is raised
10      -- If exception is not handled,
    then users not in table
 11      -- will be unable to log on
 12      NULL;
 13  END;
 14  /
Trigger created.

Test the context by logging in as the SCOTT user:

sec_mgr@KNOX10g> CONN scott/tiger
scott@KNOX10g> COL deptno format a8 scott@KNOX10g> SELECT SYS_CONTEXT ('people_ctx',
  2                      'deptno') deptno

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: