8 Enforcing Row-Level Security with Oracle Label Security

To enable Oracle Label Security:

1. Log into the database instance as user SYS with the SYSDBA administrative privilege.

   For example:

   sqlplus sys as sysdba
   Enter password: password 
   

2. Check if Oracle Label Security has been registered with the database.

   SELECT STATUS FROM DBA_OLS_STATUS WHERE NAME = 'OLS_CONFIGURE_STATUS';
   

   If it returns TRUE, then Oracle Label Security has been registered with the database. If the output is FALSE, then run the following procedure:

   EXEC LBACSYS.CONFIGURE_OLS;

3. Check if Oracle Label Security is enabled. The PARAMETER column is case sensitive, so use the case shown here.

   SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Oracle Label Security';
   

   If it returns TRUE, then Oracle Label Security is enabled. Go to "Step 2: Enable the LBACSYS Account". If it returns FALSE, then run the following procedure to enable it:

   EXEC LBACSYS.OLS_ENFORCEMENT.ENABLE_OLS;
   

4. If you needed to register and enable Oracle Label Security, then connect as user SYS with the SYSOPER privilege.

   CONNECT sys as sysoper
   Enter password: password
   

5. Restart the database.

   SHUTDOWN IMMEDIATE
   STARTUP

To enable the Oracle Label Security LBACSYS user account:

1. Access the Database home page for your target database as user SYS with the SYSDBA administrative privilege.

   See Oracle Database 2 Day DBA for more information.

2. From the Administration menu, select Security, then Users.

3. Select the LBACSYS account and click Edit.

   If the account is active (the status will say OPEN), then go to "Step 3: Create a Role and Three Users for the Oracle Label Security Tutorial".

4. In the Edit User: LBACSYS page, enter the following settings:

   • Enter Password and Confirm Password: Enter a password that meets the requirements in "Requirements for Creating Passwords".

   • Status: Set the status to Unlocked.

5. Click Apply.

6. Select the System Privileges tab.

7. In the System Privileges page, select the Edit List button.

8. In the Modify System Privileges page, select SELECT ANY DICTIONARY from the Available System Privileges list, and then move it to the Selected System Privileges list. Click OK.

9. In the Edit User page, click Apply.

Topics:

• Creating a Role

• Creating the Users

To create the ACCESS_LOCATIONS policy:

1. Log in to the Enterprise Manager target database as user as user LBACSYS with the NORMAL role selected.

2. From the Administration menu, select Security, then Oracle Label Security.

3. In the Label Security Policies page, click Create.

4. In the Create Label Security Policy page, enter the following information:

   • Name: ACCESS_LOCATIONS

   • Label Column: OLS_COLUMN

     Later on, when you apply the policy to a table, the label column is added to that table. By default, the data type of the policy label column is NUMBER(10).

   • Hide Label Column: Deselect this box so that the label column will not be hidden. (It should be deselected by default.)

     Usually, the label column is hidden, but during the development phase, you may want to have it visible so that you can check it. After the policy is created and working, hide this column so that it is transparent to applications. Many applications are designed not to show an another column, so hiding the column prevents the application from breaking.

   • Enabled: Select this box to enable the policy. (It should be enabled by default.)

   • Inverse user's read and write groups (INVERSE_GROUP): Do not select this option.

   • Default Policy Enforcement Options: Select Apply Policy Enforcement, and then select the following options:

     For all queries (READ_CONTROL)

     To use session's default label for label column update (LABEL_DEFAULT)

5. Click OK.

   The ACCESS_LOCATIONS policy appears in the Label Security Policies page.

   
   Description of the illustration GUID-CF237188-D3F3-4F0B-B2E8-EC98E4A32CE2-default.gif

At a minimum, you must create one or more levels, such as PUBLIC or SENSITIVE; and define a long name, a short name, and a number indicating the sensitivity level. Compartments and groups are optional.

The level numbers indicate the level of sensitivity needed for their corresponding labels. Select a numeric range that can be expanded later on, in case your security policy needs more levels. For example, to create the additional levels LOW_SENSITIVITY and HIGH_SENSITIVITY, you can assign them numbers 7300 (for LOW_SENSITIVITY) and 7600 (for HIGH_SENSITIVITY), so that they fit in the scale of security your policy creates. Generally, the higher the number, the more sensitive the data.

Compartments identify areas that describe the sensitivity of the labeled data, providing a finer level of granularity within a level. Compartments are optional.

Groups identify organizations owning or accessing the data. Groups are useful for the controlled dissemination of data and for timely reaction to organizational change. Groups are optional.

In this step, you define the level components, which reflect the names and relationships of the SENSITIVE, CONFIDENTIAL, and PUBLIC labels that you must create for the ACCESS_LOCATIONS policy.

To define the label components for the ACCESS_LOCATIONS policy:

1. In the Label Security policies page, select the ACCESS_LOCATIONS policy, and then select Edit.

2. In the Edit Label Security Policy page, select the Label Components subpage.

3. Under Levels, click Add 5 Rows, and then enter a long name, short name, and a numeric tag as follows. (To move from one field to the next, press the Tab key.)

   Long Name	Short Name	Numeric Tag
   SENSITIVE	SENS	3000
   CONFIDENTIAL	CONF	2000
   PUBLIC	PUB	1000

4. Click Apply.

To create the data label, you must assign a numeric tag to each level. Later on, the tag number will be stored in the security column when you apply the policy to a table. It has nothing to do with the sensitivity of the label; it is only used to identify the labels for the policy.

To create the data labels:

1. Return to the Label Security policies page by selecting the Label Security Policies link.

2. Select the selection button for the ACCESS_LOCATIONS policy.

3. In the Actions list, select Data Labels, and then click Go.

4. In the Data Labels page, click Add.

5. In the Create Data Label page, enter the following information:

   • Numeric Tag: Enter 1000.

   • Level: Enter PUB.

     
     Description of the illustration GUID-7DCB584D-8CB4-400E-ACCB-89946F96827A-default.gif

6. Click OK.

   The data label appears in the Data Labels page.

7. Click Add again, and then create a data label for the CONF label as follows:

   • Numeric Tag: Enter 2000.

   • Level: Select CONF from the list.

8. Click OK.

9. Click Add again, and then create a data label for the SENS label as follows:

   • Numeric Tag: Enter 3000.

   • Level: Select SENS from the list.

10. Click OK.

    The CONF, PUB, and SENS labels appear in the Data Labels page.

    
    Description of the illustration GUID-0C00EA4F-D8A8-47DE-8B0A-E914E4954D6B-default.gif

    Later, the tag number will be stored in the security column when you apply the policy to the HR.LOCATIONS table. It has nothing to do with the sensitivity of the label; it is only used to identify the labels for the policy.

To create user authorizations for the policy:

1. Return to the Label Security policies page by selecting the Label Security Policies link.

2. Select the selection button for the ACCESS_LOCATIONS policy.

3. In the Actions list, select Authorization, and then click Go.

4. In the Authorization page, click Add Users.

5. In the Add User: Users page, under Database Users, click Add.

   The Search and Select: Userpage appears. Enter SKING, and then click Go.

   Typically, a database user account already has been created in the database, for example, by using the CREATE USER SQL statement.

   The other option is Non Database Users. Most application users are considered nondatabase users. A nondatabase user does not exist in the database. This can be any user name that meets the Oracle Label Security naming standards and can fit into the VARCHAR2(30) length field. However, be aware that Oracle Database does not automatically configure the associated security information for the nondatabase user when the application connects to the database. In this case, the application must call an Oracle Label Security function to assume the label authorizations of the specified user who is not a database user.

6. Select the check box for user SKING, and then click Select.

   The Create User page lists user SKING.

   
   Description of the illustration GUID-153C893E-8861-4791-B049-D1E93CADAB6A-default.gif

