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