Skip Headers
Oracle® Database 2 Day + Security Guide
11g Release 2 (11.2)

Part Number E10575-08
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

6 Securing Data

This chapter contains:

About Securing Data

Oracle Database provides many ways to secure data. This chapter describes the following methods that you can use to secure data on your site:

Encrypting Data Transparently with Transparent Data Encryption

Transparent data encryption enables you to quickly encrypt one or more table columns or a tablespace. It is easy to implement and has many advantages over other types of database encryption.

This section contains:

About Encrypting Sensitive Data

Encrypted data can only be read by its recipient. You use encryption to protect data in a potentially unprotected environment, such as data you have placed on backup media that is sent to an offsite storage location.

The encryption data includes the following components:

  • An algorithm to encrypt the data. The encryption algorithm is used by Oracle databases to encrypt data. Oracle Database supports several industry-standard encryption and hashing algorithms, including the Advanced Encryption Standard (AES) encryption algorithm, which has been approved by the National Institute of Standards and Technology (NIST).

  • A key to encrypt and decrypt data. When you encrypt data, Oracle Database uses the key and clear text data as input into the encryption algorithm. Conversely, when you decrypt data, the key is used as input into the algorithm to reverse the process and retrieve the clear text data. Oracle Database uses a symmetric encryption key to perform this task, in which the same key is used to both encrypt and decrypt the data. The encryption key is stored in the data dictionary, but encrypted with another master key.

As mentioned earlier, you can encrypt individual table columns or an entire tablespace. Be careful that you do not mix the two. For example, suppose you encrypt a table column and then encrypt its surrounding tablespace. This double encryption can cause performance problems. In addition, column encryption has limitations in data type support, and only supports B-tree indexes for equality searches. To check the current encrypted settings, you can query the V$ENCRYPTED_TABLESPACES data dictionary view for tablespaces, and the DBA_ENCRYPTED_COLUMNS view for encrypted columns.

When Should You Encrypt Data?

In most cases, you encrypt sensitive data on your site to meet a regulatory compliance. For example, sensitive data such as credit card numbers, Social Security numbers, or patient health information must be encrypted.

Historically, users have wanted to encrypt data because they want to restrict data access from their database administrators. However, this problem is more of an access control problem, not an encryption problem. You can address this problem by using Oracle Database Vault to control the access to your application data from database administrators.

In most cases, you encrypt sensitive data such as credit cards, and Social Security numbers to prevent access when backup tapes or disk drives are lost or stolen. In recent years industry regulations such as the Payment Card Industry (PCI) Data Security Standard and the Healthcare Insurance Portability and Accountability Act (HIPAA) have become a driving factor behind increased usage of encryption for protecting credit card and health care information, respectively.

See Also:

Oracle Database Security Guide for common misconceptions about encrypting stored data

How Transparent Data Encryption Works

Transparent data encryption enables you to encrypt individual table columns or an entire tablespace. When a user inserts data into an encrypted column, transparent data encryption automatically encrypts the data. When users select the column, the data is automatically decrypted.

To encrypt data by using transparent data encryption, you create the following components:

  • A wallet to store the master encryption key. The wallet is an operating system file located outside the database. The database uses the wallet to store the master encryption key. To create the wallet, you can use Enterprise Manager or the ALTER SYSTEM command. The wallet is encrypted using a password as the encryption key. You create the password when you create the wallet. Access to the contents (or master key) of the wallet is thus restricted to only those who know the password. After the wallet is created, you must open the wallet using the password so that the database can access the master encryption key.

  • A location for the wallet. You can specify the wallet location in the sqlnet.ora file.

Afterward, when a user enters data, Oracle Database performs the following steps:

  1. Retrieves the master key from the wallet.

  2. Decrypts the encryption key using the master key.

  3. Uses the encryption key to encrypt the data the user entered.

  4. Stores the data in encrypted format in the database.

If the user is selecting data, the process is similar: Oracle Database decrypts the data and then displays it in clear text format.

Transparent data encryption has the following advantages:

  • As a security administrator, you can be sure that sensitive data is safe if the storage media or data file is stolen or lost.

  • Implementing transparent data encryption helps you address security-related regulatory compliance issues.

  • Data from tables is transparently decrypted for the database user. You do not need to create triggers or views to decrypt data.

  • Database users need not be aware of the fact that the data they are accessing is stored in encrypted form. Data is transparently decrypted for the database users and does not require any action on their part.

  • Applications need not be modified to handle encrypted data. Data encryption and decryption is managed by the database.

Transparent data encryption has a minimal impact on performance. Transparent data encryption column encryption affects performance only when data is retrieved from or inserted into an encrypted column. There is no impact on performance for operations involving unencrypted columns, even if these columns are in a table containing encrypted columns. However, be aware that encrypted data needs more storage space than clear text data. On average, encrypting a single column requires between 32 and 48 bytes of additional storage for each row. Transparent tablespace encryption provides even better performance because Oracle Database performs the encryption and decryption at the I/O block layer. Once blocks are decrypted, they are cached in Oracle Database memory for optimal performance.

See Also:

Oracle Database Advanced Security Administrator's Guide for detailed information about using Transparent Data Encryption

Configuring Data to Use Transparent Data Encryption

To start using transparent data encryption, you must create a wallet and set a master key. The wallet can be the default database wallet shared with other Oracle Database components, or a separate wallet specifically used by transparent data encryption. Oracle recommends that you use a separate wallet to store the master encryption key. This wallet will be used for all data that is being encrypted through transparent data encryption.

You follow these steps to configure table columns to use transparent data encryption:

See Also:

