6 Securing Data

6.2.4.1 Step 1: Configure the Wallet Location

You designate the directory location for the wallet in the sqlnet.ora file. You perform this step once.

To configure the wallet location:

1. Create a backup copy of the sqlnet.ora file, which by default is located in the $ORACLE_HOME/network/admin directory.

2. Create a directory in the $ORACLE_HOME directory in which to store the wallet.

   For example, create a directory called ORA_WALLETS in the C:\oracle\product\11.1.0\db_1 directory.

3. At the end of the sqlnet.ora file, add code similar to the following, where ORA_WALLETS is the name of the directory where you plan to store the wallet:

   ENCRYPTION_WALLET_LOCATION=
    (SOURCE=
     (METHOD=file)
      (METHOD_DATA=
       (DIRECTORY=C:\oracle\product\11.1.0\db_1\ORA_WALLETS)))
   

4. Save and close the sqlnet.ora file.

5. Start SQL*Plus and then log on as SYS, connecting AS SYSOPER.

   SQLPLUS "SYS/AS SYSOPER"
   Enter password: password
   

   SQL*Plus starts, connects to the default database, and then displays a SQL> prompt.

   For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.

6. Enter the following SQL statements to shut down and then restart the database:

   SHUTDOWN IMMEDIATE
   STARTUP
   

6.2.4.2 Step 2: Create the Wallet

To create the wallet, use the ALTER SYSTEM SQL statement. By default, the Oracle wallet stores a history of retired master keys, which enables you to change them and still be able to decrypt data that was encrypted under an old master key. A case-sensitive wallet password that might be unknown to the database administrator provides separation of duty: The database administrator might be able to restart the database, but the wallet is closed and must be manually opened by a security administrator who knows the wallet password.

To create the wallet:

1. In SQL*Plus, connect as a user with administrative privileges, such as SYSTEM, or as a security administrator.

   For example:

   CONNECT SYSTEM
   Enter password: password
   

2. Enter the following ALTER SYSTEM statement, where password is the password you want to assign to the encryption key:

   ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY "password";
   

   Enclose the password in double quotation marks. As with other passwords that you create in Oracle Database, the password will not appear in clear text or in any dynamic views or logs.

   This statement generates the wallet with a new encryption key and sets it as the current transparent data encryption master key. If you plan to use public key infrastructure (PKI) to configure the master encryption key, then specify a certificate ID, which is an optional string that contains the unique identifier of a certificate stored in the Oracle wallet. Use the following syntax:

   ALTER SYSTEM SET ENCRYPTION KEY certificate_ID IDENTIFIED BY "password";
   

6.2.4.3 Step 3: Open (or Close) the Wallet

Immediately after you create the wallet key, the wallet is open, and you are ready to start encrypting data. However, if you have restarted the database after you created the wallet, you must manually open the wallet before you can use transparent data encryption.

To open the wallet:

• In SQL*Plus, enter the following ALTER SYSTEM statement, where password is the password you assigned to the encryption key:

  ALTER SYSTEM SET ENCRYPTION WALLET OPEN IDENTIFIED BY "password";
  

In most cases, leave the wallet open unless you have a reason for closing it. You can close the wallet to disable access to the master key and prevent access to the encrypted columns. However, the unencrypted data is still available. The wallet must be open for transparent data encryption to work. To reopen the wallet, use the ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password statement.

To close the wallet:

• In SQL*Plus, enter the following statement:

  ALTER SYSTEM SET ENCRYPTION WALLET CLOSE;
  

6.2.4.4 Step 4: Encrypt (or Decrypt) Data

After you have created a directory location for the wallet in the sqlnet.ora file and created the wallet itself, you are ready to encrypt either individual table columns or an entire tablespace.

This section contains the following topics:

• Encrypting Individual Table Columns

• Encrypting a Tablespace

6.2.5.1 Checking Whether a Wallet Is Open or Closed

You can find out if a wallet is open or closed by running the V$ENCRYPTION_WALLET view.

To check whether a wallet is open or closed:

• In SQL*Plus, run the V$ENCRYPTION_VIEW view as follows:

  SELECT * FROM V$ENCRYPTION_WALLET;
  

  The wallet status appears, similar to the following:

  WRL_TYPE  WRL_PARAMETER                             STATUS
  --------  ----------------------------------------  -------
  file      C:\oracle\product\11.1.0\db_1\wallets     OPEN
  

