Oracle Label Security, Part 3: Administration - Page 2

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