4 Getting Started with Oracle Label Security

Oracle Label Security (OLS) provides row-level security for your database tables. It protects data rows by labeling individual rows. If a user tries to access a data row protected by a policy, then he must have proper authorization as determined by the OLS label for the row.

This chapter helps you get started with OLS. It discusses the tasks involved in creating a simple OLS policy. It also uses a scenario to help you create and test a sample OLS policy. This chapter includes the following topics:

4.1 Installing OLS and Enabling the LBACSYS User

A default Oracle Database installation does not include Oracle Label Security (OLS). Use Oracle Universal Installer to install OLS in an existing database. OLS provides its own user account, LBACSYS, which you need to enable after the installation.

This section covers the following topics:

Installing Oracle Label Security

This procedure explains how to install Oracle Label Security in an existing database.

Note:

Before you run Oracle Universal Installer (OUI) to install Oracle Label Security, you should shut down the database instance. You should also shut down the corresponding database service if you are using Windows.

In case you haven't shut down the database service, you would be prompted to do so during installation.

To install Oracle Label Security:

  1. Run Oracle Universal Installer from the installation media.

    • UNIX: Use the following command:

      /mnt/cdrom/runInstaller
      
    • Windows: Double click the file, setup.exe on the installation media.

      The Welcome screen appears.

  2. Click Next.

    The Select Installation Method screen appears.

  3. Select Advanced Installation. Click Next.

    The Select Installation Type screen appears.

  4. Select Custom. Click Next.

    The Specify Home Details screen appears.

  5. Ensure that the correct Oracle base and Oracle home directories are selected. Click Next.

    At this point the installer verifies that your system meets the minimum requirements. Next, the Available Product Components screen is displayed.

  6. Select the check box corresponding to Oracle Label Security. This option can be found under Oracle Database 11g, Enterprise Edition Options. Click Next.

    The Summary screen is displayed.

  7. Review your choices and click Install.

    The progress screen is displayed.

  8. The End of Installation screen is displayed. Click Exit.

Registering Oracle Label Security with the Database

After you complete the installation, you need to register Oracle Label Security with the database.

To register Oracle Label Security with the Database:

  1. Start Database Configuration Assistant (DBCA).

    • UNIX: Run the following command:

      $ORACLE_HOME/bin/dbca
      
    • Windows: From the Start menu, click All Programs. Then click Oracle - ORACLE_HOME, then Configuration and Migration Tools, and then Database Configuration Assistant.

    The Welcome screen appears.

  2. Click Next.

    The Operations screen appears.

  3. Select Configure Database Options. Click Next.

    The Database screen appears.

  4. From the list, select the database where you installed Oracle Label Security. Click Next.

    The Database Content screen appears.

  5. Select Oracle Label Security. Click Next.

    The Connection Mode screen appears.

  6. Select either Dedicated Server Mode or Shared Server Mode. Click Finish.

    A dialog box is displayed informing you that the operation will require the database to be restarted.

  7. Click OK.

    A confirmation dialog box is displayed.

  8. Click OK.

    The DBCA progress screen is displayed.

  9. After the operation is complete, you are prompted to perform another operation. Click No to exit DBCA.

Enabling the LBACSYS User Account

The OLS installation process creates a default user account, LBACSYS, which has the privileges to manage OLS administration. By default, LBACSYS is created as a locked account with it's password expired. Your next step is to unlock LBACSYS and create a new password. You also need to grant LBACSYS the SELECT ANY DICTIONARY system privilege. This privilege allows LBACSYS to log in to Enterprise Manager.

To unlock LBACSYS and create a new password:

  1. Log in to Database Control as the SYSTEM user.

  2. Click the Schema tab.

  3. Click Users under Users and privileges.

    The Users page appears

  4. Select LBACSYS. Click Edit.

    The Edit User page appears.

  5. Change the Status to Unlocked.

  6. Enter a password in the Enter Password field. Reenter the password in the Confirm Password field.

  7. Click the System Privileges tab.

  8. Select the SELECT ANY DICTIONARY system privilege.

  9. Click Apply.

4.2 Creating an OLS Policy

This section explores the following topics:

4.2.1 Step 1: Creating the Policy

You begin by defining a policy name, label column, and enforcement options for the policy.

To create a policy with default policy enforcement options:

  1. Log in to Oracle Enterprise Manager Database Control using the LBACSYS account.

  2. Click the Server tab.

  3. Click Oracle Label Security under Security. The Label Security Policies page appears.

  4. Click Create to start creating a new label security policy.

    The Create Label Security Policy page appears.

  5. Define the policy's name, label column, and the default policy enforcement options.

    • Name: Enter a name for the policy, for example, ACCESS_LOCATIONS.

    • Label Column: Enter a name for the label column, for example, 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). You can also use an existing table column of the NUMBER(10) data type as the label column.

    • Hide Label Column: Select to hide the column. When you first create the policy, you may want to disable Hide Label Column during the development phase of the policy. When the policy is satisfactory and ready for use by users, hide the column so that it is transparent to applications.

    • Enabled: Toggle to enable or disable the policy.

    • Enforcement Options: The default policy enforcement options are used when the policy is applied. Ensure that these meet the needs of the application to which you are applying the policy.

      Select from the following options:

      • Apply No Policy Enforcements (NO_CONTROL)

      • Apply Policy Enforcements

        For all queries (READ_CONTROL)

        For Insert operations (INSERT_CONTROL)

        For Update Operations (UPDATE_CONTROL)

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

        Operations that update the label column (LABEL_UPDATE)

        Update and Insert operations so that they are read accessible (CHECK_CONTROL)

  6. Click OK.

    The new policy appears in the Oracle Label Security Policies page.

4.2.2 Step 2: Creating Label Components for the Policy

At this stage, you have created a container for the policy and have set enforcement options for it. Next, you need to create label components for the policy.

To create the label components:

  1. In the Oracle Label Security Policies page, select the policy you just created. Click Edit.

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

  3. Click Add 5 Rows under Levels to add levels for the policy. Enter a Long Name, Short Name, and Numeric Tag for each level that you create. The numeric tag corresponds to the sensitivity of the level. To create more levels, you can click Add 5 Rows again. Use the same steps to create compartments and rows. For compartments and groups, the numeric tags do not correspond to sensitivity.

    At a minimum, you must create one level, such as SECRET. Creating compartments and groups is optional.

    The level numbers indicate the level of sensitivity for their corresponding labels. A greater number implies greater sensitivity. Select a numeric range that can be expanded later on, in case your security policy needs more levels. For example, if you have created levels PUBLIC (7000) and SENSITIVE (8000), and you now want to create an intermediate level called CONFIDENTIAL, then you can assign the numeric value 7500 to this level.

    Compartments identify categories associated with data, providing a finer level of granularity within a level. For example, a single table might have data corresponding to different departments that you might like to separate using compartments. 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.

  4. Click Apply.

4.2.3 Step 3: Creating Data Labels for the Policy

You are now ready to create data labels for the policy. Each data label must have exactly one level associated with it. You can optionally add one or more compartments and groups to the label.

To create a data label, you need to assign a numeric tag to the label. Later on, the tag number will be stored in the security column when you apply the policy to a table. The label tag is not linked to the sensitivity (level) of the label. It is only used to identify the label.

To create data labels for each level:

  1. In the Label Security Policies page, select the policy that needs to have labels linked to levels.

  2. In the Actions box, select Data Labels. Click Go.

    The Data Labels page appears.

  3. Click Add.

    The Create Data Label page appears.

  4. Enter the following information:

    • Numeric Tag: Enter a number that uniquely identifies the label. This number should be unique across all policies.

    • Level: Select a level from the list.

  5. You can optionally select Compartments to add to the label. To add compartments, click Add under Compartments. Select the compartments to be added to the label. Click Select to add the compartments.

  6. You can optionally select Groups to add to the label. To add groups, click Add under Groups. Select the groups to be added to the label. Click Select to add the groups.

  7. Click OK in the Create Data Label page.

    The data label appears in the Data Labels page.

  8. Repeat steps 3 to 7 to create more data labels.

4.2.4 Step 4: Authorizing Users for the Policy

You are now ready to authorize users for the Oracle Label Security policy.

To authorize users for the OLS policy:

  1. In the Label Security Policies page, select the policy that needs authorization.

  2. In the Actions box, select Authorization. Click Go.

    The Create User page appears.

  3. Add users as follows:

    • Under Database Users, click Add. In the Search and Select window, select users that you want and then click Select.

    • Under Non Database Users, click Add 5 Rows, and then add the user names of the non-database users that you want to add. Most application users are considered non-database users. A non-database 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 non-database user when the application connects to the database. In this case, the application needs to call an Oracle Label Security function to assume the label authorizations of the specified user who is not a real database user.

  4. In the Create User page, select the user that you want to authorize. Click Next. If you have multiple users that need the same authorizations, then select all users who need the same authorizations. Click Next.

    The Privileges step appears.

  5. Next, you can assign privileges to the user you selected in the preceding step. Privileges allow a database user to bypass certain controls enforced by the policy. Select the privileges you want to grant. Click Next.

    If you do not wish to assign any privilege to the user, click Next without selecting any privileges.

    The Labels, Compartments, and Groups step appears.

  6. Next, you need to create the user label for the user. Under Levels, use the flashlight icon to select data to enter for the following fields:

    • Maximum Level: Enter the highest level for read and write access for this user.

    • Minimum Level: Enter the lowest level for write access.

    • Default Level: Enter the default level when the user logs in.

      This value is equal to or greater than the minimum level and equal to or less than the maximum level.

    • Row Level: Enter the level given to the row when user writes to the table.

  7. Click Add under Compartments, to add compartments to the user label. Select the compartments to add. Click Select.

  8. For each compartment that you add, you can select the following properties:

    • Write: Allows the user to write to data that has the compartment as part of it's label

    • Default: Adds the compartment to the user's default session label

    • Row: Adds the compartment to the data label when the user writes to the table

  9. Click Add under Groups, to add groups to the user label. Select the groups and click Select.

  10. For each group that you add, you can select the following properties:

    • Write: Allows the user to write to data that has the group as part of it's label

    • Default: Adds the group to the user's default session label

    • Row: Adds the group to the data label when the user writes to the table

  11. Click Next.

    The Audit step appears.

  12. Next, you can choose to set the policy audit options for the selected user. You can set audit options for the following operations:

    • Policy Applied:

      Audit On Success By audits successful application of the policy to a table or schema. Select ACCESS to audit by access or SESSION to audit by session.

      Audit On Failure By audits failed application of the policy to a table or schema. Select ACCESS to audit by access or SESSION to audit by session.

    • Policy Removed:

      Audit On Success By audits successful removal of the policy from a table or schema. Select ACCESS to audit by access or SESSION to audit by session.

      Audit On Failure By audits failed removal of the policy from a table or schema. Select ACCESS to audit by access or SESSION to audit by session.

    • Labels And Privileges Set:

      Audit On Success By audits successful setting of user authorizations and privileges. Select ACCESS to audit by access or SESSION to audit by session.

      Audit On Failure By audits failed setting of user authorizations and privileges. Select ACCESS to audit by access or SESSION to audit by session.

    • All Policy Specific Privileges:

      Audit On Success By audits successful use of policy privileges. Select ACCESS to audit by access or SESSION to audit by session.

      Audit On Failure By audits failed use of policy privileges. Select ACCESS to audit by access or SESSION to audit by session.

  13. Click Next.

  14. You can review the policy authorization settings. Click Finish to create the policy authorization. Alternatively, you can click Back to modify the authorization settings.

4.2.5 Step 5: Applying the Policy to a Database Table

Next, apply the OLS policy to a database table.