6.2.5.2 Checking Encrypted Columns of an Individual Table

You use the DESC (for DESCRIBE) statement in SQL*Plus to check the encrypted columns in a database table.

To check the encrypted columns of an individual table:

• In SQL*Plus, run the DESC statement using the following syntax.

  DESC tablename;
  

  For example:

  DESC OE.ORDER_ITEMS;
  

  A description of the table schema appears. For example:

  Name                                      Null?     Type
  ----------------------------------------  --------  --------------------------
  ORDER_ID                                  NOT NULL  NUMBER(12)
  LINE_ITEM_ID                              NOT NULL  NUMBER(3)
  PRODUCT_ID                                NOT NULL  NUMBER(6)
  UNIT_PRICE                                          NUMBER(8,2)
  QUANTITY                                            NUMBER(8) ENCRYPT
  

6.2.5.3 Checking All Encrypted Table Columns in the Current Database Instance

To check all encrypted table columns, you use the DBA_ENCRYPTED_COLUMNS view.

To check all encrypted table columns in the current database instance:

• In SQL*Plus, select from the DBA_ENCRYPTED_COLUMNS view:

  For example:

  SELECT * FROM DBA_ENCRYPTED_COLUMNS;
  

  The tables in the current database instance that contain encrypted columns are listed. For example:

  OWNER        TABLE_NAME    COLUMN_NAME    ENCRYPTION_ALG     SALT
  -----------  ----------    -----------    ----------------   ----
  OE           CUSTOMERS     INCOME_LEVEL   AES 128 bits key   YES
  OE           UNIT_PRICE    ORADER_ITEMS   AES 128 bits key   YES
  HR           EMPLOYEES     SALARY         AES 192 bits key   YES
  

6.2.5.4 Checking Encrypted Tablespaces in the Current Database Instance

Table 6-1 lists data dictionary views that you can use to check encrypted tablespaces.

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES

TABLESPACE_NAME              ENC
---------------------------- ----
SYSTEM                       NO
SYSAUX                       NO
UNCOTBS1                     NO
TEMP                         NO
USERS                        NO
EXAMPLE                      NO
SECURESPACE                  YES

SELECT * FROM V$ENCRYPTED_TABLESPACES;

TS#  ENCRYPTIONALG  ENCRYPTEDTS
-----------  --------------  -----------
         6   AES128          YES

6.3.2.1 Step 1: If Necessary, Create the Security Administrator Account

In "Example: Creating a Secure Application Role", you created a security administrator account called sec_admin for that example. You can use that account for this example. If you have not yet created this account, follow the steps in "Step 1: Create a Security Administrator Account" to create sec_admin.

6.3.2.2 Step 2: Update the Security Administrator Account

The sec_admin account user must have privileges to use the DBMS_RLS packages. User SYS owns this package, so you must log on as SYS to grant these package privileges to sec_admin. The user sec_admin also needs to have SELECT privileges on the CUSTOMERS table in the OE schema and the EMPLOYEES table in the HR schema.

To grant sec_admin privileges to use the DBMS_RLS package:

1. Start Database Control.

   See Oracle Database 2 Day DBA for instructions about how to start Database Control.

2. Log in as user SYS and connect with the SYSDBA privilege:

   • User Name: SYS

   • Password: Enter the password for SYS.

   • Connect As: SYSDBA

3. Click Server to display the Server subpage.

4. Under Security, select Users.

   The Users Page appears.

5. Select SEC_ADMIN and then click Edit.

   The Edit User page appears.

6. Click Object Privileges to display the Object Privileges page.

7. From the Select Object Type list, select Package, and then click Add.

   The Add Package Object Privileges page appears.

8. Under Select Package Objects, enter SYS.DBMS_RLS so that sec_admin will have access to the DBMS_RLS package.

9. Under Available Privileges, select EXECUTE, and then click Move to move it to the Selected Privileges list.

10. Click OK.

    The Edit User page appears.

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

    The Add Table Object Privileges page appears.

12. Select Table Objects, and then enter HR.EMPLOYEES so that sec_admin will have access to the HR.EMPLOYEES table.

13. Under Available Privileges, select SELECT, and then click Move to move it to the Selected Privileges list.

14. Click OK.

    The Edit User page appears.

15. Click Apply.

6.3.2.3 Step 3: Create User Accounts for This Example

You are ready to create accounts for the employees who need to access the OE.ORDERS table.

To create the employee user accounts:

1. In Database Control, click Users in the Database Instance link to return to the Users page.

   The Users page appears.

2. Click Create.

   The Create User page appears.

3. Enter the following information:

   • Name: LDORAN (to create the user account Louise Doran)

   • Profile: DEFAULT

   • Authentication: Password

   • Enter Password and Confirm Password: too_much2do

   • Default Tablespace: USERS

   • Temporary Tablespace: TEMP

   • Status: Unlocked

4. Click OK.

   The Users page appears, with LDORAN listed as a new user.

5. Select LDORAN from the Users page.

   The Edit User page appears.

6. Select Object Privileges to display the Object Privileges subpage.

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

   The Add Table Object Privileges page appears.

8. In the Select Table Objects field, enter the following text:

   OE.ORDERS
   

   Do not include spaces in this text.

9. In the Available Privileges list, select SELECT, and then click Move to move it to the Selected Privileges list. Click OK.

   The Create User page appears, with SELECT privileges for OE.ORDERS listed.

10. Click Apply.

11. Select LDORAN, and from the Actions list, select Create Like. Then, click Go.

    The Create User page appears.

12. Enter the following information:

    • Name: LPOPP (to create the user account for Finance Manager Luis Popp.)

    • Enter Password and Confirm Password: shop2drop

13. Click OK.

Both employee accounts have been created, and they have identical privileges. If either performs a SELECT statement on the OE.ORDERS table, he or she will be able to see all of its data.

6.3.2.4 Step 4: Create the F_POLICY_ORDERS Policy Function

The f_policy_orders policy is a PL/SQL function that defines the policy used to filter users who query the ORDERS table. To filter the users, the policy function uses the SYS_CONTEXT PL/SQL function to retrieve session information about users who are logging in to the database.

To create the application context and its package:

1. In Database Control, click Logout and then Login.

2. Log in as user sec_admin, whose password is fussy2all.

3. Click Schema to display the Schema subpage.

4. Under Programs, select Functions.

   The Functions page appears.

5. Click Create.

   The Create Function page appears.

6. Enter the following information:

   • Name: F_POLICY_ORDERS

   • Schema: SEC_ADMIN

   • Source: Enter the following code (but not the line numbers on the left side of the code) to create a function that checks whether the user who has logged on is a sales representative.

     The f_policy_orders function accomplishes this by using the SYS_CONTEXT PL/SQL function to get the session information of the user, and then it compares this information with the job ID of that user in the HR.EMPLOYEES table, for which sec_admin has SELECT privileges.

     1 (schema in varchar2, 
       2 tab in varchar2)  
       3 return varchar2 
       4 as
       5  v_job_id   varchar2(20);
       6  v_user     varchar2(100);
       7  predicate  varchar2(400);
       8 
       9 begin
      10  v_job_id  := null;
      11  v_user    := null;
      12  predicate := '1=2'; 
      13 
      14  v_user := lower(sys_context('userenv','session_user'));
      15 
      16  select lower(job_id) into v_job_id from hr.employees 
      17    where lower(email) = v_user; 
      18  
      19  if  v_job_id='sa_rep' then 
      20     predicate := '1=1';
      21  else  
      22     null;
      23  end if;
      24 
      25  return predicate;
      26 
      27  exception
      28   when no_data_found then 
      29    null;
      30  end;
     

     In this example:

     • Lines 1–2: Define parameters for the schema (schema) and table (tab) that need to be protected. Notice that the function does not mention the OE.ORDERS table. The ACCESSCONTROL_ORDERS policy that you create in Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy uses these parameters to specify the OE schema and ORDERS table. Ensure that you create the schema parameter first, followed by the tab parameter.

     • Line 3: Returns the string that will be used for the WHERE predicate clause. Always use VARCHAR2 as the data type for this return value.

     • Lines 4–7: Define variables to store the job ID, user name of the user who has logged on, and predicate values.

     • Lines 9–25: Encompass the creation of the WHERE predicate, starting the with the BEGIN clause at Line 9.

     • Lines 10–12: Sets the v_job_id and v_user variables to null, and the predicate variable to 1=2, that is, to a false value. At this stage, no WHERE predicate can be generated until these variables pass the tests starting with Line 16.

     • Line 14: Uses the SYS_CONTEXT function to retrieve the session information of the user and write it to the v_user variable.

     • Lines 16–23: Checks if the user is a sales representative by comparing the job ID with the user who has logged on. If the job ID of the user who has logged on is sa_rep (sales representative), then the predicate variable is set to 1=1. In other words, the user, by being a sales representative, has passed the test.

     • Line 25: Returns the WHERE predicate, which translates to WHERE role_of_user_logging_on IS "sa_rep". Oracle Database appends this WHERE predicate onto any SELECT statement that users LDORAN and LPOPP issue on the OE.ORDERS table.

     • Lines 27–29: Provide an EXCEPTION clause for cases where a user without the correct privileges has logged on.

7. Click OK.

6.3.2.5 Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy

Now that you have created the Virtual Private Database policy function, you can create the Virtual Private Database policy, accesscontrol_orders, and then attach it to the ORDERS table. To increase performance, add the CONTEXT_SENSITIVE parameter to the policy, so that Oracle Database only executes the f_policy_orders function when the content of the application context changes, in this case, when a new user logs on. Oracle Database only activates the policy when a user performs a SQL SELECT statement on the ORDERS table. The INSERT, UPDATE, and DELETE statements are impossible to use, because the user was not granted permissions.

To create the ACCESSCONTROL_ORDERS Virtual Private Database policy:

1. In Database Control, click the Database Instance link to display the Database Home page.

2. Click Server to display the Server subpage.

3. In the Security section, click Virtual Private Database Policies.

   The Virtual Private Database Policies page appears.

4. Click Create.

   The Create Policy page appears.

5. Under General, enter the following:

   • Policy Name: ACCESSCONTROL_ORDERS

   • Object Name: OE.ORDERS

   • Policy Type: Select CONTEXT_SENSITIVE.

     This type reevaluates the policy function at statement run-time if it detects context changes since the last use of the cursor. For session pooling, where multiple clients share a database session, the middle tier must reset the context during client switches. Note that Oracle Database does not cache the value the function returns for this policy type; it always runs the policy function during statement parsing. The CONTEXT_SENSITIVE policy type applies to only one object.

     To enable the Policy Type, select the Enabled check box.

6. Under Policy Function, enter the following:

   • Policy Function: Enter the name of the function that generates a predicate for the policy, in this case, SEC_ADMIN.F_POLICY_ORDERS.

   • Long Predicate: Do not select this box.

     Typically, you select this check box to return a predicate with a length of up to 32K bytes. By not selecting this check box, Oracle Database limits the predicate to 4000 bytes.

7. Under Enforcement, select SELECT.

8. Click OK.

6.3.2.6 Step 6: Test the ACCESSCONTROL_ORDERS Virtual Private Database Policy

At this stage, you are ready to test the accesscontrol_orders policy by logging on as each user and attempting to select data from the ORDERS table.

To test the ACCESSCONTROL_ORDERS policy:

1. Start SQL*Plus.

   From a command prompt, enter the following command to start SQL*Plus, and log in as Sales Representative Louise Doran, whose user name is LDORAN:

   SQLPLUS LDORAN
   Enter password: too_much2do
   

   SQL*Plus starts, connects to the default database, and then displays a prompt.

   For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following results should appear for Louise. As you can see, Louise is able to access all the orders in the OE.ORDERS table.

   COUNT(*)
   --------
        105
   

3. Connect as Finance Manager Luis Popp.

   CONNECT LPOPP
   Enter password: shop2drop
   

4. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following results should appear, because Mr. Popp, who is not a sales representative, does not have access to the data in the OE.ORDERS table.

   COUNT(*)
   --------
          0
   

5. Exit SQL*Plus:

   EXIT
   

6.3.2.7 Step 7: Optionally, Remove the Components for This Example

After completing this example, you can remove the data structures that you used if you no longer need them.

To remove the data structures created by sec_admin:

1. In Database Control, log in as user sec_admin, whose password is fussy2all.

2. Click Server to display the Server subpage.

3. Under Security, select Virtual Private Database Policies.

   The Virtual Private Database Policies page appears.

4. Under Search, enter the following information, and then click Go:

   • Schema Name: OE

   • Object Name: ORDERS

   • Policy Name: %

   The policy you created, ACCESSCONTROL_ORDERS, is listed.

5. Select ACCESSCONTROL_ORDERS, and then click Delete.

6. In the Confirmation page, click Yes.

To remove the user accounts and roles:

1. In Database Control, click Logout, and then Login.

2. Log in as the administrative user who created the user accounts and roles used in this example.

3. Click Server to display the Server subpage.

4. Under Security, select Users.

   The Users page appears.

5. Select each of the following users, and then click Delete to remove them:

   • LDORAN

   • LPOPP

   Do not remove sec_admin because you will need this account for later examples in this guide.

6. Exit Database Control.

6.4.3.1 Step 1: Install Oracle Label Security and Enable User LBACSYS

In a default Oracle Database installation, Oracle Label Security is not installed, but it is part of the products available in Oracle Database. You can install it in an existing database by using Oracle Universal Installer, and then Database Configuration Assistant (DBCA) to register it. Oracle Label Security provides its own user account, LBACSYS, which you will need to enable after the installation.

• Installing Oracle Label Security

• Registering Oracle Label Security with Oracle Database

• Enabling the Default Oracle Label Security User Account LBACSYS

Installing Oracle Label Security

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

To install Oracle Label Security:

1. Shut down the database instance in which you plan to install Oracle Label Security.

   Log in to SQL*Plus as SYS, connecting with the SYSDBA privilege. At the SQL prompt, enter the following command:

   SHUTDOWN IMMEDIATE
   

2. Exit SQL*Plus.

   EXIT
   

3. Stop the Oracle Database processes.

   • UNIX: Go to the $ORACLE_HOME/bin directory and run the following commands to stop the Database Console and the listener:

     ./emctl stop dbconsole
     ./lsnrctl stop
     

   • Windows: In the Windows Services tool, right-click the Oracle listener, console, and database service services, and then from the menu, select Stop. The names of these services begin with Oracle and include the name of the database instance. For example, assuming the database instance is orcl, the names would be similar to the following:

     • OracleDBConsoleorcl

     • OracleJobSchedulerORCL

     • OracleOraDB1g-home1TNSListener

     • OracleServiceORCL

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

5. Select Advanced Installation, and then click Next.

   The Select Installation Type window appears.

6. Select Custom, and then click Next.

   The Specify Home Details screen appears.

7. Select the Oracle base directory and the Oracle home directory in which you want to install Oracle Label Security. Click Next.

   (By default, Oracle Universal Installer offers to create a new Oracle home for you, so ensure that you select the correct existing Oracle home.) Oracle Universal Installer verifies that your system meets the minimum requirements. Next, the Available Product Components window is displayed.

8. Select the check box corresponding to Oracle Label Security.

   You can find this option under Oracle Database 11g, Enterprise Edition Options. Click Next.

   The Summary window is displayed.

9. Review your choices and then click Install.

   The progress window is displayed. When the installation completes, Oracle Universal Installer displays the End of Installation window.

10. Click Exit, and then click Yes to confirm the exit.

11. Restart the services and the database instance in which you installed Oracle Label Security.

    • UNIX: Go to the $ORACLE_HOME/bin directory and run the following commands to start the Database Console and the listener:

      ./emctl start dbconsole
      ./lsnrctl start
      

      Start SQL*Plus and then restart the database instance:

      sqlplus "sys/as sysoper"
      Enter password: password
      Connected to an idle instance
      SQL> STARTUP
      

    • Windows: In the Windows Services tool, right-click the Oracle listener, console, and database service services, and then from the menu, select Start. The names of these services begin with Oracle and include the name of the database instance. For example, assuming the database instance is orcl, the names would be similar to the following:

      • OracleDBConsoleorcl

      • OracleJobSchedulerORCL (Optional; you do not need to start it for the examples in this guide.)

      • OracleOraDB1g-home1TNSListener

      • OracleServiceORCL (This service starts when you start OracleDBConsole.)

Registering Oracle Label Security with Oracle Database

After you complete the installation, you must register Oracle Label Security with Oracle Database.

To register Oracle Label Security with Oracle Database:

1. Start Database Configuration Assistant.

   • UNIX: Enter the following command at a terminal window:

     dbca
     

     Typically, dbca is in the $ORACLE_HOME/bin directory.

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

     Alternatively, you can start Database Configuration Assistant at a command prompt:

     dbca
     

     As with UNIX, typically, dbca is in the ORACLE_BASE\ORACLE_HOME\bin directory.

2. In the Welcome page, click Next.

   The Operations page appears.

3. Select Configure Database Options, and then click Next.

   The Database page appears.

4. From the list, select the database where you installed Oracle Label Security and then click Next.

   The Management Options page appears.

5. Select Keep the database configured with Database Control.

   The Security Settings page appears.

6. Select the security option you prefer, and then click Next.

   Oracle recommends that you take advantage of the enhanced security settings for this release.

   The Database Components page appears.

7. Select Oracle Label Security, and then click Next.

   The Connection Mode page appears.

8. Select either Dedicated Server Mode or Shared Server Mode (depending on the selection you made when you created this database), click Finish, and then click OK in the confirmation prompts.

   Database Configuration Assistant registers Oracle Label Security, and then restarts the database instance.

9. Exit Database Configuration Assistant.

Enabling the Default Oracle Label Security User Account LBACSYS

The Oracle Label Security installation process creates a default user account, LBACSYS, who manages the Oracle Label Security features. An administrator can create a user who has the same privileges as this user, that is, EXECUTE privileges on the SA_SYSDBA, SA_COMPONENTS, and SA_LABEL_ADMIN PL/SQL packages. By default, LBACYS is created as a locked account with its password expired. Your next step is to unlock LBACYS and create a new password. Because user LBACSYS is using Database Control to create the Oracle Label Security policy, you must grant the SELECT ANY DICTIONARY privilege to LBACSYS.

To unlock LBACSYS, create a new password, and grant it SELECT ANY DICTIONARY privileges:

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

   In the Login page, enter SYSTEM and the password assigned to SYSTEM. Set Connect As to Normal. Select Login to log in.

2. Click Schema to display the Schema subpage.

3. Under Users & Privileges, select Users.

   The Users page appears.

4. Select user LBACSYS.

   To quickly find LBACSYS, enter lba in the Object Name field, and then click Go.

5. With LBACSYS selected, click Edit.

   The Edit User page appears.

6. Next to Status, select Unlocked.

7. In the Enter Password and Confirm Password fields, enter steplively2day to create the password.

8. Click System Privileges to display the Edit User: LBACSYS page.

9. Click Edit List.

   The Modify System Privileges page appears.

10. In the Available System Privileges list, select SELECT ANY DICTIONARY, and then click Move to move it to the Selected System Privileges list. Then click OK.

11. Click Apply.

6.4.3.2 Step 2: Create a Role and Three 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 a Role

• Creating the Users

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. Ensure that you are logged in to Database Control as SYSTEM.

   If you are not already logged in as SYSTEM, then select Logout, and then select Login. In the Login page, enter SYSTEM and the password assigned to that account. Set Connect As to Normal. Select Login to log in.

   If you are logged in as SYSTEM, click the Database Instance link to display the home page.

2. Click Schema to display the Schema subpage.

3. In the Users & Privileges section, click Roles.

   The Roles page appears.

4. Click Create.

   The Create Role page appears.

5. In the Name field, enter EMP_ROLE and leave Authentication set to None.

6. Select the Object Privileges subpage.

7. From the Select Object Type list, select Table, and then 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, and then under Available Privileges, move SELECT to the Selected Privileges list.

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

Creating the Users

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 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. Ensure that you are logged in to Database Control as SYSTEM.

   If you are not already logged in as SYSTEM, then select Logout, and then select Login. In the Login page, enter SYSTEM and the password assigned to that account. Set Connect As to Normal. Select Login to log in.

   If you are logged in as SYSTEM, click the Database Instance link to display the home page.

2. Click Server to display the Server subpage.

3. In the Security section, 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: kingpin2all

   • Default Tablespace: USERS

   • Temporary Tablespace: TEMP

   • 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 check 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 privileges. Do not grant sking the ADMIN OPTION option.

6. Click OK.

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

   The Create User page appears.

8. Create accounts for kpartner and ldoran, with eager2please as the password for kpartner and too_much2do as the password for ldoran.

   Create their names and 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 SELECT privileges on the HR.LOCATIONS table.

