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.
