Step 8: Add the ACCESS_LOCATIONS Labels to the HR.LOCATIONS Data

After you have applied the ACCESS_LOCATIONS policy to the HR.LOCATIONS table, you must apply the labels of the policy to the OLS_COLUMN in LOCATIONS. 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.

Granting HR FULL Policy Privilege for the HR.LOCATIONS Table
Updating the OLS_COLUMN Table in HR.LOCATIONS

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:

Return to the Label Security policies page by selecting the Label Security Policies link.
Select the selection button for the ACCESS_LOCATIONS policy.
Select Authorization from the Actions list, and then click Go.

The Authorization page appears.
Click Add Users.

The Add Users page appears.
Under Database Users, click Add.

The Search and Select window appears.
Select the box for user HR, and then click Select.

The Create User page lists user HR.
Click Next to display the Add Users: Levels, Compartments and Groups page, and then click Next again to display the Privileges page.
Select the Bypass all Label Security checks (FULL) privilege, and then click Next.

The Audit page appears.
Click Next.

The Review page appears.
Click Finish.

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

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:

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.

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');

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');

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;

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
 
LABEL_TO_CHAR(OLS_COLUMN)
-----------------------------------------------------------------------------
PUB
PUB
SENS
PUB
CONF
PUB
CONF
PUB
PUB
PUB
PUB
 
LABEL_TO_CHAR(OLS_COLUMN)
-----------------------------------------------------------------------------
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.

Step 8: Add the ACCESS_LOCATIONS Labels to the HR.LOCATIONS Data

Related Topics