6.4.3.3 Step 3: Create the ACCESS_LOCATIONS Oracle Label Security Policy

Next, you are ready to create the ACCESS_LOCATIONS policy.

To create the ACCESS_LOCATIONS policy:

1. Log in to Database Control as user LBACSYS.

   Select Logout, and then select Login. In the Login page, enter LBACSYS and steplively2day for the password. Set Connect As to Normal. Select Login to log in.

2. Click Server to display the Server subpage.

3. In the Security section, click Oracle Label 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

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

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

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

     For all queries (READ_CONTROL)

     To 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 the illustration ols_new_policy.gif
   
   

6.4.3.4 Step 4: Define the ACCESS_LOCATIONS Policy-Level Components

At this stage, you have the policy and have set enforcement options for it. 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 need to 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.

   The Edit Label Security Policy page appears.

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

6.4.3.5 Step 5: Create the ACCESS_LOCATIONS Policy Data Labels

In this step, you create data labels for the policy you created in Step 4: Define the ACCESS_LOCATIONS Policy-Level Components. To create the data label, you need to 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 ACCESS_LOCATIONS policy.

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

   The Data Labels page appears.

4. Click Add.

   The Create Data Label page appears.

5. Enter the following information:

   • Numeric Tag: Enter 1000.

   • Level: From the list, select PUB. (To use the keyboard to select an item, enter the first letter of its name. For example, enter P to select PUB.)

     
     Description of the illustration ols_dlabel.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 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.

    At this stage, the CONF, PUB, and SENS labels appear in the Data Labels page.

    
    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.

6.4.3.6 Step 6: 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 ACCESS_LOCATIONS policy.

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

   The Authorization page appears.

4. Click Add Users.

   The Add User: Users page appears.

5. 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 needs to 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 ols_auth_user.gif
   
   

7. Click Next.

8. In the Privileges page, select Next.

   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, use the flashlight icon to select data to enter for the following fields, so that user SKING will be able to read sensitive and confidential data in HR.LOCATIONS:

   • Maximum Level: SENS (for SENSITIVE)

   • Minimum Level: CONF (for CONFIDENTIAL)

   • Default Level: SENS

   • Row Level: SENS

10. Click Next.

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

    • Privileges: Select no privileges.

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

    • Audit: Set all to None.

6.4.3.7 Step 7: 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 ACCESS_LOCATIONS policy.

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

   The Apply page appears.

4. 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 the illustration ols_apply.gif
   
   

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

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 list, and then click Go.

   The Authorization page appears.

4. Click Add Users.

   The Add 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 Bypass all Label Security checks (FULL) privilege, and then click Next.

   The Labels, Compartments, and Groups page appears.

9. Click Next.

   The Audit step appears.

10. Click Next.

    The Review step appears.

11. Click Finish.

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

    
    Description of the illustration ols_hr_added.gif
    
    

12. 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:

1. In SQL*Plus, connect as user HR, whose default password is hr.

   CONNECT HR
   Enter password: hr
   

   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 example, 2_much_fun.

   ALTER USER HR ACCOUNT UNLOCK IDENTIFIED BY 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 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;
   

   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.

6.4.3.9 Step 9: Test 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 kingpin2all.

   CONNECT sking
   Enter password: kingpin2all
   

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 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 these steps for users kpartner and ldoran.

   The password for kpartner is eager2please. She 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.
   

   The password for ldoran is too_much2do. She 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.
   

6.4.3.10 Step 10: Optionally, Remove the Components for This Example

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 Server to display the Server subpage.

3. In the Security section, click Users.

4. Select user kpartner, and then click Delete.

5. In the Confirmation page, click Yes.

6. Repeat Step 4 and Step 5 for users ldoran and sking.

7. Click Server to display the Server subpage.

8. Click the Datebase Instance link to return to the Database Home page.

9. In the Security section, click Roles.

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

11. In the Confirmation dialog box, click Yes.

12. Log out of Database Control, and then log back in as LABCSYS, whose password is steplively2day.

13. Click Server to display the Server subpage.

14. In the Security section, click Oracle Label Security.

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

    
    Description of the illustration ols_delete.gif
    
    

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

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

17. In the Confirmation page, click Yes.