Oracle Label Security, Part 1: Overview

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:

  • 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.
Jim Czuprynski
Jim Czuprynski
Jim Czuprynski has accumulated over 30 years of experience during his information technology career. He has filled diverse roles at several Fortune 1000 companies in those three decades - mainframe programmer, applications developer, business analyst, and project manager - before becoming an Oracle database administrator in 2001. He currently holds OCP certification for Oracle 9i, 10g and 11g. Jim teaches the core Oracle University database administration courses on behalf of Oracle and its Education Partners throughout the United States and Canada, instructing several hundred Oracle DBAs since 2005. He was selected as Oracle Education Partner Instructor of the Year in 2009. Jim resides in Bartlett, Illinois, USA with his wife Ruth, whose career as a project manager and software quality assurance manager for a multinational insurance company makes for interesting marital discussions. He enjoys cross-country skiing, biking, bird watching, and writing about his life experiences in the field of information technology.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles