1. 2 Day + Security Guide
  2. Enforcing Row-Level Security with Oracle Label Security

8 Enforcing Row-Level Security with Oracle Label Security

Oracle Label Security enables you to enforce row-level security.

Topics:

8.1 About Oracle Label Security

Oracle Label Security (OLS) provides row-level security for your database tables.

You can accomplish this by assigning one or more security labels that define the level of security you want for the data rows of the table.

This feature secures your database tables at the row level, and assigns these rows different levels of security based on security labels. You then create a security authorization for users based on the OLS labels.

For example, rows that contain highly sensitive data can be assigned a label entitled HIGHLY SENSITIVE; rows that are less sensitive can be labeled as SENSITIVE, and so on. Rows that all users can have access to can be labeled PUBLIC. You can create as many labels as you need, to fit your site's security requirements. In a multitenant environment, the labels apply to the local pluggable database (PDB) and the session labels apply to local users.

After you create and assign the labels, you can use Oracle Label Security to assign specific users authorization for specific rows, based on these labels. Afterward, Oracle Label Security automatically compares the label of the data row with the security clearance of the user to determine whether the user is allowed access to the data in the row.

An Oracle Label Security policy has the following components:

  • Labels. Labels for data and users, along with authorizations for users and program units, govern access to specified protected objects. Labels are composed of the following:

    • Levels. Levels indicate the type of sensitivity that you want to assign to the row, for example, SENSITIVE or HIGHLY SENSITIVE.

    • Compartments. (Optional) Data can have the same level (Public, Confidential and Secret), but can belong to different projects inside a company, for example ACME Merger and IT Security. Compartments represent the projects in this example, that help define more precise access controls. They are most often used in government environments.

    • Groups. (Optional) Groups identify organizations owning or accessing the data, for example, UK, US, Asia, Europe. Groups are used both in commercial and government environments, and frequently used in place of compartments due to their flexibility.

  • Policy. A policy is a name associated with these labels, rules, and authorizations.

You can create Oracle Label Security labels and policies in Enterprise Manager, or you can create them using the SA_SYSDBA, SA_COMPONENTS, and SA_LABEL_ADMIN PL/SQL packages. This guide explains how to create Oracle Label Security labels and policies by using Enterprise Manager.

For example, assume that a user has the SELECT privilege on an application table. As illustrated in the following figure, when the user runs a SELECT statement, Oracle Label Security evaluates each row selected to determine whether the user can access it. The decision is based on the privileges and access labels assigned to the user by the security administrator. You can also configure Oracle Label Security to perform security checks on UPDATE, DELETE, and INSERT statements.

Description of olsag008.eps follows
Description of the illustration olsag008.eps

See Also:

Oracle Label Security Administrator’s Guide for detailed information about Oracle Label Security

8.2 Virtual Private Database, Oracle Label Security, and Data Redaction Differences

Oracle Virtual Private Database, Oracle Label Security, and Oracle Data Redaction restrict the data that different users can see in database tables.

But which of the features should you use? Virtual Private Database is effective when there is existing data you can use to determine the access requirements. For example, you can configure a sales representative to see only the rows and columns in a customer order entry table for orders he or she handles. Oracle Label Security is useful if you have no natural data (such as user accounts or employee IDs) that can be used to indicate a table's access requirements. To determine this type of user access, you assign different levels of sensitivity to the table rows. Oracle Data Redaction enables you to select from three differing (redaction) styles, and it applies the redaction when the user accesses the data, not directly in the database table.

In some cases, Oracle Virtual Private Database and Oracle Label Security can complement each other. The following Oracle Technology Network hands-on tutorial demonstrates how a Virtual Private Database policy can compare an Oracle Label Security user clearance with a minimum clearance. When the user clearance dominates the threshold, the salary column is not hidden.

http://www.oracle.com/technetwork/database/security/ols-cs1-099558.html

Table 8-1 compares the features of Oracle Virtual Private Database, Oracle Label Security, and Oracle Data Redaction.

Table 8-1 Comparing Virtual Private Database, Label Security, and Data Redaction

Feature VPD OLS Data Redaction

Provides full masking, partial masking, and random masking

No

No

Yes

Redacts data in real-time, as the user is accessing it

No

No

Yes

Provides row-level security

Yes

Yes

No

Provides column-level security (column masking)

Yes

No

Yes

Binds a user-defined PL/SQL package to a table, view, or synonym

Yes

NoFoot 1

No

Modifies SQL by dynamically adding a WHERE clause returned from the PL/SQL procedures

Yes

No

No

Restricts database operations by privileged usersFoot 2

No

No

No

Controls access to a set of rows based on the sensitivity label of the row and the security level of the user

No

Yes

No

Adds a column (optionally hidden) designed to store sensitivity labels for rows in the protected table

No

Yes

No

Provides a user account to manage its administration

NoFoot 4

YesFoot 5

No

Provides pre-defined PL/SQL packages for row-level security

No

Yes

No

Is provided in the default installation of Oracle Database

Yes

Yes

Yes

Is provided as an additional option to Oracle Database and must be licensed

No

No

Yes

Footnote 1

Oracle Label Security uses predefined PL/SQL packages, not user-created packages, to attach security policies to tables.

Footnote 2

If you want to restrict privileged user access, consider using Oracle Database Vault.

Footnote 3

Usually, this column is hidden to achieve transparency and not break applications that are not designed to show an additional column.

Footnote 4

Oracle Virtual Private Database does not provide a user account, but you can create a user account that is solely responsible for managing Virtual Private Database policies.

Footnote 5

The LBACSYS account manages Oracle Label Security policies. This provides an additional layer of security in that one specific user account is responsible for these policies, which reduces the risk of another user tampering with the policies.

See Also:

8.3 Guidelines for Planning an Oracle Label Security Policy

Before you create an Oracle Label Security policy, determine how to apply the labels to the application schema.

To determine where and how to apply Oracle Label Security policies for application data, follow these guidelines:

  1. Analyze the application schema. Identify the tables that require an Oracle Label Security policy. In most cases, only a small number of the application tables will require an Oracle Label Security policy. For example, tables that store lookup values or constants usually do not need to be protected with a security policy. However, tables that contain sensitive data, such as patient medical histories or employee salaries, do.

  2. Analyze the use of data levels. After you identify the candidate tables, evaluate the data in the tables to determine the level of security for the table. Someone who has broad familiarity with business operations can provide valuable assistance with this stage of the analysis.

    Data levels refer to the sensitivity of the data. PUBLIC, SENSITIVE, and HIGHLY SENSITIVE are examples of data levels. You should also consider future sensitivities. Doing so creates a robust set of label definitions.

    Remember that if a data record is assigned a sensitivity label whose level component is lower than the clearance of the user, then a user attempting to read the record is granted access to that row.

  3. Analyze the use of data compartments. Data compartments are used primarily in government environments. If your application is a commercial application, in most cases, you will not create data compartments.

  4. Analyze the data groups. Data groups and data compartments are typically used to control access to data by organization, region, or data ownership. For example, if the application is a sales application, access to the sales data can be controlled by country or region.

    When a data record is assigned a sensitivity label with compartments and groups, a user attempting to read the record must have a user clearance that contains a level that is equal to or greater than the level of the data label, all of its compartments, and at least one of the groups in the sensitivity label. Because groups are hierarchical, a user could have the parent of one of the groups in the sensitivity label assigned to the data label and still be able to access that record.

  5. Analyze the user population. Separate the users into one or more designated user types. For example, a user might be designated as a typical user, privileged user, or administrative user. After you create these categories of users, compare the categories with the data levels you created in Step 2. They must correspond correctly for each table identified during the schema analysis you performed in Step 1. Then, compare the organizational structure of the user population with the data groups that you identified in Step 4.

  6. Examine the highly privileged and administrative users to determine which Oracle Label Security authorizations should be assigned to the user. Oracle Label Security has several special authorizations that can be assigned to users. In general, typical users do not require any special authorizations. See Oracle Label Security Administrator’s Guide for a complete list of these authorizations.

  7. Review and document the data you gathered. This step is crucial for continuity across the enterprise, and the resulting document should become part of the enterprise security policy. For example, this document should contain a list of protected application tables and corresponding justifications.

8.4 Tutorial: Creating Levels of Access to Table Data Based on the User

You can create different levels of Oracle Label Security access to table data based on who the user is.

Topics:

8.4.1 About Creating Levels of Access to Table Data Based on the User

This tutorial demonstrates the general concepts of using Oracle Label Security.

In this tutorial, you will apply security labels to the HR.LOCATIONS table. Three users, sking, kpartner, and ldoran will have access to specific rows within this table, based on the cities listed in the LOCATIONS table.

With Oracle Label Security, you restrict user access to data by focusing on row data, and designing different levels of access based on the sensitivity of your data. If you must restrict user access by focusing on user privileges, or some other method such as the job title that the user in your organization has, you can create a PL/SQL function or procedure to use with a Virtual Private Database policy. See Restricting Access with Oracle Virtual Private Database, for more information.

The schema for HR.LOCATIONS is as follows: 

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 Roma

SENSITIVE

Read access to the cities Beijing, Tokyo, and Singapore

PUBLIC

Read access to all other cities listed in HR.LOCATIONS

8.4.2 Step 1: Enable Oracle Label Security

In a default Oracle Database installation, Oracle Label Security is installed but you must manually enable it.

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

8.4.3 Step 2: Enable the LBACSYS Account

After you have enabled Oracle Label Security, you must enable the default Oracle Label Security account, which is called LBACSYS.

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 Schema menu, select 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:

  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.

8.4.4 Step 3: Create a Role and Three Users for the Oracle Label Security Tutorial

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

Topics:

  • Creating a Role
    The emp_role role provides the necessary privileges for the three users you will create.
  • Creating the Oracle Label Security Users
    The three users that you must create will have different levels of access to the HR.LOCATIONS table, depending on their position.
8.4.4.1 Creating a Role

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

To create the role emp_role:

  1. Access the Database home page.

    See Oracle Database 2 Day DBA for more information. Log in as user SYSTEM with the NORMAL privilege.

  2. From the Security menu, select Roles.

  3. In the Roles page, click Create.

  4. In the Create Role page, in the Name field, enter EMP_ROLE and leave Authentication set to None.

  5. Select the Object Privileges subpage.

  6. From the Select Object Type list, select Table, and then click Add.

    The Add Table Object Privileges page appears.

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

  8. Click OK to return to the Create Role page, and then click OK to return to the Roles page.

8.4.4.2 Creating the Oracle Label Security Users

The three users that you must create will have different levels of access to the HR.LOCATIONS table, depending on their position.

Steven King (sking) is the advertising president, so he has full read access to the HR.LOCATIONS table. Karen Partners (kpartner) 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:

  1. From the Schema menu, select Users.

    The Users page appears.

  2. Click Create.

  3. In the Create User page, enter the following information:

    • Name: SKING

    • Profile: DEFAULT

    • Authentication: Password

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

    • Default Tablespace: USERS

    • Temporary Tablespace: TEMP

    • Status: Set to Unlocked.

    • Roles: Select the Roles subpage, and then grant the emp_role role to sking by selecting Edit List. From the Available Roles list, select emp_role, and then click Move to move it to the Selected Roles list. Click OK. In the Create User page, ensure that the Default box is selected for both the CONNECT and emp_role roles.

    • System Privileges: Select the System Privileges subpage and then click Edit List to grant the CREATE SESSION privilege. Do not grant sking the ADMIN OPTION option.

  4. Click OK to return to the Create User page, and then from there, click OK to return to the Users page.

  5. In the Users page, select SKING, set Actions to Create Like, and then click Go.

  6. In the Create User page, create accounts for kpartner and ldoran.

    Create their names and passwords. (See Requirements for Creating Passwords.) You do not need to grant roles or system privileges to them. Their roles and system privileges, defined in the sking account, are automatically created.

At this stage, you have created three users who have identical privileges. All of these users have the SELECT privilege on the HR.LOCATIONS table, through the EMP_ROLE role.

8.4.5 Step 4: Create the ACCESS_LOCATIONS Oracle Label Security Policy

