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 concluding article wraps
up this series with discussion of some advanced OLS features as well as
mechanisms for maintaining an existing OLS security policy.
In Part 3 of this series, I expanded upon an initial
implementation of OLS, including a discussion of access mediation, user
session security, and how to modify security labels for rows that have already
been secured via OLS. I also made a brief excursion into using Oracle Policy
Manager for viewing all related information for an OLS security policy. In this
final article, I will take a look at some hints for creating row labels
effectively, a review of how to use label functions when inserting,
updating, and deleting rows, how to audit for changes to OLS security policies
and how to maintain or drop an OLS security policy and its
Querying Secured Data: Tricks and Techniques
will review some tricks and techniques I have used to help determine how data
secured with OLS is obtained from database tables using the row security
of Ordered Security Label Values. Oracle suggests that when the OLS security policy is set
up, the label tags should be assigned in increasing level of data sensitivity.
For example, the SADM security policy label tags range from a lower bound of
10000 for Unsecured data through 70000 for Top-Secret data. Since
it is likely that most data will not require the highest security to be
applied, relatively few rows will be tagged as Top Secret. I can also write a
query that filters criteria against the value of the data label itself. And I
can also apply an ORDER BY clause against the data label value to sort returned
rows within highest to lowest security applied, or vice versa.
Another advantage to this approach is that since the
security label is stored as a numeric value, the data security column values
can be effectively indexed for faster retrieval. Moreover, when a table
holds an extremely large number of rows, I could even consider implementing
range-based or list-based partitioning for more efficient access as well as for
security. (For example, all highly sensitive rows could be placed in their own
separate partition, thus isolating those rows from other less-sensitive ones).
Which Rows Can Be Dominated. If I want to see what the minimum and maximum
OLS data label values that are returned by a query are, I can utilize the
LEAST_UBOUND and GREATEST_LBOUND functions, respectively, to determine them.
This information can be helpful in determining what access permissions are
required to dominate all rows in a subset of data, or to find out why my query is
not working as expected for a user's session. Since for now I have only applied
my security policy to the SALESADM.SALES_REGIONS table, here is an example
using that table (run from the SALESADM user account):
SQL> -- Show lower bound and upper bound of rows returned from a query
SQL> COL region_id FORMAT 999 HEADING 'Rgn'
SQL> COL abbr HEADING 'Abbr'
SQL> COL lowest FORMAT A16 HEADING 'Label|Lower|Boundary'
SQL> COL highest FORMAT A16 HEADING 'Label|Upper|Boundary'
4 ,LEAST_UBOUND(sadm_lbl, sadm_lbl) lowest
5 ,GREATEST_LBOUND(sadm_lbl, sadm_lbl) highest
6 FROM salesadm.sales_regions
Rgn Abbr Boundary Boundary
---- ---- ---------------- ----------------
1 NE00 CW:SA:NE CW:SA:NE
2 SE00 CW:SA:SE CW:SA:SE
3 CN00 CW:SA:CN CW:SA:CN
4 SW00 CW:SA:SW CW:SA:SW
5 NW00 CW:SA:NW CW:SA:NW
Controlling Row Label Values via Label Functions When Inserting a New Row
In my last article,
I discussed the various methods to specify the value for the row security
label. One option is to specify a value for the label each time a new row is
created; another method is to specify the label value in the LABEL_DEFAULT
option. However, neither of these is an optimal solution, as they offer little
flexibility in setting the row label value based on business rules that need to
be enforced as the row is being created.
method I have retained for discussion in this article is to use a label
function to specify the row label value. As its name implies, a label
function typically accepts arguments from the values being inserted and then
uses predefined business rules to calculate the appropriate value.
shows how to create a label function, SALESADM.SF_SADM_SECURITY, which I will
use to apply a row label value for any new rows inserted into the
SALES_DISTRICTS table. Note that for the sake of this example, I have used
simple business logic for now based on the value of the SALES_DISTRICTS.REGION_ID
to create the label value.
1.2, I have updated the row labels of the existing rows of the
SALES_DISTRICTS tables with an appropriate label value before applying the
security policy to the table. Now that the label function is created, the OLS
security policy must be updated to reflect which schema objects should utilize
the function. Listing 1.3
shows how I utilized the new label function to apply to newly inserted rows of
the SALESADM.SALES_DISTRICT table.
prove that the label function is working for newly inserted rows, I have
inserted one new row into the table, and queried the result:
SQL> DELETE FROM salesadm.sales_districts WHERE district_id = 101;
1 row deleted.
SQL> INSERT INTO salesadm.sales_districts
2 VALUES (101, 'EU10', 'Northern Europe', 6);
1 row created.
SQL> COL lbl_value FORMAT A12 HEADING 'Label Value'
6 ,LABEL_TO_CHAR(sadm_lbl) lbl_value
7 FROM salesadm.sales_districts;
DISTRICT_ID ABBR DESCRIPTION REGION_ID Label Value
----------- ---- -------------------------------- ---------- ------------
101 EU10 Northern Europe 6 CW:SA:EU
1 NE10 New England 1 CW:SA:NE
2 NE20 New York 1 CW:SA:NE
3 SE10 Mid-Atlantic 2 CW:SA:SE
4 SE20 Deep South 2 CW:SA:SE
5 CN10 Midwest 3 CW:SA:CN
6 CN20 Great Plains 3 CW:SA:CN
7 SW10 Pacific Coast 4 CW:SA:SW
8 SW20 West Central 4 CW:SA:SW
9 NW10 Oregon-Washington 5 CW:SA:NW
10 NW20 Mid NorthWest 5 CW:SA:NW
11 rows selected.
Updating and Deleting OLS-Secured Rows
Up to this
point, most of my investigations have centered upon the most common DML
operation - the insertion of new rows into the table secured by OLS. However, I
would be remiss if I ignored what happens when a secured row is updated
user's session attempts to make changes to a row, OLS will interrogate the
security policy for the table or schema to determine if sufficient privileges
exist to read the row and thence to write the row. These privilege levels are
set when the security policy is applied to a table using the READ_CONTROL and
permits a finer grain of access control when I am updating or deleting a row
via the INSERT_CONTROL, UPDATE_CONTROL and DELETE_CONTROL options. See Listing 1.4
for an example of how these settings can be applied to the SALES_ZONES table
and a query to validate the settings.
minimum, Oracle recommends setting appropriate values for READ_CONTROL and
WRITE_CONTROL, and then either setting a value for the default row label using
the LABEL_DEFAULT option or utilizing a label function to set the row label
value. The good news is that when I choose to use a label function to calculate
the value of a row label during insertion of new rows, OLS uses the same
function to calculate the row label value when it is updated.
Extending Database Auditing Options with OLS
expanded the standard set of auditing procedures to include the concept of fine-grained
auditing via the DBMS_FGA package. However, as the old saying goes, "Who
will guard the guards?" Since OLS is a critical component to applying and maintaining
the security of sensitive data, it is also important to be able to track what
changes have been made to OLS security policies as well.
OLS auditing features, the AUDIT_TRAIL database initialization parameter must
first be set to either DB (i.e. write records to the database auditing
tables - recommended by Oracle) or to OS (i.e. send auditing records to
the operating system). Unfortunately, this is not a system-modifiable
parameter, so once it has been set in the database's INIT.ORA file, the
database must be restarted to enable Oracle to write auditing information to
the appropriate location.
AUDIT_TRAIL has been set and the database restarted, the SA_AUDIT_ADMIN package
is used to activate or deactivate auditing of changes to existing OLS security
policies. For example, it is possible to track the application to or removal of
an OLS security policy to a table or schema, track changes to a user's
authorizations or privileges, or track changes for all policy-specific privileges.
1.5 shows an example of how to apply OLS auditing for a specific set of
tables in the SALESADM schema, and Listing
1.6 shows how to apply OLS auditing for a specific set of users and user
1.7 shows how to turn on auditing for all OLS policy labels, and Listing 1.8
demonstrates how to create a separate, DBA-level view into which all audited
information is written.
Once the appropriate OLS audit options have been
activated via SA_AUDIT_ADMIN, the activated options can be queried from the
DBA_SA_AUDIT_OPTIONS view (see Listing 1.9).
1.10 gives an example of how to deactivate all OLS auditing options
currently in place.
Modifying an OLS Security Policy and Its Components
this series, I have used several different OLS packages to activate, modify or
deactivate features. Though a detailed examination of each package is beyond
the scope of this article, the scripts in Listing 2 are provided as
examples of the various OLS package procedures that may be utilized to modify
all aspects of an OLS security policy.
Removing an OLS Security Policy and Its Components
here is an example of dropping an entire OLS security policy and all its
components. (I am not ashamed to admit I had to do this several times as I
built examples for the sample Sales Force Administration application.)
in Listing 3 handle the
removal of the existing SADM OLS security policy. Note that these deletion
scripts traverse from the lowest level of the applied security policy elements
up to the final element, the security policy itself. In other words, Oracle
will not permit the removal of the security policy until all lower-level security
policy elements have been removed.
References and Additional Reading
is no substitute for direct experience, reading the manual is not a bad idea,
either. I have drawn upon the Oracle documentation Oracle Label Security
Administrator's Guide (A96578-01) for the deeper technical details of this
See All Articles by Columnist Jim Czuprynski