Oracle Database Advanced Security Administrator's Guide for detailed information about using tablespace encryption

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 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.2.0\db_1 directory.

  2. Create a backup copy of the sqlnet.ora file, which by default is located in the $ORACLE_HOME/network/admin 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.2.0\db_1\ORA_WALLETS)))
    
  4. Save and close the sqlnet.ora file.

  5. If the compatibility of the database is set to a release earlier than Oracle Database Release 10.2, then restart the database.

    1. Log in to SQL*Plus and then check the database compatibility.

      sqlplus sys as sysdba
      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.

    2. Check the value of the COMPATIBLE parameter.

      SHOW PARAMETER COMPATIBLE
      
      NAME                       TYPE           VALUE
      -------------------------- -------------- --------------------
      compatible                 string         11.2.0
      
    3. If the value is greater than 10.2, then you can go to Step 2: Create the Wallet. If the value is less than 10.2, then restart the database as follows.

      SHUTDOWN IMMEDIATE
      STARTUP
      

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 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 before the database can encrypt or decrypt the data.

To create the wallet:  

  1. In SQL*Plus, connect as a user with administrative privileges, such as SYS, 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 use to protect the Oracle wallet:

    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 does 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";
    

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 use to protect the wallet:

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

You must inclose the password in quotation marks.

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. 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, and ensure that you enclose the password in quotation marks:

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

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

The decisions that you make when you identify columns to be encrypted are determined by governmental security regulations, such as California Senate Bill 1386, or by industry standards such as the Payment Card Industry (PCI) Data Security Standard. Credit card numbers, Social Security numbers, and other personally identifiable information (PII) fall under this category. Another need for encryption is defined by your own internal security policies — trade secrets, research results, or employee salaries and bonuses. See "When Should You Encrypt Data?" for guidelines about when and when not to encrypt data.

Follow these guidelines when you select columns to encrypt:

  • Check the data types of the columns you plan to encrypt. Transparent data encryption supports the following data types:

      BINARY_FLOAT NUMBER
      BINARY_DOUBLE NVARCHAR2
      CHAR RAW
      DATE TIMESTAMP
      NCHAR VARCHAR2

  • Ensure that the columns you select are not part of a foreign key. With transparent data encryption, each table has its own encryption key, which is stored in the database data dictionary and encrypted with the external master key. Encrypted columns cannot be used as foreign keys.

To encrypt a column in a table:  

  1. Ensure that you have created and opened a wallet key.

    "Step 2: Create the Wallet" explains how to create a wallet key. To open an existing wallet key, see "Step 3: Open (or Close) the Wallet".

  2. Start Database Control.

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

  3. Enter an administrator user name (for example, SYSTEM, or the name of a security administrator) and password, and then click Login.

    The Database Home page appears.

  4. Click Schema to display the Schema subpage.

  5. Under Database Objects, select Tables.

    The Tables page appears.

  6. Do one of the following:

    • To create a new table, click Create, and then answer the questions in the subsequent page to start creating the table.

    • To modify an existing table, search for the table name by entering its schema name into the Schema field and the table name in the Object Name field. (You can use the percent sign (%) wildcard character to search for a group of tables, for example O% to find all tables beginning with the letter O.) When the table is listed in the Tables page, select the table, and then click Edit.

    In the Create Table or Edit Table page, you can set its encryption options.

    For example, to encrypt columns in the OE.ORDERS table, the Edit Table page appears as follows:

    Description of encrypt_cols.gif follows
    Description of the illustration encrypt_cols.gif

  7. In the Create Table (or Edit Table) page, do the following:

    1. Select the column that you want to encrypt.

      Do not select columns that are part of a foreign key constraint (primary or unique key columns). You cannot encrypt these columns. These columns are indicated with a key or check mark icon to the left of their names.

    2. Click Encryption Options to display the Encryption Options for the Table page.

    3. From the Encryption Algorithm list, select from the following options:

      • AES192: Sets the key length to 192 bits. AES is the abbreviation for Advanced Encryption Standard.

      • 3DES168: Sets the key length to 168 bits. 3DES is the abbreviation for Triple Data Encryption Standard.

      • AES128: Sets the key length to 128 bits. This option is the default.

      • AES256: Sets the key length to 256 bits.

    4. Under Key Generation, select either Generate Key Randomly or Specify Key. If you select Specify Key, enter characters for the seed values in the Enter Key and Confirm Key fields.

      The Generate Key Randomly setting enables salt. Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing the same text to appear different when encrypted. Salt removes one method attackers use to steal data, namely, matching patterns of encrypted text.

    5. Click Continue to return to the Create Table (or Edit Table) page.

    6. Enable encryption for the column by selecting its box under Encrypted.

  8. Click Continue.

    The Create Table (or Edit Table) page appears.

While a table is being updated, read access is still possible. Afterward, existing and future data in the column is encrypted when it is written to the database file, and it is decrypted when an authorized user selects it. If data manipulation language (DML) statements are needed, you can use online redefinition statements.

Encrypting a Tablespace

You can encrypt a new tablespace while you are creating it, but you cannot encrypt an existing tablespace. As a workaround, you can use the CREATE TABLE AS SELECT, ALTER TABLE MOVE, or use Oracle Data Pump import to get data from an existing tablespace into an encrypted tablespace. For details about creating a tablespace, see Oracle Database 2 Day DBA.

To encrypt a tablespace:  

  1. Ensure that you have created and opened a wallet key.

    "Step 2: Create the Wallet" explains how to create a wallet key. To open an existing wallet key, see "Step 3: Open (or Close) the Wallet".

  2. Start Database Control.

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

  3. Enter an administrator user name (for example, SYSTEM, or the name of a security administrator) and password, and then click Login.

    The Database Home page appears.

  4. Click Server to display the Server subpage.

  5. Under Storage, click Tablespaces.

    The Tablespaces page appears.

  6. Click Create, and then answer the questions in the subsequent page to start creating the tablespace and its required data file.

  7. In the Create Tablespace page, do the following:

    1. Under Type, select the Encryption box, under Permanent.

    2. Select Encryption options to display the Encryption Options page.

    3. From the Encryption Algorithm list, select from the following options:

      • AES192: Sets the key length to 192 bits. AES is the abbreviation for Advanced Encryption Standard.

      • 3DES168: Sets the key length to 168 bits. 3DES is the abbreviation for Triple Data Encryption Standard.

      • AES128: Sets the key length to 128 bits. This option is the default.

      • AES256: Sets the key length to 256 bits.

      See "Available Methods" under Step 5 in "Configuring Network Encryption" for more information about these encryption algorithms.

    4. Click Continue.

      The Create Tablespace page appears.

  8. Click OK.

    The new tablespace appears in the list of existing tablespaces. Remember that you cannot encrypt an existing tablespace.