After you create the user accounts, you are ready to create the ACCESS_LOCATIONS policy.

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 Security menu, select 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 ols_new_policy.gif follows
    Description of the illustration ols_new_policy.gif

8.4.6 Step 5: Define the ACCESS_LOCATIONS Policy-Level Components

Next, you are ready to create label components for the policy.

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.)

    Table 8-2 Values for Oracle Label Security Levels

    Long Name Short Name Numeric Tag

    SENSITIVE

    SENS

    3000

    CONFIDENTIAL

    CONF

    2000

    PUBLIC

    PUB

    1000

  4. Click Apply.

8.4.7 Step 6: Create the ACCESS_LOCATIONS Policy Data Labels

In this step, you create data labels for the ACCESS_LOCATION policy.

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:

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

8.4.8 Step 7: Create 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 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 ols_auth_user.gif follows
    Description of the illustration ols_auth_user.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 ols_auth.gif follows
    Description of the illustration ols_auth.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.

8.4.9 Step 8: Apply 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 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 ols_apply.gif follows
    Description of the illustration ols_apply.gif

8.4.10 Step 9: Add the ACCESS_LOCATIONS Labels to the HR.LOCATIONS Data

You must apply the labels of the policy to the OLS_COLUMN in LOCATIONS of the HR.LOCATIONS table.

Topics:

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

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

To grant HR FULL access to the ACCESS_LOCATIONS 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. Select Authorization from the Actions list, and then click Go.

  4. In the Authorization page, click Add Users.

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

  6. In the Search and Select window, select the box for user HR, and then click Select.

    The Add User page lists user HR.

  7. Click Next to display the Privileges page.

  8. Select the Bypass all Label Security checks (FULL) privilege, and then click Next.

  9. Click Next to display the Levels, Compartments and Groups page.

  10. Click Next.

  11. In the Audit page, click Next to display the Review page.

  12. Click Finish.

    At this stage, HR is listed in the Authorization page with the other users.

    Description of ols_hr_added.gif follows
    Description of the illustration ols_hr_added.gif

  13. Do not exit Enterprise Manager.

8.4.10.2 Updating the OLS_COLUMN Table in HR.LOCATIONS

The user HR now can update the OLS_COLUMN column in the HR.LOCATIONS table to include data labels that will be assigned to specific rows in the table, based on the cities listed in the CITY column.

To update the OLS_COLUMN table in HR.LOCATIONS:

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

    CONNECT HR
Enter password: password

    If you cannot log in as HR because this account locked and expired, log in as SYSTEM and then enter the following statement. Replace password with an appropriate password for the HR account. For greater security, do not reuse the same password that was used in previous releases of Oracle Database. See Requirements for Creating Passwords. 

    ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY password

    After you complete this ALTER USER statement, try logging in as user HR again.

  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 Roma: 

    UPDATE LOCATIONS
SET ols_column = CHAR_TO_LABEL('ACCESS_LOCATIONS','CONF')
WHERE UPPER(city) IN ('MUNICH', 'OXFORD', 'ROMA');

  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 statement: 

    SELECT LABEL_TO_CHAR (OLS_COLUMN) FROM LOCATIONS;

    The following output should appear:

    LABEL_TO_CHAR(OLS_COLUMN)
-----------------------------------------------------------------------------
CONF
PUB
SENS
PUB
PUB
PUB
PUB
PUB
PUB
PUB
SENS
PUB
PUB
SENS
PUB
CONF
PUB
CONF
PUB
PUB
PUB
PUB
PUB
 
23 rows selected.

    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 does not show the label column if it is hidden. This feature enables the label column to remain transparent to applications. An application that was designed before the label column was added does not know about the label column and will never see it.

8.4.11 Step 10: Test the ACCESS_LOCATIONS Policy

You can test the ACCESS_LOCATIONS policy by having the three users and perform a SELECT on the HR.LOCATIONS table.

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.

8.4.12 Step 11: Optionally, Remove the Components for This Tutorial

You can remove the components that you created for this tutorial if you no longer need them.

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 Schema menu, select 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 Security menu, select 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