7. Select the check box for user SKING and then click Next.

   (You may need to refresh the page to display user SKING's check box.)

8. In the Privileges page, select Next to move to the Audit page.

   Oracle Label Security enforces the policy through the label authorizations. The Privileges page enables the user to override the policy label authorization, so do not select any of its options.

9. In the Labels, Compartments and Groups page, enter the following settings:

   • Maximum Level: SENS (for SENSITIVE)

   • Minimum Level: CONF (for CONFIDENTIAL)

   • Default Level: SENS

   • Row Level: SENS

10. Click Next to go to the Audit page.

11. In the Audit pane of the Add Users: Audit page, ensure that all of the audit operations are set to None, and then click Next.

    The Review page appears.

    
    Description of the illustration GUID-D4975BA4-ADAF-448E-8696-663B7A4472F4-default.gif

12. Ensure that the settings are correct, and then click Finish.

    The Review page lists all the authorization settings you have selected.

13. Repeat Step 4 through Step 12 to create the following authorizations for user KPARTNER, so that she can read confidential and public data in HR.LOCATIONS.

    • Privileges: Select no privileges.

    • Labels, Compartments And Groups: Set the four levels to the following:

      • Maximum Level: CONF (for CONFIDENTIAL)

      • Minimum Level: PUB (for PUBLIC)

      • Default Level: CONF

      • Row Level: CONF

    • Audit: Set all to None.

14. Create the following authorizations for user LDORAN, who is only allowed to read public data from HR.LOCATIONS:

    • Privileges: Select no privileges.

    • Labels, Compartments And Groups: Set all four levels to PUB.

    • Audit: Set all to None.

To apply the ACCESS_LOCATIONS policy to the HR.LOCATIONS table:

1. Return to the Label Security policies page by selecting the Label Security Policies link.

2. Select the selection button for the ACCESS_LOCATIONS policy.

3. In the Actions list, select Apply, and then click Go.

4. In the Apply page, click Create.

   The Add Table page appears.

5. In the Table field, enter HR.LOCATIONS.

6. Ensure that the Hide Policy Column box is not selected.

7. Ensure that the Enabled box is selected.

8. Under Policy Enforcement Options, select Use Default Policy Enforcement.

   By choosing Use Default Policy Enforcement, you are automatically choosing these options:

   • For all queries (READ_CONTROL)

   • Use session's default label for label column update (LABEL_DEFAULT)

9. Click OK.

   The ACCESS_LOCATIONS policy is applied to the HR.LOCATIONS table.

   
   Description of the illustration GUID-BCA579E0-9476-4C4C-B881-500F133A5FEB-default.gif

For the user HR (the owner of that table) to accomplish this, the user must have FULL access to locations before being able to add the data labels to the hidden OLS_COLUMN column in LOCATIONS.

Topics:

• Granting HR FULL Policy Privilege for the HR.LOCATIONS Table

• Updating the OLS_COLUMN Table in HR.LOCATIONS

To test the ACCESS_LOCATIONS policy:

1. In SQL*Plus, connect as user sking.

   CONNECT sking
   Enter password: password
   

2. Enter the following:

   The following commands format the width of the table columns so that you can read them easier. You only need to perform this step once for the entire session (including when kpartner and ldoran log in.)

   COL city HEADING City FORMAT a25
   COL country_id HEADING Country FORMAT a11
   COL Label format a10
   

   Now enter the SELECT statement as follows:

   SELECT CITY, COUNTRY_ID, LABEL_TO_CHAR (OLS_COLUMN)
   AS LABEL FROM HR.LOCATIONS ORDER BY OLS_COLUMN;
   

   User sking is able to access all 23 rows of the HR.LOCATIONS table. Even though he is only authorized to access rows that are labeled CONF and SENS, he can still read (but not write to) rows labeled PUB.

   City                      Country     LABEL
   ------------------------- ----------- ----------
   Venice                    IT          PUB
   Utrecht                   NL          PUB
   Bern                      CH          PUB
   Geneva                    CH          PUB
   Sao Paulo                 BR          PUB
   Stretford                 UK          PUB
   Mexico City               MX          PUB
   Hiroshima                 JP          PUB
   Southlake                 US          PUB
   South San Francisco       US          PUB
   South Brunswick           US          PUB
   Seattle                   US          PUB
   Toronto                   CA          PUB
   Whitehorse                CA          PUB
   Bombay                    IN          PUB
   Sydney                    AU          PUB
   London                    UK          PUB
   Oxford                    UK          CONF
   Munich                    DE          CONF
   Roma                      IT          CONF
   Singapore                 SG          SENS
   Tokyo                     JP          SENS
   Beijing                   CN          SENS
   
   23 rows selected.
   

3. Repeat Steps 1 and 2 for users kpartner and ldoran.

   User KPARTNER can access the rows labeled CONF and PUB:

   City                      Country     LABEL
   ------------------------- ----------- ----------
   Venice                    IT          PUB
   Utrecht                   NL          PUB
   Bern                      CH          PUB
   Mexico City               MX          PUB
   Hiroshima                 JP          PUB
   Southlake                 US          PUB
   South San Francisco       US          PUB
   South Brunswick           US          PUB
   Seattle                   US          PUB
   Toronto                   CA          PUB
   Whitehorse                CA          PUB
   Bombay                    IN          PUB
   Sydney                    AU          PUB
   London                    UK          PUB
   Stretford                 UK          PUB
   Sao Paulo                 BR          PUB
   Geneva                    CH          PUB
   Oxford                    UK          CONF
   Munich                    DE          CONF
   Roma                      IT          CONF
   20 rows selected.
   

   User LDORAN can access the rows labeled PUB:

   City                      Country     LABEL
   ------------------------- ----------- ----------
   Venice                    IT          PUB
   Hiroshima                 JP          PUB
   Southlake                 US          PUB
   South San Francisco       US          PUB
   South Brunswick           US          PUB
   Seattle                   US          PUB
   Toronto                   CA          PUB
   Whitehorse                CA          PUB
   Bombay                    IN          PUB
   Sydney                    AU          PUB
   London                    UK          PUB
   Stretford                 UK          PUB
   Sao Paulo                 BR          PUB
   Geneva                    CH          PUB
   Bern                      CH          PUB
   Utrecht                   NL          PUB
   Mexico City               MX          PUB
   
   17 rows selected.
   

4. Exit SQL*Plus.

To remove the components for this tutorial:

1. Return to the Label Security policies page by selecting the Label Security Policies link.

2. Select the ACCESS_LOCATIONS policy and then click Delete. In the Confirmation page, select the Drop column check box and then click Yes.

   Deleting the ACCESS_LOCATIONS policy also drops the OLS_COLUMN column from the HR.LOCATIONS table.

3. Log out of Enterprise Manager and then log back in as user SYSTEM.

4. From the Administration menu, select Security, then Users.

5. Select user KPARTNER, and then click Delete.

6. In the Confirmation page, click Yes.

7. Repeat Step 5 and Step 6 to remove users ldoran and sking.

8. Click the Database Instance link to return to the Database home page.

9. From the Administration menu, select Security, then Roles.

10. Select the role emp_role, and then click Delete.

11. In the Confirmation dialog box, click Yes.

12. Log out of Enterprise Manager.

13. If necessary, disable Oracle Label Security.

    1. Log into the database instance as either SYS with the SYSDBA administrative privilege, or as a user who has been granted the LBAC_DBA role.

       For example:

       sqlplus lbacsys
       Enter password: password
       

    2. Run the following procedure:

       EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS
       

    3. Restart the database:

       CONNECT SYS AS SYSOPER
       Enter password: password
       
       SHUTDOWN IMMEDIATE
       STARTUP