See Also:

Checking Existing Encrypted Data

You can query the database for the data that you have encrypted. You can check for individually encrypted columns, all tables in the current database instance that have encrypted columns, or all tablespaces that are encrypted.

This section contains:

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, query 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.2.0\db_1\wallets     OPEN
    

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
    

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;
    

    This SELECT statement lists all tables and column in the database that contain columns encrypted using Oracle Transparent Data Encryption. 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
    

See Also:

Oracle Database Reference for more information about the DBA_ENCRYPTED_COLUMNS view

Checking Encrypted Tablespaces in the Current Database Instance

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

Table 6-1 Data Dictionary Views for Encrypted Tablespaces

Data Dictionary View Description

DBA_TABLESPACES

Describes all tablespaces in the database. For example, find out if the tablespace has been encrypted, enter the following:

SELECT TABLESPACE_NAME, ENCRYPTED FROM DBA_TABLESPACES

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

USER_TABLESPACES

Describes the tablespaces accessible to the current user. It has the same columns as DBA_TABLESPACES, except for the PLUGGED_IN column.

V$ENCRYPTED_TABLESPACES

Displays information about the tablespaces that are encrypted. For example:

SELECT * FROM V$ENCRYPTED_TABLESPACES;
TS#  ENCRYPTIONALG  ENCRYPTEDTS
-----------  -------------  -----------
         6   AES128          YES

The list includes the tablespace number, its encryption algorithm, and whether its encryption is enabled or disabled.

If you want to find the name of the tablespace, use the following join operation:

SELECT NAME, ENCRYPTIONALG ENCRYPTEDTS
FROM V$ENCRYPTED_TABLESPACES, V$TABLESPACE
WHERE V$ENCRYPTED_TABLESPACES.TS# = V$TABLESPACE.TS#;

See Also:

Oracle Database Reference for more information about data dictionary views

Choosing Between Oracle Virtual Private Database and Oracle Label Security

Both Oracle Virtual Private Database (VPD) and Oracle Label Security (OLS) enable you to restrict the data that different users can see in database tables. But when should you use Virtual Private Database and when should you use Oracle Label Security? 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.

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 6-2 compares the features of Oracle Virtual Private Database with Oracle Label Security.

Table 6-2 Comparing Oracle Virtual Private Database with Oracle Label Security

Feature VPD OLS

Provides row-level security

Yes

Yes

Provides column-level security (column masking)

Yes

No

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

Yes

NoFoot 1 

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

Yes

No

Restricts database operations by privileged usersFoot 2 

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

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

No

Yes

Provides a user account to manage its administration

NoFoot 4 

YesFoot 5 

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

No

Yes

Is provided in the default installation of Oracle Database

Yes

No

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

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

Controlling Data Access with Oracle Virtual Private Database

Oracle Virtual Private Database (VPD) enables you to dynamically add a WHERE clause in any SQL statement that a user executes. The WHERE clause filters the data the user is allowed to access, based on the identity of a user.

This section contains:

See Also:

Oracle Database Security Guide for detailed information about how Oracle Virtual Private Database works

About Oracle Virtual Private Database

Oracle Virtual Private Database (VPD) provides row-level security at the database table or view level. You can extend it to provide column-level security as well. Essentially, Virtual Private Database inserts an additional WHERE clause to any SQL statement that is used on any table or view to which a Virtual Private Database security policy has been applied. (A security policy is a function that allows or prevents access to data.) The WHERE clause allows only users whose identity passes the security policy, and hence, have access to the data that you want to protect.

An Oracle Virtual Private Database policy has the following components, which are typically created in the schema of the security administrator:

  • A PL/SQL function to append the dynamic WHERE clause to SQL statements that affect the Virtual Private Database tables. For example, a PL/SQL function translates the following SELECT statement:

    SELECT * FROM ORDERS;
    

    to the following:

    SELECT * FROM ORDERS
      WHERE SALES_REP_ID = 159;
    

    In this example, the user can only view orders by Sales Representative 159. The PL/SQL function used to generate this WHERE clause is as follows:

     
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    
    CREATE OR REPLACE FUNCTION auth_orders( 
     schema_var IN VARCHAR2, 
     table_var  IN VARCHAR2 
    ) 
    RETURN VARCHAR2
    IS  
     return_val VARCHAR2 (400); 
    BEGIN 
     return_val := 'SALES_REP_ID = 159';
     RETURN return_val; 
    END auth_orders; 
    /
    

    In this example:

    • Lines 2–3: Create parameters to store the schema name, OE, and table name, ORDERS. (The second parameter, table_var, for the table, can also be used for views and synonyms.) Always create these two parameters in this order: create the parameter for the schema first, followed by the parameter for the table, view, or synonym object. Note that the function itself does not specify the OE schema or its ORDERS table. The Virtual Private Database policy you create uses these parameters to specify the OE.ORDERS table.

    • Line 5: Returns the string that will be used for the WHERE predicate clause.

    • Lines 6–10: Encompass the creation of the WHERE SALES_REP_ID = 159 predicate.

    You can design the WHERE clause to filter the user information based on the session information of that user, such as the user ID. To do so, you create an application context. Application contexts can be used to authenticate both database and nondatabase users. An application context is a name-value pair. For example:

    SELECT * FROM oe.orders 
     WHERE sales_rep_id = SYS_CONTEXT('userenv','session_user'); 
    

    In this example, the WHERE clause uses the SYS_CONTEXT PL/SQL function to retrieve the user session ID (session_user) designated by the userenv context. See Oracle Database Security Guide for detailed information about application contexts.

  • A way to attach the policy the package. Use the DBMS_RLS.ADD_POLICY function to attach the policy to the package. Before you can use the DBMS_RLS PL/SQL package, you must be granted EXECUTE privileges on it. User SYS owns the DBMS_RLS package.

The advantages of enforcing row-level security at the database level rather than at the application program level are enormous. Because the security policy is implemented in the database itself, where the data to be protected is, this data is less likely to be vulnerable to attacks by different data access methods. This layer of security is present and enforced no matter how users (or intruders) try to access the data it protects. The maintenance overhead is low because you maintain the policy in one place, the database, rather than having to maintain it in the applications that connect to this database. The policies that you create provide a great deal of flexibility because you can write them for specific DML operations.

Tutorial: Creating an Oracle Virtual Private Database Policy

The ORDERS table in the Order Entry database, OE, contains the following information:

Name                                   Null?    Type
-------------------------------------- -------- ---------------------------------
ORDER_ID                               NOTNULL  NUMBER(12)
ORDER_DATE                             NOTNULL  TIMESTAMP(6) WITH LOCAL TIME ZONE
ORDER_MODE                                      VARCHAR2(8)
CUSTOMER_ID                            NOTNULL  NUMBER(6)
ORDER_STATUS                                    NUMBER(2)
ORDER_TOTAL                                     NUMBER(8,2)
SALES_REP_ID                                    NUMBER(6)
PROMOTION_ID                                    NUMBER(6)

Suppose you want to limit access to this table based on the person who is querying the table. For example, a sales representative should only see the orders that he or she have created, but other employees should not. In this tutorial, you create a sales representative user account and an account for a finance manager. Then, you create an Oracle Virtual Private Database policy that will limit the data access to these users based on their roles.

The Virtual Private Database policy that you will create is associated with a PL/SQL function. Because VPD policies are controlled by PL/SQL functions or procedures, you can design the policy to restrict access in many different ways. For this tutorial, the function you create will restrict access by the employees based on to whom they report. The function will restrict the customer access based on the customer's ID.

You may want to store VPD policies in a database account separate from the database administrator and from application accounts. In this tutorial, you will use the sec_admin account, which was created in "Tutorial: Creating a Secure Application Role", to create the VPD policy. This provides better security by separating the VPD policy from the applications tables.

To restrict access based on the sensitivity of row data, you can use Oracle Label Security (OLS). OLS lets you categorize data into different levels of security, with each level determining who can access the data in that row. This way, the data access restriction is focused on the data itself, rather than on user privileges. See "Enforcing Row-Level Security with Oracle Label Security" for more information.

In this tutorial:

Step 1: If Necessary, Create the Security Administrator Account

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

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 must 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 the SEC_ADMIN user, and in the View User page, 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. In the Select Table Objects field, 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. It shows that user sec_admin has object privileges for the EMPLOYEES table and DBMS_RLS PL/SQL package. Ensure that you do not select the grant option for either of these objects.

  15. Click Apply.

    All the changes you have made, in this case, the addition of the two object privileges, are applied to the sec_admin user account.

Step 3: Create User Accounts for This Tutorial

You are ready to create accounts for the employees who must 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: Enter a password that meets the requirements in "Requirements for Creating Passwords".

    • Default Tablespace: USERS

    • Temporary Tablespace: TEMP

    • Status: Unlocked

  4. Select the Object Privileges tab.

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

    The Add Table Object Privileges page appears.

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

    OE.ORDERS
    

    Do not include spaces in this text.

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

  8. Click OK.

    The Users page appears, with user ldoran is listed in the UserName column.

  9. Select the selection button for user LDORAN, and from the Actions list, select Create Like. Then, click Go.

    The Create User page appears.

  10. Enter the following information:

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

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

  11. Click OK.

Both employee accounts have been created, and they have identical privileges. If you check the privileges for user LPOPP, you will see that they are identical to those of user LDORAN's. At this stage, if either of these users performs a SELECT statement on the OE.ORDERS table, he or she will be able to see all of its data.

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.

  3. Click Schema to display the Schema subpage.

  4. Under Programs, select Functions.

    The Functions page appears.

  5. Ensure that the Object Type menu is set to Function, and then click Create.

    The Create Function page appears.

  6. Enter the following information:

    • Name: F_POLICY_ORDERS

    • Schema: SEC_ADMIN

    • Source: Delete the empty function code that has been provided, and then 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. You can copy and paste this text by positioning the cursor at the start of (schema in varchar2) in the first line.

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

      In this example:

      • Lines 1–2: Define parameters for the schema (schema) and table (tab) that must 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.

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. Hence, the user cannot run the INSERT, UPDATE, and DELETE statements, because the policy does not allow him or her to do so.

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.

    The Virtual Private Database Policies page appears.

  4. Click Create.

    The Create Policy page appears, with the Policy subpage displaying.

  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 that 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 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 box to return a predicate with a length of up to 32K bytes. By not selecting this box, Oracle Database limits the predicate to 4000 bytes.

  7. Under Enforcement, select the SELECT option but deselect the remaining options that already may be selected.

  8. Do not select any options under Security Relevant Columns.

  9. Click OK.

    The Virtual Private Database Policies page appears, with the ACCESSCONTROL_ORDERS policy listed in the list of policies.

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

    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: password
    
  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. Because Mr. Popp does not have access, Oracle Database only allows him access to 0 rows.

    COUNT(*)
    --------
           0
    
  5. Exit SQL*Plus:

    EXIT
    

Step 7: Optionally, Remove the Components for This Tutorial

After completing this tutorial, 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.

  2. Click Server to display the Server subpage.

  3. Under Security, select Virtual Private Database.

    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 (for example, SYSTEM) who created the user accounts and roles used in this tutorial.

  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 tutorials in this guide.

  6. Exit Database Control.

Enforcing Row-Level Security with 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 section contains:

About Oracle Label Security