To apply the policy to a database table:

  1. In the Label Security Policies page, select the policy that needs to be applied to a table.

  2. Select Apply from the Actions box. Click Go.

    The Apply page appears.

  3. Select the Tables tab to apply the policy to a table.

    Note:

    Select the Schemas tab if you are applying the policy to a schema.The process is same as applying the policy to a table.
  4. Click Create.

    The Add Table page appears.

  5. Next to the Table box, click the flashlight icon.

  6. In the Search and Select window, enter the following information under Search:

    • Schema: Enter the name of the schema in which the table appears. Leaving this field empty displays tables in all schemas.

    • Name: Optionally, enter the name of the table. Leaving this box empty displays all the tables within the schema.

    To narrow the search by using wildcards, use the percent (%) sign. For example, enter O% to search for all tables beginning with the letter O.

  7. Select the table and click Select.

    The Add Table page appears.

  8. Enter the following information:

    • Policy Enforcement Options: Select enforcement options as needed. These options will apply to the table on top of the enforcement options that you selected when you created the policy in Step 1: Creating the Policy.

      To make no change from those enforcement options, that is, to use the same enforcement options created earlier, select Use Default Policy Enforcement. To add more enforcement options, select from the other options listed.

    • Labeling Function: Optionally, specify a labeling function to automatically compute the label to be associated with a new or updated row. That function is always invoked thereafter to provide the data labels written under that policy, because active labeling functions take precedence over any alternative means of supplying a label.

    • Predicate: Optionally, specify an additional predicate to combine (using AND or OR) with the label-based predicate for READ_CONTROL.

  9. Click OK.

4.2.6 Step 6: Adding Policy Labels to Table Rows

After you have applied a policy to a table, you need to add data labels to the rows in the table. These labels are stored in the policy label column created in the table. The user updating the table needs to have the FULL security privilege for the policy. This user is normally the owner of the table.

To grant the table owner FULL privilege for the OLS Policy:

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

  2. Select the ACCESS_LOCATIONS policy.

  3. Select Authorization from the Actions box. Click Go.

    The Authorization page appears.

  4. Click Add.

    The Create User page appears.

  5. Under Database Users, click Add.

    The Search and Select window appears.

  6. Select the check box corresponding to the user that owns the table. Click Select.

    The Create User page lists the user that was added.

  7. Click Next.

    The Privileges step appears.

  8. Select the following privilege:

    Bypass all Label Security checks (FULL)

    Click Next.

    The Labels, Compartments, and Groups step appears.

  9. Click Next.

    The Audit step appears.

  10. Click Next.

    The Review step appears.

  11. Click Finish.

To add data labels to the table:

  • In SQL*Plus, enter an UPDATE statement using the following syntax:

    UPDATE LOCATIONS
    SET OLS_COLUMN = CHAR_TO_LABEL('OLS_POLICY','DATA_LABEL')
    WHERE UPPER(TABLE_COLUMN) IN (COLUMN_DATA);
    

    For example, suppose LABCSYS has created a policy called ACCESS_LOCATIONS and wants to add the label SENS to the cities Beijing, Tokyo, and Singapore in the HR.LOCATIONS table. The policy label column is called ROW_LABEL. The UPDATE statement is as follows:

    UPDATE LOCATIONS
    SET ROW_LABEL = CHAR_TO_LABEL('ACCESS_LOCATIONS','SENS')
    WHERE UPPER(city) IN ('BEIJING', 'TOKYO', 'SINGAPORE');
    

If you want to check that your labels really made it into the table, run the following SELECT statement:

SELECT LABEL_TO_CHAR (ROW_LABEL) FROM LOCATIONS;

4.3 Creating a Sample OLS Policy

This example demonstrates the general concepts of using Oracle Label Security. In it, you will apply security labels to the HR.LOCATIONS table. Three users, SKING, KPARTNERS, and LDORAN will have access to specific rows within this table, based on the cities listed in the LOCATIONS table.

The HR.LOCATIONS is described as follows:

SQL> DESCRIBE locations

