Oracle Label Security, Part 3: Administration

Oracle Label
Security (OLS) offers a powerful implementation of row-based security that is
perfect for restricting user access to specific data, especially in a data mart
or data warehousing environment. Previous articles presented a brief overview
of how these features work, and how these features can be implemented in any
Oracle database. This article discusses how Oracle Label Security policies can
be administered when data or users change.

In Part
of this series, I offered a practical example of the end-to-end process
required to implement OLS features for a sample Sales Force Administration
application. Part 3 of this series will expand upon the existing sample
implementation to demonstrate additional OLS features, including a discussion
of access mediation. I will also explore how to view and maintain user
session security and modify security labels for rows that have already been
secured via OLS. Finally, I will make a brief excursion into using Oracle
Policy Manager to view all of this OLS information for the security policy, its
users, and the schema objects to which security has been applied.

My previous
article concluded with proof that OLS has been successfully implemented for the
SADM security policy by running this query from the RGNMGR1 user id. Note that
only data has been returned for the Northeast region, which is precisely the
desired result because the RGNMGR1 user is supposed to be able to view data
only for that region:

,LABEL_TO_CHAR(sadm_lbl) seclbl
FROM salesadm.sales_regions;

——— —- ——————————– ——————–
1 NE00 Northeastern United States CW:SA:NE

Here is
another example of a query that uses OLS. In Part 2 of this series, one of the
requirements established was that each Regional Manager can view and
historical customer contact information only for those
customers in the Region for which he/she is responsible. My current OLS
security policy implements the view-only restriction part of this requirement
via a complex view (SALESADM.SALES_MADE see Listing
from the prior article for the DDL used to create the view). The view
combines the SALES_REGION table in the SALESADM schema with the CUSTOMERS and
SALES_HISTORY tables in the SH schema to obtain summary sales information for
those customers only in the Northeast region:

rgn_abbr “Rgn”,
dst_abbr “Dist”,
sum(total_sales) “Total Sales”
FROM salesadm.sales_made
GROUP BY rgn_abbr, dst_abbr;

Rgn Dist Total Sales
—- —- ———–
NE00 NE10 2058062.6
NE00 NE20 25721358.5

How does
OLS react to data manipulation language (DML) statements? Here are sample
INSERT, DELETE and UPDATE statements, run from the RGNMGR1 user:

SQL> — Attempt to insert a new row into SALES_REGION
SQL> INSERT INTO salesadm.sales_regions VALUES(6, ‘EUR’, ‘European’);
1 row created.
Commit complete.

SQL> — For RGNMGR1 user, this statement will succeed …
SQL> UPDATE salesadm.sales_regions
2 SET description = ‘US NorthEast Region’
3 WHERE abbr = ‘NE00’;
1 row updated.
Commit complete.

SQL> — … but for RGNMGR1 user, this statement will fail.
SQL> UPDATE salesadm.sales_regions
2 SET description = ‘US SouthEast Region’
3 WHERE abbr = ‘SE00’;
0 rows updated.
Commit complete.

SQL> — Show results of DML
SQL> COL region_id FORMAT 9999 HEADING “Rgn”
SQL> COL description FORMAT A24 HEADING “Description”
SQL> COL ols_label FORMAT A16 HEADING “OLS Label”
2 region_id
3 ,abbr
4 ,description
5 ,label_to_char(SR.sadm_lbl) ols_label
6 FROM salesadm.sales_regions SR;

Rgn Abbr Description OLS Label
—– —— ———————— —————-
1 NE00 US NorthEast Region CW:SA:NE
6 EUR European CW:SA:NE

SQL> — Attempt to delete newly-inserted row from SALES_REGION
SQL> DELETE FROM salesadm.sales_regions WHERE abbr=’EUR’;
1 row deleted.
Commit complete.

As the results
show, the RGNMGR1 user was able to perform an update and a delete successfully
for the data stamped with the appropriate OLS security labels for the Northeast
region, but could not update the values for the Southeast region. This is
expected behavior.

note that the security label value set for the newly inserted row for the
European region only allows RGRNMGR1 access to that row, which, of course, is
completely unsatisfactory. The reason this is happening is that I have not yet
fully completed the configuration of OLS to handle writing data to the
database via DML statements. This brings us to a more detailed discussion of
one of the most crucial concepts behind OLS: access mediation.

Access Mediation and Row Domination

is the
collection of methods that OLS uses to determine if a user’s session has
sufficient access to a row in either read or write mode. Row domination
is the key to understanding access mediation. Simply put, when a user’s session
has been granted sufficient access to perform the read or write activity
against the row, it is said to dominate a row.

Rules for Reading Secured Data

When a user
submits a query to be parsed for read access, OLS compares the policy
label permissions granted to the user’s session versus the data label on the
target rows based on the following rules:

  • The user’s session
    must be greater than or equal to the row’s level; and

  • the user’s session
    must contain at least one group with read access specified in the row’s data label; and

  • the user’s session
    compartments must contain all the compartments listed in the row
    label’s compartments.

If the user
session’s security passes these tests, then the session dominates the row, and
the user session can read the row; otherwise, the next row is read, the session
label is compared vs. the row label, and so forth, until all data that matches
the query’s selection criteria is processed.

One other
OLS concept that is important to grasp is called reading down. When a
user session has been granted security level permissions, the session
cannot read any data above the level; however, it can read all data at
that level and below.

Finally, it
is important to note that if a data label is NULL, or if it is otherwise
invalid, OLS will deny access to the row because the user’s session cannot
dominate the row.

Rules for Writing Secured Data

When a user
submits DML statements for write access (i.e. INSERT, UPDATE, DELETE, or
MERGE), the implementation of the security policy rules become somewhat more
complex. The first set of rules that are interrogated insure that a user
session cannot write to a row that is outside of the session’s permissible security

  • The row’s level
    must be greater than or equal to the user’s minimum level; and

  • the row’s level
    must be less than or equal to the user’s session level.

The next rule insures that a user session cannot write
outside of the security groups assigned to the session:

  • The user’s session
    labels’ group must contain at least one group with write access
    specified in the row’s data label.

These last
two rules describe the interaction of groups and compartments for
the user session:

  • The compartments
    specified in the user’s session labels must contain all the compartments
    in the data label.

  • If no groups
    are present in the data label, the user must have write access on all
    compartments in the data label.

When OLS
enforces these restrictions in write mode, the end result is that the user
cannot write data that is below the user’s minimum write level,
and the user cannot write data that is above the user’s current session
. However, note that in write mode, reading down is still

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.

Latest Articles