You use Oracle Label Security to secure your database tables at the row level, and assign these rows different levels of security based on the needs of your site. 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.

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 Database Control, or you can create them using the SA_SYSDBA, SA_COMPONENTS, and SA_LABEL_ADMIN PL/SQL packages. For information about using the PL/SQL packages, see Oracle Label Security Administrator's Guide. This guide explains how to create Oracle Label Security labels and policies by using Database Control.

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

Guidelines for Planning an Oracle Label Security Policy

Before you create an Oracle Label Security policy, you must determine where and 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.

Tutorial: Applying Security Labels to the HR.LOCATIONS Table

This tutorial demonstrates the general concepts of using Oracle Label Security. In it, 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 "Controlling Data 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

In this tutorial:

Step 1: Register Oracle Label Security and Enable the LBACSYS Account

In a default Oracle Database installation, Oracle Label Security is installed. However, you must register Oracle Label Security and then enable the default Oracle Label Security account, which is called LBACSYS.

Registering Oracle Label Security with Oracle Database

After you complete the installation, you must register Oracle Label Security with Oracle Database. You can check if Oracle Label Security is already registered by entering the following SELECT statement in SQL*Plus. The PARAMETER column is case sensitive, so use the case shown here.

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

If the output is TRUE, then Oracle Label Security has been registered. Go to "Enabling the Default Oracle Label Security User Account LBACSYS". If it is FALSE, then register Oracle Label Security.

To register Oracle Label Security with Oracle Database: 

  1. Stop the database, Database Control console process, and listener.

    • UNIX: Log in to SQL*Plus as user SYS with the SYSOPER privilege and shut down the database. Then from the command line, stop the Database Control console process and listener.

      For example:

      sqlplus sys as sysoper
      Enter password: password
      
      SQL> SHUTDOWN IMMEDIATE
      SQL> EXIT
      
      $ emctl stop dbconsole
      $ lsnrctl stop [listener_name]
      

      For Oracle RAC installations, shut down each database instance as follows:

      $ srvctl stop database -d db_name
      
    • Windows: Stop the database, Database Control console process, and listener from the Services tool in the Control Panel. The names of Oracle Database services begin with Oracle.

  2. Enable Oracle Label Security as follows:

    • UNIX: Run the following commands:

      $ cd $ORACLE_HOME/rdbms/lib
      $ make -f ins_rdbms.mk lbac_on ioracle
      
    • Windows: In the ORACLE_BASE\ORACLE_HOME\bin directory, rename the oralbacll.dll.dbl file to oralbacll.dll.

  3. Restart the database and listener. (Do not restart the Database Control console process yet.)

    • UNIX: Log in to SQL*Plus as user SYS with the SYSOPER privilege and restart the database. Then from the command line, restart the listener.

      For example:

      sqlplus sys as sysoper
      Enter password: password
      
      SQL> STARTUP
      SQL> EXIT
      
      $ lsnrctl start [listener_name]
      

      For Oracle RAC installations, restart each database instance as follows:

      $ srvctl start database -d db_name
      
    • Windows: Restart the database and listener from the Services tool in the Control Panel. The names of Oracle Database services begin with Oracle.

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

  5. In the Welcome page, click Next.

    The Operations page appears.

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

    The Database page appears.

  7. From the list, select the database where you installed Oracle Database and then enter the name and password of a user who has been granted the DBA role (for example, user SYS). Click Next.

    The Database Content page appears.

    Database Content page of DBCA
    Description of the illustration ols_config.gif

  8. Select Oracle Label Security and then click Next.

    The Connection Mode page appears.

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

  10. Exit Database Configuration Assistant.

  11. Restart the Database Control console process.

    • UNIX: Run the following command:

      $ emctl start dbconsole
      
    • Windows: Restart the Database Control console process (for example, OracleDBConsoleorcl if the database is named orcl) from the Services tool in the Control Panel.

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 enable the LBACSYS user account:  

  1. Log in to Database Control as the user SYS with the SYSDBA privilege.

  2. Click Server to display the Server subpage.

  3. Under Security, select Users.

    The Users page appears.

  4. Select the LBACSYS user, and in the View User page, click Edit.

    The Edit User page appears.

  5. Next to Status, select Unlocked.

  6. In the Enter Password and Confirm Password fields, enter a secure password, according to the guidelines in "Requirements for Creating Passwords".

    For greater security, do not reuse the same password that was used in previous releases of Oracle Database.

  7. Click Roles to display the Edit User: LBACSYS page.

  8. Click Edit List.

    The Modify Roles page appears.

  9. In the Available Roles list, select the SELECT_CATALOG_ROLE role and then then click Move to move it to the Selected Roles list. Then click OK to return to the Edit User page.

  10. Click System Privileges.

  11. Click Edit List.

    The Modify System Privileges page appears.

  12. 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 to return to the Edit User page.

  13. Select Object Privileges.

  14. In the Select Object Type list, select Package and then click Add.

  15. In the Add Package Object Privileges page, do the following:

    1. Under Select Package Objects, select the flashlight icon to display the Select Package Objects window.

    2. Set the Schema to LBACSYS.

    3. Enter % in the Search Package Name field and then click Go.

    4. Select all the package objects listed, for both pages of listed objects.

    5. Click Select to return to the Add Package Object Privileges window.

    6. Under Available Privileges, move the EXECUTE privilege to the Selected Privileges list.

    7. Click OK.

  16. Click OK to return to the Edit User page, and then click Apply to apply the changes.

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

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. Connect to Database Control as user SYSTEM.

  2. From the Database Home page, click Server to display the Server subpage.

  3. In the Security 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: 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 privileges. Do not grant sking the ADMIN OPTION option.

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

  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.

    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.

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, log in as user LBACSYS. 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 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 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 ols_new_policy.gif follows
    Description of the illustration ols_new_policy.gif

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

    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.

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

    The Data Labels page appears.

  4. Click Add.

    The Create Data Label page appears.

  5. Enter the following information:

  6. Click OK.

    The data label appears in the Data Labels page.

  7. Click Add again, and then create a data label for the CONF 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.

    At this stage, 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.

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 selection button for 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 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 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

  9. Click Next to go to the Privileges page.

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

  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.

    • 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

    • Privileges: Select no privileges.

    • Audit: Set all to None.

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

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

    • Privileges: Select no privileges.

    • Audit: Set all to None.

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 selection button for 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 box is not selected.

  7. Ensure that the Enabled box is selected.

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

    The default policy enforcement options for ACCESS_LOCATIONS are:

    • For all queries (READ_CONTROL)

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

  9. Click OK.

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

    Description of ols_apply.gif follows
    Description of the illustration ols_apply.gif

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

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.

    The Authorization page appears.

  4. Click Add Users.

    The Add Users page appears.

  5. Under Database Users, click Add.

    The Search and Select window appears.

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

    The Create User page lists user HR.

  7. Click Next to display the Add Users: Levels, Compartments and Groups page, and then click Next again to display the Privileges page.

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

    The Audit page appears.

  9. Click Next.

    The Review page appears.

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

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

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

    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.

