Oracle Label Security, Part 1: Overview
August 29, 2003
Synopsis. Oracle Label Security (OLS) is a relatively new feature of Oracle 9i. It offers a powerful implementation of row-based security that's perfect for restricting user access to specific data, especially in a data mart or data warehousing environment. This article presents a high-level view of this new set of features in preparation for implementation by any reasonably skilled Oracle DBA.
Occasionally I'm granted a respite from my role as senior Oracle DBA at our small but growing telecommunications firm. Lately I've been fulfilling the role of project leader on a major undertaking: revising our existing applications - and by extension, of course, their underlying data structures -- to provide increased flexibility and scalability as our company grows.
During a few recent requirements-gathering sessions, our business analysts uncovered several new sets of specifications. For example, for our existing Human Resources application:
And for a new Sales Reporting system:
And finally, some enhancements for an existing Billing and Accounts Receivable system:
These business rules have several things in common. In some cases, they imply the need to restrict access to results returned based on values stored within the rows used to construct those results. In other cases, the access must be restricted based on the user's position within a hierarchical relationship. And finally, in some cases a user's ability to view data is unencumbered while the ability to update data must be restricted.
One solution is to enforce these business rules at the application level. However, I know from prior experience that there are several pitfalls with this approach. First, data structures and methods to capture and enforce the business rules must be constructed. Second, those structures and methods must be flexible enough to account for all possible levels of security, including interaction between the different types of restrictions. Finally, the application developer must be sure to utilize these methods properly to enforce the business rules properly in the application.
The good news is that I can handle just about every possible business rule permutation described previously with Oracle's answer for row-level data security: Oracle Label Security ("acronyzed" to OLS for the purpose of these articles).
How It Works
Oracle already provides discretionary access control (DAC) through the familiar method of granting object-level permissions to database users. For example, when I issue a GRANT for user SCOTT to SELECT, INSERT, or UPDATE the values in the SALES_HISTORY table, SCOTT now has full permission to view, create, and update any rows in that table, but cannot delete them. This type of control is still too broad to restrict users to viewing the contents of SALES_HISTORY for a select group of salespeople, geographic regions, or sensitivity.
OLS relies upon the concept of the Virtual Private Database (VPD) available as part of Oracle Enterprise Edition to expand security to the row level. Essentially, once the business rules are in place via OLS, VPD will append the appropriate additional selection criteria to any issued SQL statements to limit a user's access to only the appropriate data based on the business rules being enforced.
What makes VPD even more elegant is that application of the rules are handled "behind the scenes" without the user's knowledge. For example, if I've implemented a rule that user SCOTT can view only those rows in the SALES_HISTORY table with his USERID stamp, VPD automatically appends that selection criteria (WHERE SALES_HISTORY.USERID = 'SCOTT') to the query.
OLS takes VPD to another level for enforcing complex business rules. In a nutshell, here's how it works: