Writing to OLS Protected Tables

While the preceding examples performed a few insert operations, the major emphasis until now has been on read protection of the data. The READ_CONTROL option placed on tables uses Oracle’s VPD technology to limit the records returned by queries. Inserts, updates, and deletes of data, collectively known in the OLS world as write control, use VPD and after-row database triggers to enforce the security policies.

User authorizations are defined by combinations of levels, compartments, and groups. There are specific algorithms followed by OLS to control how data is accessed for SELECT, INSERT, UPDATE, and DELETE operations. The Oracle Label Security Administrator’s Guide produces an excellent explanation with flow diagrams for these algorithms; search for “Label Evaluation Process” in Chapter 3 of the guide for more information.

Understanding Write Authorizations

When OLS applies security for reading or writing data, it can be thought of as a set filtering process. The first access mediation is based on the user’s level. There is a valid set of data that falls into the user’s authorization level. This set is then further filtered based on the user’s compartment authorizations. Lastly, the resulting set is filtered by the user’s group authorizations.

This set filtering description is useful in understanding how OLS implements write access controls. A user’s write authorizations are a subset of their read authorizations. Put another way, a user can never write data that they can’t read. This basic principle will help you in understanding and assigning OLS privileges to the user because there is a read-write access control relationship.

For each label component, the OLS administrator can define three access lists that determine the user’s read and write authorizations. The first list is a set of all possible values the user can possess within their session for that component. That is, a user can never set their label to anything outside of this list. For levels, this is the range between and including the minimum and maximum level. For compartments and groups, this is the Write access control list. The Write lists are the set to which the user can perform insert, update, or delete operations.

The next access list is called Default. Entries in the Default list are automatically set for the user when their profile is set either during logon or when the SET_ACCESS_PROFILE procedure is executed.

Lastly, there is a Row list. This list becomes part of the label for any data that is inserted or updated when the LABEL_DEFAULT option is enabled. You witnessed this for inserted data with the examples in the “Using the Default Session Label” section.

Figure 12-11 illustrates the three lists for the Compartments component as seen from the Oracle Policy Manager for the US_SALES_MGR profile. The figure shows the user has the SALES compartment for WRITE, DEFAULT, and ROW. WRITE specifies the user can perform insert, update, or delete operations on data labeled with the SALES compartment; DEFAULT indicates the SALES compartment is set in the user’s label when the profile is enabled; and ROW indicates the SALES compartment automatically will be written to the data label if using the LABEL_DEFAULT option.

image from book
Figure 12-11: User authorization labels are set and row access is determined by the WRITE, DEFAULT, and ROW attributes.

Level Insertion

The best way to ensure your understanding of write operations is to study an example. The following example uses the level component to show how the write controls are enforced with OLS. The other label components behave similarly, and you can add them once you are comfortable with access controls based on a single component.

When the user authorizations were created earlier, the level values were automatically set using the value passed in the MAX_READ_LABEL parameter. However, you can further control the different attributes (access lists) of the level component by invoking the SET_LEVELS procedure in the SA_USER_ADMIN package. The procedure accepts four parameters for defining the user’s level characteristics: a maximum level (max_level), a minimum level (min_level), a default level (def_level), and a row level (row_level).

In this test, the user SCOTT will be authorized with specific level values. Assume your security policy says SCOTT is a manager and he should also be allowed to write data as an employee. Furthermore, when he logs in, he should automatically be allowed to access management-level information. However, if he is adding new records or updating existing data, you want the data to be labeled at the employee level:

sec_mgr@KNOX10g> -- Create scott's authorizations. sec_mgr@KNOX10g> -- Give a different read level and insert level sec_mgr@KNOX10g> BEGIN   2    sa_user_admin.set_levels   3                   (policy_name    => 'ESBD',   4                    user_name      => 'SCOTT',   5                    max_level      => 'MGR',   6                    min_level      => 'EMP',   7                    def_level      => 'MGR',   8                    row_level      => 'EMP');   9  END;  10  / PL/SQL procedure successfully completed.

At no time will the user’s label contain a level value greater than the maximum level defined by the user’s maximum level. Also, at no time will the user’s label contain a level below the user’s minimum level. The user’s label can fluctuate between the maximum and minimum values.

The user’s default level is set to the value assigned by their default setting. The row level is the level to which the data will be labeled when the user performs an insert or update and the LABEL_DEFAULT option is in effect. When SCOTT logs in, his authorizations are automatically set. SCOTT can query his session label to see that it has been automatically set to manager:

sec_mgr@KNOX10g> CONN scott/tiger   Connected. scott@KNOX10g> COL "Select Label" format a15   scott@KNOX10g> COL "Insert Label" format a15   scott@KNOX10g> SELECT sa_session.read_label ('ESBD') "Select Label",   2         sa_session.row_label ('ESBD') "Insert Label"   3    FROM DUAL; Select Label    Insert Label   --------------- ---------------   MGR             EMP

When SCOTT performs an insert, the data will be automatically labeled at the employee level since the ROW_LEVEL parameter was set to EMP.

scott@KNOX10g> INSERT INTO announcements     2       VALUES ('Scott will be out of the office.'); 1 row created. scott@KNOX10g> COL "OLS Label" format a12   scott@KNOX10g> COL message     format a50   scott@KNOX10g> SELECT MESSAGE, label_to_char (ROWLABEL) "OLS Label"   2    FROM announcements     3   WHERE MESSAGE LIKE 'Scott%'; MESSAGE                                            OLS Label   -------------------------------------------------- ------------   Scott will be out of the office.                   EMP 

OLS allows the user to change their session’s label for levels, compartments, and groups. You can change the label by executing the SA_SESSION.SET_LABEL procedure. In the following example, SCOTT’s label is changed setting the read value to EMP. Any subsequent queries for the session will be restricted to EMP level data.

scott@KNOX10g> -- Show default labels user can access   scott@KNOX10g> COL "User Read Label" format a20   scott@KNOX10g> COL "Data Record Labels" format a20   scott@KNOX10g> BREAK on "User's Read Label" scott@KNOX10g> SELECT sa_session.read_label('ESBD') "User Read Label"   2         label_to_char (ROWLABEL) "Data Record Labels"   3      FROM scott.announcements      4  GROUP BY ROWLABEL; User Read Label      Data Record Labels   -------------------- --------------------   MGR                  MGR   MGR                  EMP     scott@KNOX10g> -- Change the user's default read label   scott@KNOX10g> EXEC sa_session.set_label('ESBD','EMP'); PL/SQL procedure successfully completed. scott@KNOX10g> /     User Read Label      Data Record Labels   -------------------- --------------------   EMP                  EMP

Note that this change is only temporary. When the user logs in later, they will assume the original default label. To change the default row label (which affects insert and update statements), execute the SET_ROW_LABEL procedure. However, you can’t set the default row label to a value outside the user’s current session (read) label. Recall the principle in the beginning of the section— a user can never write data that they can’t read:

scott@KNOX10g> -- This fails if session label is set to EMP   scott@KNOX10g> EXEC sa_session.set_row_label('ESBD','MGR'); BEGIN sa_session.set_row_label('ESBD','MGR'); END;                                     * ERROR at line 1: ORA-12470: NULL or invalid user label: session row label  (other error messages deleted) scott@KNOX10g> -- Reset session and try again   scott@KNOX10g> EXEC sa_session.set_label('ESBD','MGR'); PL/SQL procedure successfully completed. scott@KNOX10g> EXEC sa_session.set_row_label('ESBD','MGR'); PL/SQL procedure successfully completed. scott@KNOX10g> INSERT INTO announcements     2       VALUES ('Scott's management meeting is cancelled.'); 1 row created. scott@KNOX10g> SELECT MESSAGE, label_to_char (ROWLABEL) "OLS Label"   2    FROM announcements; MESSAGE                                             OLS Label   --------------------------------------------------- ------------   Scott will be out of the office.                    EMP   Scott's management meeting is cancelled.            MGR   All Managers: employee compensation announcement... MGR   This message is to notify all employees...          EMP

For compartments and groups, the administrator can set three lists: Write, Default, and Row. The Write enforcement option is analogous to the minimum and maximum levels within the level components. The Default and Row enforcement options are analogous to the corresponding Default and Row options set in the level component.

Groups and Compartments Dependency

Most of the OLS access control algorithms are obvious and easy to understand, but a nuance of the OLS write controls behavior needs to be explained.. Write controls are governed by considering not only the individual compartment or group write authorizations but also the combination of read and write privileges for compartments and groups. The point of interest is the condition when data has been labeled with both compartments and groups. The user has the ability to write to the data as long as they have write access to one of the groups and read access to all of the compartments. Write access to the compartments isn’t needed. To show this dependency in action, the write controls are enabled on the ANNOUNCEMENTS table:

sec_mgr@KNOX10g> DECLARE   2    l_options  VARCHAR2 (50)   3      := 'HIDE,LABEL_DEFAULT,WRITE_CONTROL,READ_CONTROL';   4  BEGIN   5    sa_policy_admin.remove_table_policy   6                     (policy_name    => 'ESBD',   7                      schema_name    => 'SCOTT',   8                      table_name     => 'ANNOUNCEMENTS');   9    sa_policy_admin.apply_table_policy  10                     (policy_name      => 'ESBD',  11                      schema_name      => 'SCOTT',  12                      table_name       => 'ANNOUNCEMENTS',  13                      table_options    => l_options);  14  END;  15  / PL/SQL procedure successfully completed. 

Two compartments are added for SCOTT. Since SCOTT carries no commission (as determined by a NULL value in the EMP table), you can conclude that SCOTT works in development and will therefore have read and write access to data labeled with the DEV compartment:

sec_mgr@KNOX10g> -- Add DEV compartment to SCOTT sec_mgr@KNOX10g> -- for read/write operations sec_mgr@KNOX10g> BEGIN   2    sa_user_admin.add_compartments   3                 (policy_name    => 'ESBD',   4                  user_name      => 'SCOTT',   5                  comps          => 'DEV',   6                  access_mode    => sa_utl.read_write,   7                  in_def         => 'Y',   8                  in_row         => 'Y');   9  END;  10  / PL/SQL procedure successfully completed.

The SALES compartment will also be granted to SCOTT but with read only access. You’ll see in the following code that this can be overridden based on the existence of groups. The SALES compartment will be set in SCOTT’s default label but not as his default row label since SCOTT, who works in development, shouldn’t write data labeled with the SALES compartment.

sec_mgr@KNOX10g> -- Add SALES compartment for SCOTT as read only. sec_mgr@KNOX10g> BEGIN    2    sa_user_admin.add_compartments   3                  (policy_name    => 'ESBD',   4                   user_name      => 'SCOTT',   5                   comps          => 'SALES',   6                   access_mode    => sa_utl.read_only,   7                   in_def         => 'Y',   8                   in_row         => 'N');   9  END;  10  /     PL/SQL procedure successfully completed.

Figure 12-12 illustrates the results of these two additions.

image from book
Figure 12-12: Write authorizations can be easily verified using Oracle Policy Manager.

Lastly, SCOTT is added to the US group. Recall this is a parent group of the LA and NY groups, so SCOTT will be able to access records labeled with those groups too.

sec_mgr@KNOX10g> -- Add US group to Scott for read/write operations   sec_mgr@KNOX10g> -- both as a default label and as insert/update label   sec_mgr@KNOX10g> BEGIN     2    sa_user_admin.add_groups   3                 (policy_name    => 'ESBD',   4                  user_name      => 'SCOTT',   5                  GROUPS         => 'US',   6                  access_mode    => sa_utl.read_write,   7                  in_def         => 'Y',   8                  in_row         => 'Y');   9  END;  10  /     PL/SQL procedure successfully completed. 

SCOTT’s final label authorizations can now be tested. In the following code, the first query shows SCOTT’s read and write label authorizations. Note that the SALES compartment isn’t in his write label. The subsequent query shows the label distribution in the table. Also note that there are four records that have a SALES compartment as part of the label:

scott@KNOX10g> -- Show user's read and write labels   scott@KNOX10g> SELECT sa_session.read_label ('ESBD') "Read Label",   2         sa_session.write_label ('ESBD') "Write Label"   3    FROM DUAL; Read Label                     Write Label   ------------------------------ ------------------------------   MGR:DEV,SALES:US,NY,LA         MGR:DEV:US,NY,LA scott@KNOX10g> -- Show how labels are spread across data in table   scott@KNOX10g> SELECT label_to_char (ROWLABEL) "OLS Label",   2         COUNT (ROWLABEL) "Total Records Labeled"   3      FROM announcements     4  GROUP BY ROWLABEL; OLS Label    Total Records Labeled   ------------ ---------------------   MGR                              2   EMP                              2 MGR:SALES                        1 MGR:DEV                          1   EMP:SALES                        1   EMP:DEV                          1   MGR:SALES:US                     1 EMP:SALES:NY                     1 EMP:SALES:LA                     1 9 rows selected.

The importance of the bolded records is shown next. SCOTT issues a delete, which targets all data labeled with the SALES compartment, but SCOTT doesn’t have write access to this compartment. How many records will get deleted?

scott@KNOX10g> -- Delete data labeled with SALES compartment. scott@KNOX10g> DELETE FROM announcements   2        WHERE label_to_char (ROWLABEL) LIKE '%:SALES:%';  3 rows deleted. scott@KNOX10g> SELECT label_to_char (ROWLABEL) "OLS Label",   2         COUNT (ROWLABEL) "Total Records Labeled"   3      FROM announcements      4  GROUP BY ROWLABEL; OLS Label    Total Records Labeled ------------ ---------------------   MGR                              2   EMP                              2   MGR:SALES                        1 MGR:DEV                          1   EMP:SALES                        1   EMP:DEV                          1   6 rows selected.

SCOTT successfully deleted records labeled with the SALES compartment even though he doesn’t have write access to the SALES compartment. This is not a bug. The dependency is best summarized in the Oracle Label Security Administrator’s Guide, which has been slightly altered to fit the example, but otherwise states the following: If the data label has groups, and the user has write access to one of the groups, the user only needs read access to (all) the compartments to write the data. If the data label has no groups, then the user must have write access on all the compartments in the label to write the data.

The deleted records contained group components to which SCOTT had write access (the US group of which NY and LA are children). Those bolded records are shown in the query prior to the delete statement. The preceding bolded record wasn’t deleted because it has no group component in its data label. SCOTT doesn’t have write access to the SALES compartment, so that record wasn’t deleted.



Effective Oracle Database 10g Security by Design
Effective Oracle Database 10g Security by Design
ISBN: 0072231300
EAN: 2147483647
Year: 2003
Pages: 111

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net