Step 10: Optionally, Remove the Components for This Tutorial

Remove the components that you created for this tutorial.

To remove the components for this tutorial: 

  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 the Database Instance link to return to the Database home page.

  8. Click Server to display the Server subpage.

  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.

  13. Click Server to display the Server subpage.

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

  15. In the Label Security Policies page, 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.

  16. Log out of Database Control.

  17. Optionally, remove Oracle Label Security.

    See Oracle Label Security Administrator's Guide for information about removing Oracle Label Security.s

Controlling Administrator Access with Oracle Database Vault

Oracle Database Vault enables you to restrict administrative access to an Oracle database. This helps you address the most difficult security problems remaining today: protecting against insider threats, meeting regulatory compliance requirements, and enforcing separation of duty.

About Oracle Database Vault

Typically, the main job of an Oracle database administrator is to perform tasks such database tuning, installing upgrades, monitoring the state of the database, and then remedying any problems that he or she finds. In a default Oracle Database installation, database administrators also have the ability to create users and access user data. For greater security, you should restrict these activities only to those users who must perform them. This is called separation of duty, and it frees the database administrator to focus on tasks ideally suited to his or her expertise, such as performance tuning.

By restricting administrator access to your Oracle databases, Oracle Database Vault helps you to follow common regulatory compliance requirements, such as the Payment Card Industry (PCI) Data Security Standard (DSS) requirements, Sarbanes-Oxley (SOX) Act, European Union (EU) Privacy Directive, and Healthcare Insurance Portability and Accountability Act (HIPAA). These regulations require strong internal controls on access, disclosure or modification of sensitive information that could lead to fraud, identity theft, financial irregularities and financial penalties.

Oracle Database Vault provides the following ways for you to restrict administrator access to an Oracle database:

  • Group database schemas, objects, and roles that you want to secure. This grouping is called a realm, and all the components of the realm are protected. After you, the Database Vault administrator, create a realm, you designate a user to manage access to the realm. For example, you can create a realm around one table within a schema, or around the entire schema itself.

  • Create PL/SQL expressions to customize your database restrictions. You create an expression in a rule, and for multiple rules within one category, you can group the rules into a rule set. To enforce the rules within the rule set, you then associate the rule set with a realm or command rule. For example, if you wanted to prevent access to a database during a maintenance period (for example, from 10 to 12 p.m.), you can create a rule to restrict access only during those hours.

  • Designate specific PL/SQL statements that are accessible or not accessible to users. These are called command rules. A command rule contains a command to be protected and a rule set that determines whether the execution of the command is permitted. You can create a command rule to protect SELECT, ALTER SYSTEM, database definition language (DDL), and data manipulation language (DML) statements that affect one or more database objects. You can associate a rule set to further customize the command rule.

  • Define attributes to record data such as session users or IP addresses that Oracle Database Vault can recognize and secure. These attributes are called factors. You can use factors for activities such as authorizing database accounts to connect to the database or creating filtering logic to restrict the visibility and manageability of data. To further customize the factor, you can associate a rule set with it.

  • Design secure application roles that are enabled only by Oracle Database Vault rules. After you create the secure application role in Oracle Database Vault, you associate a rule set with it. The rule set defines when and how the secure application role is enabled or disabled.

You can create these components by using either Oracle Database Vault Administrator, or by using its PL/SQL packages.

Tutorial: Controlling Administrator Access to the OE Schema

The OE schema has several tables that contain confidential data, such as the credit limits allowed for customers and other information. Order Entry tables typically contain sensitive information, such as credit card or Social Security numbers. This type of information must be restricted only to individuals whose job requires access to this information, according to Payment Card Industry (PCI) Data Security Standards (DSS).

In this tutorial, you create a realm around the OE schema, which will protect it from administrator access. However, user SCOTT needs access to the OE.CUSTOMERS table, so you must ensure that he can continue to access this data.

In this tutorial:

Step 1: Enable Oracle Database Vault

Oracle Database Vault is installed when you perform a default installation of Oracle Database. After you install it, you must register Oracle Database Vault with Oracle Database and then enable the Oracle Database Vault Account Manager user account.

Checking if Oracle Database Vault Is Enabled

You can check if Oracle Database Vault is enabled by logging in to SQL*Plus and entering the following SELECT statement. The PARAMETER column is case sensitive, so use the case shown here.

SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';

If it returns TRUE, then Oracle Database Vault is registered. Go to "Enabling Database Access Control for the Database Vault Account Manager Account". Otherwise, complete the registration process described in the next section.

Registering Oracle Database Vault with Oracle Database

In the registration process, Oracle Database Vault is enabled and you are prompted to create its administrative accounts.

