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:

• An employee should be allowed to view his own vacation and sick time hours, but not adjust them.
• A department supervisor is allowed to view and adjust vacation and sick time hours for only the employees within her department.
• Only the head of Human Resources is allowed to view and adjust vacation and sick time for all employees, including department supervisors.

And for a new Sales Reporting system:

• Wholesalers are allowed to see sales information only for their customers.
• Salespeople are allowed to see sales information only for the wholesalers they are responsible for calling upon.
• Account Executives are allowed to see sales information for only the customers within their assigned geographic sales regions.

And finally, some enhancements for an existing Billing and Accounts Receivable system:

• Billers can only create invoices for their assigned customers, but they can view any invoice to help resolve customer billing inquiries.
• Only the head of Accounts Receivable is allowed to create and post General Ledger entries to the company's books.

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:

• First, security policies are established to identify how the data needs to be secured by specification of security components for the policies.
• Next, user labels are established that define what row-level security policies are possible for each user.
• For each table that needs to enforce row-level security, a special column called a label column is built and populated.
• During data access, a process called access mediation determines which permissions are required to access the row, and what actions can be performed on the row once it's accessed.