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.