To register Oracle Database Vault: 

  1. Stop the database, Database Control console process, and listener.

    • UNIX: Log in to SQL*Plus as user SYS with the SYSOPER privilege and shut down the database. Then from the command line, stop the Database Control console process and listener.

      For example:

      sqlplus sys as sysoper
      Enter password: password
      
      SQL> SHUTDOWN IMMEDIATE
      SQL> EXIT
      
      $ emctl stop dbconsole
      $ lsnrctl stop [listener_name]
      

      For Oracle RAC installations, shut down each database instance as follows:

      $ srvctl stop database -d db_name
      
    • Windows: Stop the database, Database Control console process, and listener from the Services tool in the Control Panel. The names of Oracle Database services begin with Oracle.

  2. Enable Oracle Database Vault as follows:

    • UNIX: Run the following commands. The make command enables both Oracle Database Vault (dv_on) and Oracle Label Security (lbac_on). You must enable Oracle Label Security before you can use Database Vault.

      $ cd $ORACLE_HOME/rdbms/lib
      $ make -f ins_rdbms.mk dv_on lbac_on ioracle
      
    • Windows: In the ORACLE_BASE\ORACLE_HOME\bin directory, rename the oradvll.dll.dbl file to oradvll.dll. If Oracle Label Security has not been enabled, then change the name of the oralbacll.dll.dbl file to oralbacll.dll. You must enable Oracle Label Security before you can use Database Vault.

  3. Restart the database and listener. (Do not restart the Database Control console process yet.)

    • UNIX: Log in to SQL*Plus as user SYS with the SYSOPER privilege and restart the database. Then from the command line, restart the listener.

      For example:

      sqlplus sys as sysoper
      Enter password: password
      
      SQL> STARTUP
      SQL> EXIT
      
      $ lsnrctl start [listener_name]
      

      For Oracle RAC installations, restart each database instance as follows:

      $ srvctl start database -d db_name
      
    • Windows: Restart the database and listener from the Services tool in the Control Panel. The names of Oracle Database services begin with Oracle.

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

  5. In the Welcome page, click Next.

    The Operations page appears.

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

    The Database page appears.

  7. From the list, select the database where you installed Oracle Database and then enter the name and password of a user who has been granted the DBA role. Click Next.

    The Database Content page appears.

  8. Perform one of the following actions:

    • If Oracle Label Security is already enabled: Select the Oracle Database Vault option, and then click Next.

    • If Oracle Label Security is not enabled: Select the Oracle Label Security option so that the Oracle Database Vault option becomes available for selection. Select the Oracle Database Vault option as well, and then click Next.

    The Oracle Database Vault Credentials page appears.

  9. Specify the name and password for the Database Vault Owner account (for example, DBVOWNER) and the Database Vault Account Manager (for example, DBVACCTMGR).

    Enter any password that is secure, according to the password guidelines described in "Requirements for Creating Passwords". Oracle Database Vault has additional password requirements, which are displayed if you try to create an incorrect password.

  10. Click Next.

    The Connection Mode page appears.

  11. 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 Database Vault, and then restarts the database instance.

  12. Exit Database Configuration Assistant.

  13. Restart the Database Control console process.

    • UNIX: Run the following command:

      $ emctl start dbconsole
      
    • Windows: Restart the Database Control console process (for example, OracleDBConsoleorcl if the database is named orcl) from the Services tool in the Control Panel.

Enabling Database Access Control for the Database Vault Account Manager Account

The Database Vault Account Manager account must have additional privileges to use Database Control.

To grant the necessary privileges to the Database Vault Account Manager account: 

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

    In the Login page, enter SYS and the password assigned to SYS. Set Connect As to SYSDBA. Select Login to log in. See Oracle Database 2 Day DBA for instructions about how to start Database Control.

  2. Click Server to display the Server subpage.

  3. Under Security, select Users.

    The Users page appears.

  4. Select the Database Vault Account Manager account (for example, DBVACCTMGR).

    To quickly find DBVACCTMGR, enter DBV in the Object Name field, and then click Go.

  5. Select the DBVACCTMGR user, and in the View User page, click Edit.

    The Edit User page appears.

  6. Click Roles to display the Edit User: DBVACCTMGR page.

  7. Click Edit List.

    The Modify Roles page appears.

  8. In the Available Roles list, select the SELECT_CATALOG_ROLE role and then then click Move to move it to the Selected Roles list. Then click OK to return to the Edit User page.

  9. Click System Privileges.

  10. Click Edit List.

    The Modify System Privileges page appears.

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

  12. Click Apply.

Step 2: Grant the SELECT Privilege on the OE.CUSTOMERS Table to User SCOTT

To test the tutorial later on, user SCOTT must select from the OE.CUSTOMERS table. First, you should ensure that he SCOTT account is active.

To enable user SCOTT: 

  1. In Database Control, connect as the Oracle Database Vault Account Manager account with the Normal privilege.

    After you install Oracle Database Vault, you no longer can use the administrative accounts (such as SYS and SYSTEM) to create or enable user accounts. This is because right out of the box, Oracle Database Vault provides separation-of-duty principles to administrative accounts. From now on, to manage user accounts, you must use the Oracle Database Vault Account Manager account.

    However, administrative users still have the privileges they do need. For example, user SYS, who owns system privileges and many PL/SQL packages, can still grant privileges on these to other users. However, user SYS can no longer create, modify, or drop user accounts.

  2. Click Server to display the Server subpage.

  3. Under Security, select Users.

    The Users page appears.

  4. Select the SCOTT user, and in the View User page, click Edit.

    The Edit User page appears.

  5. Enter the following settings:

    • Enter Password and Confirm Password: If the SCOTT account password status is expired, then enter a new password. Enter any password that is secure, according to the password guidelines described in "Requirements for Creating Passwords".

    • Status: Click Unlocked.

  6. Click Apply.

  7. Click Logout.

To grant user SCOTT the SELECT privilege on the OE.CUSTOMERS table: 

  1. Log in to SQL*Plus as user OE.

    sqlplus oe
    Enter password: password
    Connected. 
    
  2. Grant user SCOTT the SELECT privilege on the OE.CUSTOMERS table.

    SQL> GRANT SELECT ON CUSTOMERS TO SCOTT;
    

Step 3: Select from the OE.CUSTOMERS Table as Users SYS and SCOTT

