8 Configuring Secure Application Roles for Oracle Database Vault

8.1 What Are Secure Application Roles for Oracle Database Vault?

In Oracle Database Vault, you can create a secure application role that you enable with an Oracle Database Vault rule set. Regular Oracle Database secure application roles are enabled by custom PL/SQL procedures. You use secure application roles to prevent users from accessing data from outside an application. This forces users to work within the framework of the application privileges that have been granted to the role.

The advantage of basing database access for a role on a rule set is that you can store database security policies in one central place, as opposed to storing them in all your applications. Basing the role on a rule set provides a consistent and flexible method to enforce the security policies that the role provides. In this way, if you must update the security policy for the application role, you do it in one place, the rule set. Furthermore, no matter how the user connects to the database, the result is the same, because the rule set is bound to the role.

You can run reports on secure application roles that you create in Oracle Database Vault. See "Related Reports" for more information.

This chapter explains how to configure secure application roles by using Oracle Database Vault Administrator. To configure secure application roles by using the PL/SQL interfaces and packages provided by Oracle Database Vault, refer to the following appendixes:

• Appendix H, "PL/SQL Interfaces to Oracle Database Vault"

• Appendix E, "Oracle Database Vault DVSYS.DBMS_MACADM Package"

8.2 Creating and Editing Secure Application Roles

Follow these steps:

1. Log in to Oracle Database Vault Administrator using a database account that has been granted the DV_OWNER role.

   At a minimum, you must have the DV_ADMIN role. "Starting Oracle Database Vault Administrator" explains how to log in.

2. In the Administration page, under Database Vault Feature Administration, click Secure Application Roles.

3. In the Secure Application Roles page:

   • To create a new secure application role, click Create.

   • To edit an existing secure application role, select it from the list and then click Edit.

     Remember that you can modify an existing secure application role only if it has been created in Oracle Database Vault. You cannot modify secure application roles or database roles that have been created outside of Oracle Database Vault. If you want to modify an existing Oracle Database role so that it can work with Oracle Database Vault, create a new secure application role in Oracle Database Vault and then grant the existing role to the secure application role. For example, in SQL*Plus:

     SQL> GRANT myExistingDBrole TO myDVrole;
     

     After you create a new secure application role, you must modify your code to use this new role. You can use DVSYS.DBMS_MACSEC_ROLES.SET_ROLE in your application code to accomplish this. See "SET_ROLE Function" for more information about the SET_ROLE function.

4. In the Create (or Edit) Role page, enter the following settings and then click OK.

   • General

   • Rule Set

General

Enter the following settings:

• Role: Enter the name using no more than 30 characters, with no spaces. Preferably, enter the role name in upper case letters, though you are not required to do so. Ensure that this name follows the standard Oracle naming conventions for role creation using the CREATE ROLE statement, described in Oracle Database SQL Language Reference. This attribute is mandatory.

• Status: Select either Enabled or Disabled to enable or disable the secure application role during run time. The default is Enabled. This attribute is mandatory.

  • Enabled: Calls the DVSYS.DBMS_MACSEC_ROLES.SET_ROLE function to determine whether or not a role is set for a database session.

    See "SET_ROLE Function" for more information about this function.

  • Disabled: Prevents the need for the DVSYS.DBMS_MACSEC_ROLES.SET_ROLE function.

  See "Oracle Database Vault PL/SQL Packages" for more information about the DVSYS.DBMS_MACSEC_ROLES.SET_ROLE function.

Rule Set

From the list, select the rule set that you want to associate with the secure application role. This attribute is mandatory.

When calling DVSYS.DBMS_MACSEC_ROLES.SET_ROLE, if the rule set evaluates to true, then Oracle Database Vault sets the role for the database session. If the rule set evaluates to false, then the role is not set.

See Chapter 5, "Configuring Rule Sets" for more information about rule sets.

8.3 Securing a Secure Application Role

Users who have database administrative privileges can use the DROP ROLE command to delete secure application roles that were created using Oracle Database Vault.

To prevent the database administrator from deleting a secure application role, when you create secure application roles, protect them by using a realm. To do so, add the role to a realm authorization. See "Defining Realm Authorization" for more information.

8.4 Deleting a Secure Application Role

Before you delete a secure application role, you can locate the various references to it by querying the role-related Oracle Database Vault views. See "Oracle Database Vault Public Views" for more information.

To delete a secure application role:

1. Check and modify any applications that may be using the secure application role that you want to delete.

2. In the Oracle Database Vault Administration page, select Secure Application Roles.

3. In the Secure Application Roles page, select the role that you want to remove.

4. Click Remove.

5. In the Confirmation page, click Yes.

8.5 How Secure Application Roles Work

The process flow for a secure application role that is managed by Oracle Database Vault is as follows:

1. Create or update the role either in Oracle Database Vault Administrator or by using the secure application role-specific functions in the DVSYS.DBMS_MACADM package.

   See "Secure Application Role Functions Within DVSYS.DBMS_MACADM" for more information.

2. Modify your application to call the role, by using the DVSYS.DBMS_MACSEC_ROLES.SET_ROLE function.

   See "SET_ROLE Function" for more information.

3. Oracle Database Vault then evaluates the rule set associated with the secure application role.

   If the rule set evaluates to true, then Oracle Database Vault enables the role for the current session. If the rule set evaluates to false, the role is not enabled. In either case, Oracle Database Vault processes the associated auditing and custom event handlers for the rule set associated with the secure application role.

8.6 Example of How Secure Application Roles Work

Suppose you wanted to create a secure application role to allow employees to manage their own accounts within the in-house applications your company has created. To do so, you would follow these steps:

• Step 1: Create a Rule Set to Be Used with the Secure Application Role

• Step 2: Create the Secure Application Role Using the Rule Set

• Step 3: Grant Privileges to the Role

• Step 4: Enable the Role in Your Applications

• Step 5: Test the New Secure Application Role

8.6.2 Step 2: Create the Secure Application Role Using the Rule Set

Figure 8-1 shows how to create a secure application role for employees to manage their own accounts.

Figure 8-1 Secure Application Role Example

Description of "Figure 8-1 Secure Application Role Example"

SQL> CONNECT appadmin
Enter password: password
Connected.

SQL> GRANT SELECT ON hr.employees TO employee_details_role;
Grant succeeded.

DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('employee_details_role')

SQL> CONNECT system@hrdb_from_cubicle
Enter password: password
Connected.

SQL> select dvf.f$network from dual
CORPORATE 

SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('employee_details_role');
PL/SQL procedure successfully completed.

SQL> SELECT count(*) from hr.employees 
COUNT(*)
----------
        57
----------

SQL> CONNECT system@hrdb_from_home
Enter password: password
Connected.

SQL> select dvf.f$network from dual 
REMOTE

SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('employee_details_role');
*
ERROR at line 1:
ORA-01031: insufficient privileges

8.7 How Secure Application Roles Affect Performance

You can check system performance by running tools such as Oracle Enterprise Manager (including Oracle Enterprise Manager Database Control, which is installed by default with Oracle Database), Statspack, and TKPROF. For more information about Oracle Enterprise Manager, see the Oracle Enterprise Manager documentation set. For information about Database Control, refer to its online Help. Oracle Database Performance Tuning Guide describes the Statspack and TKPROF utilities.

8.8 Related Reports

Table 8-1 lists Oracle Database Vault reports that are useful for analyzing secure application roles. See Chapter 11, "Oracle Database Vault Reports" for information about how to run these reports.