Name                                      Null?    Type
----------------------------------------- -------- -------------
LOCATION_ID                               NOT NULL NUMBER(4)
STREET_ADDRESS                                     VARCHAR2(40)
POSTAL_CODE                                        VARCHAR2(12)
CITY                                      NOT NULL VARCHAR2(30)
STATE_PROVINCE                                     VARCHAR2(25)
COUNTRY_ID                                         CHAR(2)

You will apply the following labels:

Label Privileges
CONFIDENTIAL Read access to the cities Munich, Oxford, and Rome
SENSITIVE Read access to the cities Beijing, Tokyo, and Singapore
PUBLIC Read access to all other cities listed in HR.LOCATIONS

You will follow these steps to complete this example:

4.3.1 Step 1: Creating Users for the Oracle Label Security Example

You are ready to create a role and three users, and then grant these users the role.

Creating the EMP_ROLE Role

The EMP_ROLE role provides the necessary privileges for the three users that you will create.

To create the role EMP_ROLE:

  1. Log in to Database Control as the SYSTEM user.

  2. Click the Schema tab.

  3. Under Users and Privileges, click Roles.

    The Roles page appears.

  4. Click Create.

    The Create Role page appears.

  5. Enter EMP_ROLE in the Name field. Leave Authentication set to None.

  6. Click the Object Privileges tab.

  7. Select Table from the Select Object Type box. Click Add.

    The Add Table Object Privileges page appears.

  8. Under Select Table Objects, enter HR.LOCATIONS to select the LOCATIONS table in the HR schema. Under Available Privileges, move SELECT to the Selected Privileges list.

  9. Click OK.

  10. Click OK in the Create Role page.

Creating the Users SKING, KPARTNERS, and LDORAN

The three users you create will have different levels of access to the HR.LOCATIONS table, depending on their position. Steven King (SKING) is the Sales president, so he has full read access to the HR.LOCATIONS table. Karen Partners (KPARTNERS) is a sales manager who has less access, and Louise Doran (LDORAN) is a sales representative who has the least access.

To create the users SKING, KPARTNERS, and LDORAN:

  1. Log in to Database Control as SYSTEM.

  2. Click the Schema tab.

  3. Under Users and Privileges, click Users.

    The Users page appears.

  4. Click Create.

    The Create User page appears.

  5. Enter the following information:

    • Name: SKING

    • Profile: DEFAULT

    • Authentication: Password

    • Enter Password and Confirm Password: there4all

    • Default Tablespace: USERS

    • Temporary Tablespace: TEMP

    • Roles: Select the Roles tab and grant the EMP_ROLE role to SKING. Select the Default check box.

    • System Privileges: Select the System Privileges tab and grant the CREATE SESSION system privilege.

  6. Click OK.

  7. In the Users page, select SKING, select Create Like in the Actions box, and then click Go.

    The Create User page appears.

  8. Create accounts for KPARTNERS and LDORAN, with eager2please as the password for KPARTNERS and ready2go as the password for LDORAN.

    You only need to create their names and passwords. You do not need to grant roles or system privileges to them. Their roles and system privileges, defined in SKING's account, are automatically created.

At this stage, you have created three users who have identical privileges.

4.3.2 Step 2: Creating the ACCESS_LOCATIONS Policy

The policy is the container for the label components that you will create later.

To create the ACCESS_LOCATIONS policy:

  1. Log in to Database Control as the LBACSYS user.

  2. Click the Server tab.

  3. Click Oracle Label Security under Security.

    The Label Security Policies page appears.

  4. Click Create.

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

    • Name: ACCESS_LOCATIONS

    • Label Column: OLS_COLUMN

    • Hide Label Column: Deselect this check box so that the label column will not be hidden.

      Usually, it should be 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, you should hide this column for greater security.

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

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

      For all queries (READ_CONTROL)

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

  6. Click OK.

    The ACCESS_LOCATIONS policy appears in the Label Security Policies page.

    Description of ols_new_policy.gif follows
    Description of the illustration ols_new_policy.gif

4.3.3 Step 3: Defining the ACCESS_LOCATIONS Policy-Level Components

You can now define the label components 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. Click Edit.

    The Edit Label Security Policy page appears.

  2. Select the Label Components tab.

  3. Under Levels, click Add 5 Rows, and add the following levels:

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

  4. Click Apply.

4.3.4 Step 4: Creating the ACCESS_LOCATIONS Policy Data Labels

In this step, you create data labels corresponding to the levels that you created in the last step.

To create the data labels:

  1. Return to the Label Security Policies page by clicking the Label Security Policies link.

  2. Select the ACCESS_LOCATIONS policy.

  3. Select Data Labels from the Actions list. Click Go.

    The Data Labels page appears.

  4. Click Add.

    The Create Data Label page appears.

  5. Enter the following information:

  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 level. For the numeric tag, enter 2000.

  8. Click OK.

  9. Click Add again, and then create a data label for the SENS level. For the numeric tag, enter 3000.

  10. Click OK.

4.3.5 Step 5: Creating the ACCESS_LOCATIONS Policy User Authorizations

Next, you are ready to create user authorizations 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 ACCESS_LOCATIONS policy.

  3. Select Authorization from the Actions box. Click Go.

    The Authorization page appears.

  4. Click Add.

    The Create User page appears.

  5. Under Database Users, click Add.

    The Search and Select window appears.

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

    The Create User page lists user SKING.

    Description of ols_auth_user.gif follows
    Description of the illustration ols_auth_user.gif

  7. Click Next.

    The Privileges step appears.

  8. Click Next.

    The Labels, Compartments and Groups step appears.

  9. Set the following levels for the user SKING:

    • Maximum Level: SENS (for SENSITIVE)

    • Minimum Level: CONF (for CONFIDENTIAL)

    • Default Level: SENS

    • Row Level: SENS

    This allows SKING to read CONFIDENTIAL and SENSITIVE data.

  10. Click Next.

    The Audit step appears.

  11. Ensure that all of the audit operations are set to None, and then click Next.

    The Review step appears.

    Description of ols_auth.gif follows
    Description of the illustration ols_auth.gif

  12. The Review step lists all the authorization settings you have selected. Ensure that the settings are correct, and then click Finish.

  13. Repeat these steps to create the following authorizations for user KPARTNERS, so that she can read confidential and public data in HR.LOCATIONS.

    • Labels, Compartments And Groups: Set all 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:

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

    • Audit: Set all to None.

4.3.6 Step 6: Applying the ACCESS_LOCATIONS Policy to the HR.LOCATIONS Table

Next, you are ready to apply the policy to the HR.LOCATIONS table.

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 ACCESS_LOCATIONS policy.

  3. Select Apply from the Actions box. Click Go.

    The Apply page appears.

  4. Ensure that the Tables tab is selected. Click Create.

    The Add Table page appears.

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

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

  7. Ensure that the Enabled check box is selected.

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

    The default policy enforcement options for ACCESS_LOCATIONS are:

    • 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 ols_apply.gif follows
    Description of the illustration ols_apply.gif

4.3.7 Step 7: Adding Policy Labels to Table Data

After you have applied the ACCESS_LOCATIONS policy to the HR.LOCATIONS table, you need to label the rows in the table. HR is the owner of the LOCATIONS table. HR needs to have the FULL security privilege for the ACCESS_LOCATIONS policy, in order to successfully update the LOCATIONS table with the policy labels.

Granting FULL Privilege to the Owner of the Application Table

The label security administrative user, LBACSYS can grant HR the necessary privilege.

To grant HR FULL Privilege for the OLS Policy:

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

  2. Select the ACCESS_LOCATIONS policy.

  3. Select Authorization from the Actions box. Click Go.

    The Authorization page appears.

  4. Click Add.

    The Create User page appears.

  5. Under Database Users, click Add.

    The Search and Select window appears.

  6. Select the check box for user HR and then click Select.

    The Create User page lists user HR.

  7. Click Next.

    The Privileges step appears.

  8. Select the following privilege:

    Bypass all Label Security checks (FULL)

    Click Next. The Labels, Compartments, and Groups step appears.

  9. Click Next. The Audit step appears.

  10. Click Next. The Review step appears.

  11. Click Finish.

Updating the OLS_COLUMN Table in HR.LOCATIONS

The user HR can now update the HR.LOCATIONS table with the appropriate labels.

To update the OLS_COLUMN table in HR.LOCATIONS:

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

    CONNECT HR
    Enter password: 
    

    If you receive an error message saying that HR is locked, you can unlock the account and reset its password by entering the following statements:

    CONNECT system
    Enter password: sys_password
    ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password
    CONNECT hr
    Enter password:
    
  2. Enter the following UPDATE statement to apply the SENS label to the cities Beijing, Tokyo, and Singapore:

    UPDATE LOCATIONS
    SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','SENS')
    WHERE UPPER(city) IN ('BEIJING', 'TOKYO', 'SINGAPORE');
    
  3. Enter the following UPDATE statement to apply the CONF label to the cities Munich, Oxford, and Rome:

    UPDATE LOCATIONS
    SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','CONF')
    WHERE UPPER(city) IN ('MUNICH', 'OXFORD', 'ROME');
    
  4. Enter the following UPDATE statement to apply the PUB label to the remaining cities:

    UPDATE LOCATIONS
    SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','PUB')
    WHERE ols_column IS NULL;
    
  5. To check that the columns were updated, enter the following query:

    SELECT LABEL_TO_CHAR (OLS_COLUMN) FROM LOCATIONS;
    

    Note:

    Using the label column name (OLS_COLUMN) explicitly in the preceding query enables you to see the label column even if it was hidden.

    If the label column is hidden, and you do not specify the label column name explicitly, then the label column is not displayed in the query results. For example, using the SELECT * FROM LOCATIONS query will not show the label column if it is hidden. This feature allows the label column to remain transparent to applications. An application that was designed before the label column was added will not know about the label column and will never see it.

4.3.8 Step 8: Testing the ACCESS_LOCATIONS Policy

The ACCESS_LOCATIONS policy is complete and ready to be tested. You can test it by logging in to SQL*Plus as each of the three users and performing a SELECT on the HR.LOCATIONS table.

To test the ACCESS_LOCATIONS policy:

  1. In SQL*Plus, connect as user SKING, whose password is there4all.

    CONNNECT SKING
    Enter password: there4all
    
  2. Enter the following statement:

    COL city HEADING City FORMAT a25
    COL country_id HEADING Country FORMAT a11
    COL Label format a10
    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 rows that are labeled PUB, CONF, and SENS.

  3. Repeat these steps for users KPARTNERS and LDORAN.

    The password for KPARTNERS is eager2please and the password for LDORAN is ready2go.

    KPARTNERS can access rows labeled CONF and PUB, and LDORAN is able to access the rows labeled PUB.

4.3.9 Step 9: Removing the Components for This Example (Optional)

If you want, remove the components that you created for this example.

To remove the components for this example:

  1. In Database Control, connect as user SYSTEM.

  2. Click the Schema tab.

  3. Click Users under Users and Privileges.

  4. Select user KPARTNERS and then click Delete.

  5. In the Confirmation page, click Yes.

  6. Repeat Step 4 and Step 5 for users LDORAN and SKING.

  7. Log out of Database Control, and then log back in as the LABCSYS user.

  8. Click the Server tab.

  9. Click Oracle Label Security under Security.

  10. In the Label Security Policies page, in the Name field, enter ACCESS% and then click Go.

    Description of ols_delete.gif follows
    Description of the illustration ols_delete.gif

  11. Ensure that ACCESS_LOCATIONS is selected, and then click Delete.

    The Confirmation page appears.

  12. If you select Drop Column, the OLS_COLUMN policy column is also dropped from the HR.LOCATIONS table. Click Yes to delete the policy.