Hiding the Label | Effective Oracle Database 10g Security by Design

OLS provides the ability to hide the label column. Hiding the column means that the ROWLABEL column will not appear in a table DESCRIBE or a SELECT * query. Additionally, insert statements that insert data into all columns (for example, insert into <table> values …) don’t need to specify the label column. You may recall that this column hiding was the behavior that Trusted Oracle exhibited.

Hiding the column can be desirable when you want to obscure the row-level security mechanism from users or applications. However, the user or application can always ask for the label column assuming they know the column name. OLS allows the column to be hidden, but not because it makes the data more secure. If your security is based on the users not knowing the column name, or security through obscurity, you have big challenges ahead of you! Hiding the column allows OLS to be transparently applied to tables with minimal impact on existing applications—those that issue SELECT * queries and insert all column statements. There also are many tools that model database tables. Hiding the label column is desirable for these tools because the label column shouldn’t appear in the table models.

OLS will hide the column when the HIDE directive is passed to either the DEFAULT_OPTIONS parameter of the SA_SYSDBA.CREATE_POLICY procedure, or the TABLE_OPTIONS parameter of the APPLY_TABLE_POLICY procedure. Note that while the label column name is constant for the policy, the HIDE option is enforced on a table-by-table basis.

Changing the Hidden Status

Once an OLS policy has been applied to a table, you can’t change the hidden status of the label column without losing your labels. The effect is illustrated here. Assume you now want to hide the ROWLABEL column on the ANNOUNCEMENTS table. Because you are changing a policy option, you’ll have to remove the policy and then re-apply with the HIDE directive. Unfortunately, OLS will not let you hide the existing label column:

sec_mgr@KNOX10g> -- Drop OLS policy. sec_mgr@KNOX10g> BEGIN   2    sa_policy_admin.remove_table_policy   3                     (policy_name    => 'ESBD',   4                      schema_name    => 'SCOTT',   5                      table_name     => 'ANNOUNCEMENTS');   6  END;   7  / PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- Add Policy with Hide option. sec_mgr@KNOX10g> BEGIN   2    sa_policy_admin.apply_table_policy   3      (policy_name      => 'ESBD',   4       schema_name      => 'SCOTT',   5       table_name       => 'ANNOUNCEMENTS',   6       table_options    => 'HIDE,LABEL_DEFAULT,READ_CONTROL');   7  END;   8  / BEGIN * ERROR at line 1: ORA-12445: cannot change HIDDEN property of column ORA-12432: LBAC error: cannot HIDE column

The only way to change the hidden status is to remove the policy with the DROP_COLUMN parameter (of the REMOVE_TABLE_POLICY procedure) set to TRUE. The problem with this is that when the column is dropped, all the labels in the column are also dropped. This is a problem if you don’t want to remove the labels and only want to hide them. Note in the previous code that the column wasn’t dropped. The policy has been removed, but the labels still reside with the data. A simple fact motivating why this is so important: it recently took me eight hours to label the data in a single table!

A solution to this is to copy the label values to another column before you drop the column. To do this, alter the table and add a new column:

scott@KNOX10g> -- Add temporary column to table scott@KNOX10g> ALTER TABLE announcements ADD (temp_label NUMBER(10)); Table altered.

Next, copy the label values from the OLS label column to your temporary column. Be sure you can access all rows before you try to copy. For this operation, you can either set your authorization to a profile that can access all the records or execute as a user that has the special OLS READ privilege. Refer back to “Special OLS Privileges” for more details on how to do this. To simplify this example, the ALL_EXECS profile is set as follows:

sec_mgr@KNOX10g> -- Set access to all rows sec_mgr@KNOX10g> BEGIN   2    sa_session.set_access_profile ('ESBD',   3                                   'ALL_EXECS');   4  END;   5  / PL/SQL procedure successfully completed. sec_mgr@KNOX10g> -- Copy labels to temporary column sec_mgr@KNOX10g> UPDATE scott.announcements   2     SET temp_label = ROWLABEL; 12 rows updated. sec_mgr@KNOX10g> COMMIT ; Commit complete. sec_mgr@KNOX10g> -- Verify update sec_mgr@KNOX10g> COL message format a55 sec_mgr@KNOX10g> COL "OLS Label" format a12 sec_mgr@KNOX10g> SELECT rownum,   2         label_to_char (ROWLABEL) "OLS Label",   3         rowlabel "Label Value",   4         temp_label "Temp Label Value"   5    FROM scott.announcements;     ROWNUM OLS Label    Label Value Temp Label Value ---------- ------------ ----------- ----------------          1 MGR:SALES             20               20          2 MGR:DEV               25               25          3 EMP:SALES             30               30          4 EMP:DEV               35               35          5 EMP:IS                39               39          6 EMP:SALES:NY         320              320          7 EMP:SALES:LA         330              330          8 EMP:DEV:APAC         410              410          9 MGR:SALES:US         310              310         10 EXEC                   1                1         11 MGR                    2                2         12 EMP                    3                3 12 rows selected.

Now remove the policy with the DROP_COLUMN set to TRUE. This will drop the ROWLABEL column from the table. Next, re-apply the policy with the HIDE option:

sec_mgr@KNOX10g> -- Remove policy and re-apply with HIDE option sec_mgr@KNOX10g> BEGIN   2    sa_policy_admin.remove_table_policy   3      (policy_name    => 'ESBD',   4       schema_name    => 'SCOTT',   5       table_name     => 'announcements',   6       DROP_COLUMN    => TRUE);   7    sa_policy_admin.apply_table_policy   8      (policy_name      => 'ESBD',   9       schema_name      => 'SCOTT',  10       table_name       => 'ANNOUNCEMENTS',  11       table_options    => 'HIDE,LABEL_DEFAULT,READ_CONTROL');  12  13  END;  14  / PL/SQL procedure successfully completed.

The last step is to copy the values back to the OLS-enforced label column and drop the temporary column:

sec_mgr@KNOX10g> -- Copy values back to rowlabel column sec_mgr@KNOX10g> UPDATE scott.announcements   2     SET ROWLABEL = temp_label; 12 rows updated. sec_mgr@KNOX10g> COMMIT ; Commit complete. sec_mgr@KNOX10g> -- Verify update sec_mgr@KNOX10g> SELECT ROWNUM,   2         label_to_char (ROWLABEL) "OLS Label",   3         ROWLABEL "Label Value",   4         temp_label "Temp Label Value"   5    FROM scott.announcements;     ROWNUM OLS Label    Label Value Temp Label Value ---------- ------------ ----------- ----------------          1 MGR:SALES             20               20          2 MGR:DEV               25               25          3 EMP:SALES             30               30          4 EMP:DEV               35               35          5 EMP:IS                39               39          6 EMP:SALES:NY         320              320          7 EMP:SALES:LA         330              330          8 EMP:DEV:APAC         410              410          9 MGR:SALES:US         310              310         10 EXEC                   1                1         11 MGR                    2                2         12 EMP                    3                3 12 rows selected.  sec_mgr@KNOX10g> -- Drop temporary column sec_mgr@KNOX10g> conn scott/tiger Connected. scott@KNOX10g> ALTER TABLE announcements DROP COLUMN temp_label; Table altered. scott@KNOX10g> DESC announcements  Name                                        Null?    Type  ------------------------------------------- -------- ---------------  MESSAGE                                              VARCHAR2(4000)