Oracle Label Security, Part 3: Administration

October 16, 2003

Synopsis. 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 2 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:

SELECT
    region_id
   ,abbr
   ,description
   ,LABEL_TO_CHAR(sadm_lbl) seclbl
 FROM salesadm.sales_regions;

REGION_ID ABBR DESCRIPTION                      SECLBL
--------- ---- -------------------------------- --------------------
        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 maintain 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 1.3 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:

SELECT
   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.
SQL> COMMIT;
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.
SQL> COMMIT;
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.
SQL> COMMIT;
Commit complete.

SQL> -- Show results of DML
SQL> COL region_id 	   FORMAT 9999  HEADING "Rgn"
SQL> COL abbr      	   FORMAT A6    HEADING "Abbr"
SQL> COL description   FORMAT A24   HEADING "Description"
SQL> COL ols_label     FORMAT A16   HEADING "OLS Label"
SQL> SELECT
  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.
SQL> COMMIT;
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.

However, 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

Access mediation 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 level must be greater than or equal to the row's level; and
• the user's session group 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 level limits:

• 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 level. However, note that in write mode, reading down is still permitted.