5 Controlling Access with Oracle Database Vault

Oracle Database Vault enables you to restrict administrative access to an Oracle database.

Topics:

About Oracle Database Vault

You can use Oracle Database Vault to restrict administrative access to an Oracle database using a fine-grained approach.

This helps you address the most difficult security problems remaining today: protecting against insider threats, meeting regulatory compliance requirements, and enforcing separation of duty. In addition to restricting administrator access to your databases, Database Vault enables you to enforce separation of duty, and control who, when, where and how applications, databases, and data are accessed.

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 policies using these components by using either Oracle Database Vault Administrator, or by using its PL/SQL packages. In a multitenant environment, each policy applies only to the current pluggable database (PDB). Step 1: Enable Oracle Database Vault

Tutorial: Controlling Administrator Access to a User Schema

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.

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

Topics:

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.

If Oracle Label Security is not enabled, then the registration process enables it as well as Database Vault.

To register Oracle Database Vault:

  1. Log into the database instance as user SYS with the SYSDBA administrative privilege.

    For example:

    sqlplus sys as sysdba
    Enter password: password 
    
  2. Check if Oracle Database Vault has already been enabled. The PARAMETER column is case sensitive, so use the case shown here.

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

    If it returns TRUE, then Oracle Database Vault is registered. Go to "Step 2: Grant the SELECT Privilege on the OE.CUSTOMERS Table to User SCOTT". If it returns FALSE, then register Database Vault with your database, as described in Oracle Database Vault Administrator's Guide.

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.

Topics:

Enabling User SCOTT for Oracle Database Vault

You can use Enterprise Manager to enable user SCOTT.

To enable user SCOTT:

  1. In Enterprise Manager, ensure that you are logged in as the Database Vault Account Manager (a user who has been granted the role DV_ACCTMGR) with the NORMAL role selected.

    After you enable 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.

    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. Instead, you must log in as the Database Vault Account Manager.

  2. From the Administration menu, select Security, then Users.

  3. In the Users page, select the user SCOTT, and in the View User page, click Edit.

    The Edit User page appears.

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

  5. Click Apply.

  6. Do not exit Enterprise Manager.

Granting User SCOTT the SELCT Privilege on the OE.CUSTOMERS Table

After you enable user SCOTT, you can grant him the appropriate privileges.

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.

    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 administrative 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 must create a realm around the OE schema.

To create a realm around the OE schema:

  1. In Enterprise Manager, click Log Out to log out of the database.

  2. In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.

  3. Log in as a user who has been granted the DV_OWNER or DV_ADMIN account (for example, dbv_owner). Connect using the Normal Role role.

  4. From the Administration menu, select Security, then Database Vault.

  5. In the Database Vault page, select the Administration tab.

  6. Under Database Vault Components, select Realms. (It should be selected by default.)

    The Realms page appears. (It should be selected by default.)

  7. Click Create.

  8. In the Create Realm page, enter the following information:

    • Name: OE Protections

    • Description: Realm to protect the OE schema

    • Status: Click Enabled.

    • Audit Options: Select Audit on Failure.

  9. Click Next.

    The Realm Secured Objects page appears.

  10. Click Add.

  11. In the Add Secured Objects window, add the following information:

    • Owner: OE

    • Object Type: TABLE

    • Object Name: %

  12. Click OK.

    The OE table is now listed as a realm-secured object.

  13. Click Next.

  14. In the Realm Authorizations page, click Add.

    The Add Authorizations window appears.

  15. Enter the following information:

    • Realm Authorization Grantee: Enter OE.

    • Realm Authorization Type: Select Owner.

    • Realm Authorization Ruleset: Enter Disabled.

  16. Click OK, and then click Next.

    The Review page appears, so that you can check your settings.

  17. Click Finish.

    The Realms page now shows the OE Protections realm.

  18. Do not exit Enterprise Manager.

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

    CONNECT SYS AS SYSDBA
    Enter password: password
    Connected.
    

    If you were connected as SYS before, then you do not need to reconnect. The changes that you just made take effect immediately.

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

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

Topics:

Dropping the OE Protections Realm

You can use Enterprise Manager to drop the OE protections realm.

To drop the OE Protections realm:

  1. In Enterprise Manager, if you have logged out of the Database Vault Administrator pages, then log back in as the Database Vault Owner account that you created when you installed Oracle Database Vault (for example, dbv_owner).

  2. In the Administration page, under Database Vault Feature Administration, click Realms.

    The Realms page appears.

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

  4. Log out of Oracle Database Vault Administrator.

Revoking the SELECT Privilege on OE.CUSTOMERS from User SCOTT

To revoke the SELECT privilege on OE.CUSTOMERS from user SCOTT, you can use SQL*Plus.

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;
    

Disabling Oracle Database Vault and Oracle Label Security

You can use SQL*Plus to disable Oracle Database Vault and Oracle Label Security.

To disable Oracle Database Vault and if necessary, Oracle Label Security:

  1. Connect as a user who has been granted the DV_OWNER role.

    For example:

    CONNECT dbv_owner
    Enter password: password
    
  2. Run the following procedure to disable Oracle Database Vault:

    EXEC DVSYS.DBMS_MACADM.DISABLE_DV;
    
  3. Connect as user SYS with the SYSDBA administrative privilege

    CONNECT SYS AS SYSDBA
    Enter password: password
    
  4. Run the following procedure to disable Oracle Label Security:

    EXEC LBACSYS.OLS_ENFORCEMENT.DISABLE_OLS;
    

    When you register and enable Oracle Database Vault, Oracle Label Security is also enabled. If you choose to not disable Oracle Database Vault, then do not disable Oracle Label Security, because Database Vault uses Oracle Label Security. (This guide assumes that you are disabling Database Vault.) However, you can have Oracle Label Security enabled and Database Vault disabled.

  5. Restart the database.

    SHUTDOWN IMMEDIATE
    STARTUP