Controlling Access with Oracle Database Vault

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.
Tutorial: Controlling Administrator Access to a User Schema
In this tutorial, you create a realm around the OE schema to protect it from administrator access but allow SCOTT to access OE.CUSTOMERS.

5.1 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

5.2 Tutorial: Controlling Administrator Access to a User Schema

In this tutorial, you create a realm around the OE schema to protect it from administrator access but allow SCOTT to access OE.CUSTOMERS.

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
After you install Oracle Database, you must register Oracle Database Vault and enable the Oracle Database Vault Account Manager user account.
Step 2: Grant SELECT on the OE.CUSTOMERS Table to User SCOTT
To test the tutorial later on, user SCOTT must select from the OE.CUSTOMERS table.
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.
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.
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.
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.

5.2.1 Step 1: Enable Oracle Database Vault

After you install Oracle Database, you must register Oracle Database Vault and enable the Oracle Database Vault Account Manager user account.

Oracle Database Vault is installed when you perform a default installation of Oracle Database.

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

To register Oracle Database Vault:

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

For example:
```
sqlplus sys as sysdba
Enter password: password 
```
Check if Oracle Database Vault has already been enabled. The PARAMETER column is case sensitive, so use the case shown here.
```
SELECT * FROM DBA_DV_STATUS;
```
If it returns TRUE for both the DV_CONFIGURE_STATUS and the DV_ENABLE_STATUS, then Oracle Database Vault is registered. Go to Step 2: Grant SELECT 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.

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

To test the tutorial later on, user SCOTT must select from the OE.CUSTOMERS table.

Topics:

Enabling User SCOTT for Oracle Database Vault
You can use Enterprise Manager to enable user SCOTT.
Granting User SCOTT the SELECT Privilege on the OE.CUSTOMERS Table
After you enable user SCOTT, you can grant him the appropriate privileges.

5.2.2.1 Enabling User SCOTT for Oracle Database Vault

You can use Enterprise Manager to enable user SCOTT.

To enable user SCOTT:

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.
From the Administration menu, select Security, then Users.
In the Users page, select the user SCOTT, and in the View User page, click Edit.

The Edit User page appears.
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.
Click Apply.
Do not exit Enterprise Manager.

5.2.2.2 Granting User SCOTT the SELECT 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:

sqlplus oe
Enter password: password
Connected.

Grant user SCOTT the SELECT privilege on the OE.CUSTOMERS table.
```
GRANT SELECT ON CUSTOMERS TO SCOTT;
```

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

This is because SYS has administrative privileges and SCOTT has an explicit SELECT privilege granted by user OE.

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

In SQL*Plus, connect as user SYS using the SYSDBA administrative privilege
```
sqlplus sys as sysdba
Enter password: password
```
Select from the OE.CUSTOMERS table as follows:
```
SELECT COUNT(*) FROM OE.CUSTOMERS;
```
The following output should appear
```
COUNT(*)
--------
     319
```

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

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

In Enterprise Manager, click Log Out to log out of the database.
In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.
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.
From the Security menu, select Database Vault.
In the Database Vault page, select the Administration tab.
Under Database Vault Components, select Realms.

The Realms page appears.
Click Create.
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.
Click Next.

The Realm Secured Objects page appears.
Click Add.
In the Add Secured Objects window, add the following information:
- Owner: OE
- Object Type: TABLE
- Object Name: %
Click OK.

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

Description of the illustration dv_realm_object.gif
Click Next.
In the Realm Authorizations page, click Add.

The Add Authorizations window appears.
Enter the following information:
- Realm Authorization Grantee: Select OE.
- Realm Authorization Type: Select Owner.
- Realm Authorization Rule Set: Select Disabled.
Click OK, and then click Next.

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

The Realms page now shows the OE Protections realm.

Description of the illustration dv_realm.gif
Do not exit Enterprise Manager.

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

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

Connect as user SCOTT.

CONNECT SCOTT
Enter password: password
Connected.

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.

5.2.6 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.
Revoking 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.
Disabling Oracle Database Vault and Oracle Label Security
You can use SQL*Plus to disable Oracle Database Vault and Oracle Label Security.

5.2.6.1 Dropping the OE Protections Realm

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

To drop the OE Protections realm:

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).
From the Security menu, select Database Vault.
In the Oracle Database Vault page, select the Administration tab.
Under Database Vault Feature Administration, click Realms.

The Realms page appears.
Select OE Protections from the list of realms, and then click Delete. Then click Yes in the Confirmation page.
Log out of Oracle Database Vault Administrator.

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

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

In SQL*Plus, connect as user OE.

CONNECT OE
Enter password: password
Connected.

Revoke the SELECT privilege from user SCOTT.
```
REVOKE SELECT ON CUSTOMERS FROM SCOTT;
```

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

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

For example:
```
CONNECT dbv_owner
Enter password: password
```
Run the following procedure to disable Oracle Database Vault:
```
EXEC DVSYS.DBMS_MACADM.DISABLE_DV;
```
Connect as user SYS with the SYSDBA administrative privilege
```
CONNECT SYS AS SYSDBA
Enter password: password
```
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.
Restart the database.
```
SHUTDOWN IMMEDIATE
STARTUP
```