Oracle Label Security, Part 3: Administration

Thursday Oct 16th 2003 by Jim Czuprynski
Share:

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.

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.

Determining User Session Label Permissions

With these rules in hand, it is time to turn my attention to the issues encountered when I attempted to insert a new row using the RGNMGR1 user account. When I run the query in Listing 1.1 from the OLS Administration (LBACSYS) user, I can view all the OLS attributes assigned to all users assigned to the SADM security policy:

             Max          Min          Max          Default      Default
             Read         Write        Write        Read         Write
User         Label        Label        Label        Label        Label
------------ ------------ ------------ ------------ ------------ ------------
RGNMGR1      CW:SA:NE     CW           CW:SA:NE     CW:SA:NE     CW:SA:NE
RGNMGR2      CW:SA:SE     UN           CW:SA:SE     CW:SA:SE     CW:SA:SE
RGNMGR3      CW:SA:CN     UN           CW:SA:CN     CW:SA:CN     CW:SA:CN
RGNMGR4      CW:SA:SW     UN           CW:SA:SW     CW:SA:SW     CW:SA:SW
RGNMGR5      CW:SA:NW     UN           CW:SA:NW     CW:SA:NW     CW:SA:NW
SALESADM     CW:SA:T      UN           CW:SA:T      CW:SA:T      CW:SA:T
SLSMGR       CW:SA:T      UN           CW:SA:T      CW:SA:T      CW:SA:T

7 rows selected.

(If I wanted to see the OLS attributes for all users assigned to the SADM security policy's levels, compartments, and groups, I could run the queries in Listing 1.2, Listing 1.3, and Listing 1.4, respectively.)

From these query results, I can now see the problem: When I applied the security policy to the RGNMGR1 user account via the SET_USER_LABELS procedure of the SA_USER_ADMIN package, I supplied a NULL value for the default write label. OLS therefore assigned the maximum read level (CW:SA:NE) as the default write label, and that's what OLS applied when I INSERTed the new row. Luckily, OLS provides several methods to overcome this issue.

Choosing Methods For Inserting New Rows

First, though, I will need to connect as the OLS administrator (LBACSYS) and create a new valid group for the European region, otherwise, I will never be able to add the correct label value during insertion. I will also GRANT EXECUTE permissions on the TO_DATA_LABEL function for an upcoming example (see Listing 1.5).

Now to attempt some different INSERT methods. First, I could use the CHAR_TO_LABEL function to insert a new row into SALESADM.SALES_REGIONS (see Listing 1.6):

SQL> CONNECT salesadm/password;
Connected.
SQL> DELETE FROM salesadm.sales_regions WHERE region_id = 6;
0 rows deleted.
SQL> INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
  2  VALUES(6, 'EUR', 'European', CHAR_TO_LABEL('SADM', 'CW:SA:EU'));
1 row created.
SQL> COMMIT;
Commit complete.

Here's another approach: I could use a numeric tag value (see Listing 1.7):

SQL> CONNECT salesadm/password;
Connected.
SQL> DELETE FROM salesadm.sales_regions WHERE region_id = 6;
1 row deleted.
SQL> INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
  2  VALUES(6, 'EUR', 'European', 30160);
1 row created.
SQL> COMMIT;
Commit complete.

However, in both these cases, I will end up with a data label that is not necessarily valid, i.e. has not been defined for the OLS security policy. I have another option: I can use the TO_DATA_LABEL function to create a valid data label "on the fly" (see Listing 1.8):

SQL> DELETE FROM salesadm.sales_regions WHERE region_id = 6;
1 row deleted.
SQL> INSERT INTO salesadm.sales_regions (region_id, abbr, description, sadm_lbl)
  2  VALUES(6, 'EUR', 'European', TO_DATA_LABEL('sadm', 'CW:SA:EU'));
1 row created.
SQL> COMMIT;
Commit complete.

However, this is somewhat dangerous, since now the RGNMGR1 user could create any data label value desired, and I would like to keep my data labels nice and tidy. To prevent this, I can also temporarily change the default label for the RGNMGR1's user session.

Modifying User Session Label Permissions

OLS supplies several functions to allow a user to change her session attributes using the SA_SESSION package. SA_SESSION.SET_LABEL allows a session user to change his current security level, compartment(s), and group(s) to any valid label to which he has read access. Likewise, SA_SESSION.SET_ROW_LABEL allows a session user to set the default row label that will be applied to any new rows inserted into an OLS-secured table. Here is the result of applying changes to RGNMGR1's current user session (see Listing 1.9):

SQL> BEGIN
  2     SA_SESSION.SET_LABEL('SADM', 'CW:SA:EU');
  3     SA_SESSION.SET_ROW_LABEL('SADM', 'CW:SA:EU');
  4  END;
  5  /

PL/SQL procedure successfully completed.

SQL> 
SQL> COL sa_user_name FORMAT A12 HEADING 'User Name'
SQL> COL label        FORMAT A20 HEADING 'Default Label'
SQL> COL row_label    FORMAT A12 HEADING 'Row Label'
SQL> SELECT
  2      sa_user_name
  3     ,label
  4     ,row_label
  5    FROM user_sa_session
  6  ;

User Name    Default Label        Row Label                                     
------------ -------------------- ------------                                  
RGNMGR1      CW:SA:EU             CW:SA:EU                                      

SA_SESSION.RESTORE_DEFAULT_LABELS and SA_SESSION.SAVE_DEFAULT_LABELS allow a session user to restore and save default label values, respectively. See Listing 1.10 for an example of how these procedures work.

SQL> BEGIN
  2     SA_SESSION.RESTORE_DEFAULT_LABELS('SADM');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> COL sa_user_name FORMAT A12 HEADING 'User Name'
SQL> COL label        FORMAT A20 HEADING 'Default Label'
SQL> COL row_label    FORMAT A12 HEADING 'Row Label'
SQL> SELECT
  2      sa_user_name
  3     ,label
  4     ,row_label
  5    FROM user_sa_session
  6  ;

User Name    Default Label        Row Label
------------ -------------------- ------------
RGNMGR1      CW:SA:NE,EU          CW:SA:NE,EU

Oracle Policy Manager: A Sharp Blade To Cut Through OLS Confusion

I have found Oracle Policy Manager to be an excellent graphical tool to view the OLS information I have set up for these test cases. Though I'm more in favor of using PL/SQL scripts to accomplish the goal of setting up an OLS security policy, Policy Manager's intuitive interface lets me view and maintain all OLS security policy components, data labels, and user session assignments, as well as which portions of the security policy have been applied to which schema objects.

While a discussion of the myriad uses of Policy Manager is beyond the scope of this article, I have captured some screen images in Listing 2 that reflect the contents of the SALESADM security policy and its application to users and objects.

Conclusion

I have delved more deeply into what happens behind the scenes when a user session attempts to read and write rows that are under the control of OLS, and how to manage the user session labels to get the correct security label values applied to the rows. In my next article, I will cover some additional methods for managing the addition of how to use a label function to apply row label security to rows automatically. I will also supply some additional concrete examples on how to manage OLS security policy components.

References and Additional Reading

While there 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 article.

» See All Articles by Columnist Jim Czuprynski

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved