This chapter illustrates how VPD can be used to provide row-level security. The chapter begins with a “Quick Start” section that introduces you to the ease and power that VPD can provide. This section can also serve as a refresher for you later.

Next, the chapter examines the various aspects of VPD including how to enable the row-level security features as well as providing examples of how it works with different DML statements—select, insert, update, and delete. One of the most valuable sections is “Debugging RLS Policies,” which offers tips and tricks for troubleshooting the VPD implementation. The transparency that works as your ally for security reasons also works against you when things go wrong. The chapter explores common mistakes to avoid as well as ways to help you track down the source of your errors.

Oracle Database 10g introduces a new feature to VPD called Column Sensitive polices, and you will see how to use this. The VPD Performance section suggests how to maintain high-performing security policies and provides examples of the new caching methods introduced in the Oracle Database 10g release.

When I first began working for Oracle, I was asked to work on a Department of Defense (DoD) project that was using a special version of Oracle called Trusted Oracle. Trusted Oracle ran on special “trusted” operating systems. I was familiar with Oracle, and I was familiar with UNIX operating systems, but working with Trusted Oracle was really bizarre. A lot of what I had learned about access controls and security was somehow deficient in this world.

The one behavior that I quickly realized was distinctly different was that Trusted Oracle transparently filtered data records. I found out that the DoD security requirements dictated mandatory separation of records based on a user’s authorizations. In this case the users were authorized for access at different sensitivity levels—SECRET, CONFIDENTIAL, and UNCLASSIFIED. The data was intermingled within tables at various sensitivity levels. One user accessing the data would see one set of records, and a different user with different authorizations would see a different set of records.

The interesting part was that the security was implemented so that it was transparent and could not be subverted. The manner in which Trusted Oracle behaved and the requirements from customers in other industries gave Oracle the idea of abstracting the row-level security features from Trusted Oracle into a framework that would support practically any data model and security policy. This was the genesis of the Virtual Private Database technology.

Officially, the phrase “Virtual Private Database (VPD)” refers to the use of row-level security (RLS) and the use of application contexts. (Application contexts were discussed in detail in Chapter 9.) However, the term “VPD” is commonly used when discussing the use of the row-level security features irrespective of implementation.

Many examples you see using VPD involve the use of application contexts and/or several data tables with esoteric column names and complicated referential integrity constraints. I find that these elements, while truthful in their representation of many database schemas, tend to confuse and mislead the reader about how the row-level security technology works and precisely what is needed to enable it. Using RLS is easy, and the purpose of this section is to prove this very point.

VPD’s row-level security allows you to restrict access to records based on a security policy implemented in PL/SQL. A security policy, as used here, simply describes the rules governing access to the data rows. This process is done by creating a PL/SQL function that returns a string. The function is then registered against the tables, views, or synonyms you want to protect by using the DBMS_RLS PL/SQL package. When a query is issued against the protected object, Oracle effectively appends the string returned from the function to the original SQL statement, thereby filtering the data records.

This example will focus on the process required to enable RLS. The intention is to keep the data and security policy simple so as not to distract from how to enable an RLS solution.

The RLS capability in Oracle requires a PL/SQL function. The function accepts two parameters, as shown next. The database will call this function automatically and transparently. The string value returned from the function (called the predicate) will be effectively added to the original SQL. This results in an elimination of rows and thus provides the row-level security.

The security policy for this example will exclude Department 10 records from queries on SCOTT.EMP. The PL/SQL function to implement this will look as follows:

sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_dept10(
  2    p_schema  IN VARCHAR2,
  3    p_object  IN VARCHAR2)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    RETURN 'deptno != 10';
  8  END;
  9  /
Function created.

To protect the SCOTT.EMP table, simply associate the preceding PL/SQL function to the table using the DBMS_RLS.ADD_POLICY procedure:

sec_mgr@KNOX10g> BEGIN
  2    DBMS_RLS.add_policy
  3       (object_schema    => 'SCOTT',
  4        object_name      => 'EMP',
  5        policy_name      => 'quickstart',
  6        policy_function  => 'no_dept10');
  7  END;
  8  /
PL/SQL procedure successfully completed.

That’s it; you are done! To test this policy, log on as a user with access to the SCOTT.EMP table and issue your DML. The following shows all the department numbers available in the table. Department 10 is no longer seen because the RLS policy transparently filters out those records:

scott@KNOX10g> -- Show department numbers. scott@KNOX10g> -- There should be no department 10.
scott@KNOX10g> SELECT DISTINCT deptno FROM emp;
   DEPTNO
---------
       20
       30

The important point is that row-level security can be trivial to implement.

NOTE

RLS has no requirements or dependencies on the use of application contexts, the user’s identity, or the predicate referencing the table’s columns.

Changing the security implementation is trivial, too. Suppose the security policy is changed so that no records should be returned for the user SYSTEM:

sec_mgr@KNOX10g> -- change policy implementation to
sec_mgr@KNOX10g> -- remove all records for the SYSTEM user
sec_mgr@KNOX10g> CREATE OR REPLACE FUNCTION no_dept10 (
  2    p_schema  IN  VARCHAR2,
  3    p_object  IN  VARCHAR2)
  4    RETURN VARCHAR2
  5  AS
  6  BEGIN
  7    RETURN 'USER != ''SYSTEM''';
  8  END;
  9  /
Function created.
sec_mgr@KNOX10g> -- Test by counting records as SCOTT
sec_mgr@KNOX10g> -- then by counting records as SYSTEM
sec_mgr@KNOX10g> CONN scott/tiger
Connected.
scott@KNOX10g> SELECT COUNT(*) Total_Records FROM emp;
TOTAL_RECORDS
-------------
           14
scott@KNOX10g> CONN system/manager Connected.
system@KNOX10g> SELECT COUNT(*) Total_Records FROM scott.emp;
TOTAL_RECORDS
-------------
            0

Notice that the security policy implemented by the function can change without requiring any re-registration with the DBMS_RLS package.