At this stage, both users SYS and SCOTT can select from the OE.CUSTOMERS table, because SYS has administrative privileges and because SCOTT has an explicit SELECT privilege granted by user OE.

To select from OE.CUSTOMERS as users SYS and SCOTT: 

  1. In SQL*Plus, connect as user SYS using the SYSDBA privilege

    sqlplus sys as sysdba
    Enter password: password
    
  2. Select from the OE.CUSTOMERS table as follows:

    SELECT COUNT(*) FROM OE.CUSTOMERS;
    

    The following output should appear

    COUNT(*)
    --------
         319
    
  3. Connect as user SCOTT, and then perform the same SELECT statement.

    CONNECT SCOTT
    Enter password: password
    Connected.
    
    SELECT COUNT(*) FROM OE.CUSTOMERS;
    

    The following output should appear:

    COUNT(*)
    --------
         319
    

Step 4: Create a Realm to Protect the OE.CUSTOMERS Table

To restrict the OE.CUSTOMER table from administrative access, you will create a realm around the OE schema.

To create a realm around the OE schema: 

  1. Start Oracle Database Vault Administrator.

    In a browser, enter the following URL:

    https://host_name:port/dva

    Replace host_name with the name of the server on which you installed Oracle Database Vault, and port with the Oracle Enterprise Manager Console HTTPS port number. In most cases, the name of the server and port number are the same as those used by Database Control.

    If you cannot start Database Vault Administrator, you may need to manually deploy it. See Oracle Database Vault Administrator's Guide for more information.

  2. In the Login to Database page, enter the following information:

    • User Name: Enter the name of the DV_OWNER or DV_ADMIN account (for example, DBVOWNER).

    • Password: Enter the password of the user whose name you entered.

    • Host: Enter the host name or IP address of the computer where you installed Oracle Database Vault, for example, myserver.us.example.com.

    • Port: Enter the port number for the database, for example, 1521.

    • SID/Service: Enter either the SID (for example, orcl) of the database, or the service (for example, myserver.us.example.com).

    The Database Instance Administration page appears.

  3. Under Database Vault Feature Administration, select Realms.

    The Realms page appears.

  4. Click Create.

    The Create Realm page appears.

  5. Enter the following information:

    • Name: OE Protections

    • Description: Realm to protect the OE schema

    • Status: Click Enabled.

    • Audit Options: Select Audit on Failure.

  6. Click OK.

    The Realms page appears, with the OE schema listed as a realm. However, it has no protected objects or authorized users yet.

    Realms page in DVA
    Description of the illustration dv_realm.gif

  7. Select the OE Protections realm and then click Edit.

    The Edit Realm page appears.

  8. Under Realm Secured Objects, click Create.

    The Create Realm Secured Object page appears.

  9. From the Object Owner list, select OE.

  10. From the Object Type list, select TABLE.

  11. In the Object Name field, enter % to specify all tables within the OE schema, and then click OK.

    The Edit Realm page appears.

  12. Under Realm Authorizations, click Create.

    The Create Realm Authorization page appears.

  13. From the Grantee list, select OE [USER], and then set the Authorization Type to Owner. Then set Authorization Rule Set to <Non Selected>.

    This authorizes the OE user to manage access to the objects within the OE schema. As an Owner, the OE user can grant or revoke realm-secured database roles, and access, manipulate, and create objects protected by the OE Protections realm.

    The Authorization Rule Set list enables to you select a rule that further controls access, such as the time the realm is in effect, and so on.

  14. Click OK to return to the Edit Realm page, and then click OK again to return to the Realms page.

  15. Do not exit Oracle Database Vault Administrator.

Step 5: Test the OE Protections Realm

Now that you have created a realm to protect the OE schema, you are ready to test it. You do not need to restart the database session, because any protections you define in Oracle Database Vault take effect right away.

To test the OE Protections realm: 

  1. Connect to SQL*Plus as user SYS using the SYSDBA privilege.

    CONNECT SYS/AS SYSDBA
    Enter password: password
    Connected.
    
  2. Try selecting from the OE.CUSTOMERS table.

    SELECT COUNT(*) FROM OE.CUSTOMERS;
    

    The following output should appear:

    ERROR at line 1:
    ORA-01031: insufficient privileges
    

    The OE Protections realm prevents the administrative user from accessing the OE.CUSTOMERS table. Because you defined the OE Protections realm to protect the entire schema, the administrative user does not have access to any of the other tables in OE, either.

  3. Connect as user SCOTT.

    CONNECT SCOTT
    Enter password: password
    Connected.
    
  4. Try selecting from the OE.CUSTOMERS table.

    SELECT COUNT(*) FROM OE.CUSTOMERS;
    

    The following output should appear:

    COUNT(*)
    ----------
           319
    

    The OE Protections realm does not apply to user SCOTT because user OE has explicitly granted this user the SELECT privilege on the OE.CUSTOMERS table. Oracle Database Vault sets up the protections you need, but does not override the explicit privileges you have defined. SCOTT still can query this table.

Step 6: Optionally, Remove the Components for This Tutorial

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

To revoke the SELECT privilege on OE.CUSTOMERS from user SCOTT: 

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

    CONNECT OE
    Enter password: password
    Connected. 
    
  2. Revoke the SELECT privilege from user SCOTT.

    REVOKE SELECT ON CUSTOMERS FROM SCOTT;
    

To drop the OE Protections realm: 

  1. If you have logged out of Oracle Database Vault Administrator, log back in as the Database Vault Owner account that you created when you installed Oracle Database Vault (for example, DBVOWNER).

    See Step 1 in "Step 4: Create a Realm to Protect the OE.CUSTOMERS Table" for how to start Database Vault Administrator.

    The Administration page appears.

  2. Under Database Vault Feature Administration, click Realms.

    The Realms page appears.

  3. Select OE Protections from the list of realms, and then click Remove. Then click Yes in the Confirmation page.

  4. Log out of Oracle Database Vault Administrator.

To disable Oracle Database Vault: