1. Administrator's Guide
  2. Configuring Secure Application Roles for Oracle Database Vault

8 Configuring Secure Application Roles for Oracle Database Vault

Secure application roles enable you to control how much access users have to an application.

8.1 What Are Secure Application Roles in 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.

You only can create a secure application role in a PDB, not in the CDB root or the application root.

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. All you need to do is to create the role and then associate it with a rule set. The associated rule set validates the user who is trying to enable the role.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.2 Security for Oracle Database Vault Secure Application Roles

Users who have database administrative privileges may try to use the DROP ROLE statement to delete Oracle Database Vault secure application roles.

Whenever an Oracle Database Vault secure application role has been created, Database Vault adds the secure application role to the Oracle Database Vault realm. This prevents database administrator from deleting the secure application role using the DROP ROLE statement.

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.3 Creating an Oracle Database Vault Secure Application Role

When you create a secure application role, you associate it with an Oracle Database Vault-maintained rule set to determine when the role is enabled or disabled.

Before creating a secure application role, you must have a rule set available to associate with the role. The rule set can be an Oracle maintained rule set or, preferably, a rule set you have created based on the rules you created to determine when, where, and how the secure application role should be allowed to be enabled.
  1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.
  2. If necessary, create a rule set that the secure application role will use.
    The DBA_DV_RULE_SET data dictionary view lists existing rule sets.
  3. Run the DBMS_MACADM.CREATE_ROLE procedure to create the security role.
    For example: 
    BEGIN
 DBMS_MACADM.CREATE_ROLE(
  role_name      => 'access_hr_employees', 
  enabled        => DBMS_MACUTL.G_YES,
  rule_set_name  => 'Can Access HR.EMPLOYEES');
END;
/
  4. As the owner of the schema that will be affected by the secure application role, grant the appropriate privileges to the secure application role.
    These privileges should be the same privileges that the secure application role will control. For example, suppose you created a role that enabled users to select or update the HR.EMPLOYEES table. The HR user would need to grant the SELECT and UPDATE privileges to the secure application role.
    For example: 
    CONNECT HR@pdb_name
Enter password: password

GRANT SELECT, UPDATE ON EMPLOYEES TO ACCESS_HR_EMPLOYEES;
  5. Test the secure application role.
    1. Connect as the user who will be granted or denied the secure application role.
    2. Run the DBMS_MACSEC_ROLES.SET_ROLE procedure on the role. For example: 
      EXEC DBMS_MACSEC_ROLES.SET_ROLE('ACCESS_HR_EMPLOYEES');
    3. Attempt to perform an action that is controlled by the secure application role. For example: 
      SELECT COUNT(*) FROM HR.EMPLOYEES;

      If the user should be granted the privileges, then the user can perform the action. Otherwise, the action will fail.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.4 Enabling Oracle Database Secure Application Roles to Work with Oracle Database Vault

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. However, you can enable non-Oracle Database Vault roles to work with Oracle Database Vault.
  1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.
  2. Create a new secure application role in Oracle Database Vault and then grant the existing role to the secure application role.
    For example: 
    GRANT myExistingDBrole TO myDVrole;
  3. Modify your code to use this new role.
    You can use DBMS_MACSEC_ROLES.SET_ROLE in your application code to accomplish this.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.5 Modifying a Secure Application Role

You can modify the definition of an Oracle Database Vault secure application role.

  1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.
  2. Find the secure application role and check its definition.
    For example:
    SELECT * FROM DBA_DV_ROLE ORDER BY ROLE;
  3. Run the DBMS_MACADM.UPDATE_ROLE statement.
    For example: 
    BEGIN
 DBMS_MACADM.UPDATE_ROLE(
  role_name      => 'access_hr_employees', 
  enabled        => DBMS_MACUTL.G_NO, 
  rule_set_name  => 'System Access Controls');
END;
/

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.6 Deleting an Oracle Database Vault Secure Application Role

You can delete Oracle Database Vault secure application roles if no applications are using them.

  1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.
  2. Query the DBA_DV_ROLE data dictionary view to find the secure application roles that you want to delete.
    For example: 
    SELECT ROLE FROM DBA_DV_ROLE ORDER BY ROLE;
  3. Check and modify any applications that may be using the secure application role that you want to delete.
  4. Run the DBMS_MACADM.DELETE_ROLE procedure to delete the role.
    For example: 
    EXEC DBMS_MACADM.DELETE_ROLE('access_hr_employees');

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.7 How Oracle Database Vault Secure Application Roles Work

The process flow for an Oracle Database Vault secure application role begins after you create and set the secure application role.

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

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

  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.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.8 Tutorial: Granting Access with Database Vault Secure Application Roles

This tutorial demonstrates how to create a secure application role to control user access to the OE.ORDERS table during work hours.

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.8.1 About This Tutorial

In this tutorial, you restrict the SELECT statement on the ORDERS table in the OE schema to a specific set of users.

Furthermore, these users can only perform these statements on the OE.ORDERS table from within the office, not from a remote connection. To accomplish this, you create an Oracle Database Vault secure application role that is enabled for the user only if the user passes the checks enforced by the rule set that you associate with the secure application role.

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.2 Step 1: Create Users for This Tutorial

First, you must create users for the tutorial.

  1. Log in to a PDB as a user who has been granted the DV_ACCTMGR role.
  2. Create the following user accounts: 
    GRANT CREATE SESSION TO eabel IDENTIFIED BY password;
GRANT CREATE SESSION TO ahutton IDENTIFIED BY password;
GRANT CREATE SESSION TO ldoran IDENTIFIED BY password;

    Replace password with a password that meets the password complexity requirements of the user's profile.

Related Topics

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.3 Step 2: Enable the OE User Account

The OE schema will be used for this tutorial.

  1. In SQL*Plus, connect as the DV_ACCTMGR user.
  2. Check the account status of the OE account. 
    SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
  3. If the OE account is locked and expired, unlock it and assign it a new password. 
    ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;

    Replace password with a password that meets the password complexity requirements of the user's profile.

Related Topics

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.4 Step 3: Create the Rule Set and Its Rules

The rule set and rules will restrict who can modify orders in the OE.ORDERS table.

  1. Connect as a user who has been granted the DV_OWNER role.
  2. Create the following rule set. 
    BEGIN
 DBMS_MACADM.CREATE_RULE_SET(
  rule_set_name    => 'Can Modify Orders', 
  description      => 'Rule set to control who can modify orders in the OE.ORDERS table', 
  enabled          => DBMS_MACUTL.G_YES,
  eval_options     => DBMS_MACUTL.G_RULESET_EVAL_ALL,
  audit_options    => DBMS_MACUTL.G_RULESET_AUDIT_OFF,
  fail_options     => DBMS_MACUTL.G_RULESET_FAIL_SHOW,
  fail_message     => 'Failure',
  fail_code        => 20461,
  handler_options  => DBMS_MACUTL.G_RULESET_HANDLER_OFF,
  handler          => '',
  is_static        => FALSE,
  scope            => DBMS_MACUTL.G_SCOPE_LOCAL);
END;
/
  3. Create the following rule. 
    BEGIN
 DBMS_MACADM.CREATE_RULE(
  rule_name  => 'Check Session User', 
  rule_expr  =>'DVF.F$SESSION_USER IN (''EABEL'',''AHUTTON'')');
END;
/
  4. Add the Check Session User rule to the Can Modify Orders rule set. 
    BEGIN
 DBMS_MACADM.ADD_RULE_TO_RULE_SET(
  rule_set_name => 'Can Modify Orders', 
  rule_name     => 'Check Session User');
END;
/

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.5 Step 4: Create the Database Vault Secure Application Role

The Database Vault secure application role will be set when the rule set conditions are satisfied.

  1. If necessary, connect as the user who was granted the DV_OWNER role.
  2. Create and enable the secure application role, and associate it with the Can Modify Orders rule set. 
    BEGIN
 DBMS_MACADM.CREATE_ROLE(
  role_name      => 'ORDERS_MGMT', 
  enabled        => DBMS_MACUTL.G_YES,
  rule_set_name  => 'Can Modify Orders');
END;
/

At this stage, the Database Vault secure application role and its associated rule set are created, though the role does not yet have any privileges.

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.6 Step 5: Grant the SELECT Privilege to the Secure Application Role

The secure application role must be granted the SELECT privilege.

  1. In SQL*Plus, connect as user OE.
  2. Grant the SELECT privilege to the ORDERS_MGMT secure application role. 
    GRANT SELECT ON ORDERS TO ORDERS_MGMT;

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.7 Step 6: Test the Database Vault Secure Application Role

With all the components in place, you can test the Database Vault secure application role.

  1. Connect as user eabel.
  2. Set the ORDERS_MGMT role. 
    EXEC DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');

    Typically, you would embed this call in the application to which the user logs in.

  3. Select from the OE.ORDERS table. 
    SELECT COUNT(*) FROM OE.ORDERS;

    The following output should appear:

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

    Because user eabel is configured as a valid session user, she can select from the OE.ORDERS table. If user ahutton logs in to SQL*Plus in the same manner, she also can select from the OE.ORDERS table.

  4. Connect as user ldoran.
  5. Enter the following statements:
    EXEC DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
SELECT COUNT(*) FROM OE.ORDERS;

    Because user ldoran is not a valid user, she cannot enable the ORDERS_MGMT role. Therefore, she cannot select from the OE.ORDERS table.

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.8.8 Step 7: Remove the Components for This Tutorial

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

  1. Connect as a user who was granted the DV_OWNER role.
  2. Drop the ORDERS_MGMT secure application role. 
    EXEC DBMS_MACADM.DELETE_ROLE('ORDERS_MGMT');
  3. Remove the Check Session User rule from the Can Modify Orders rule set. 
    BEGIN
 DBMS_MACADM.DELETE_RULE_FROM_RULE_SET(
  rule_set_name => 'Can Modify Orders', 
  rule_name     => 'Check Session User');
END;
/
  4. Drop the rule and rule set. 
    EXEC DBMS_MACADM.DELETE_RULE('Check Session User');
EXEC DBMS_MACADM.DELETE_RULE_SET('Can Modify Orders');
  5. Connect as a user who has been granted the DV_ACCTMGR role.
  6. Drop the users.
    DROP USER eabel;
DROP USER ahutton;
DROP USER ldoran;
  7. If unnecessary, lock and expire the OE user account. 
    ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;

Parent topic: Tutorial: Granting Access with Database Vault Secure Application Roles

8.9 How Secure Application Roles Affect Performance

You can check system performance by using Oracle Database tools, including Oracle Enterprise Manager Cloud Control.

Other tools that you can use are Automatic Workload Repository (AWR) and TKPROF.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault

8.10 Secure Application Role Related Reports and Data Dictionary View

Together, Oracle Enterprise Manager and Oracle Database Vault provide reports and a data dictionary view that are useful for analyzing Oracle Database Vault secure application roles.

Table 8-1 lists the Oracle Database Vault reports.

Table 8-1 Oracle Enterprise Manager Reports Related to Secure Application Roles

Report Description

Secure Application Role Audit Report

Lists audit records generated by the Oracle Database Vault secure application role-enabling operation.

To generate this type of audit record, enable auditing for the rule set associated with the role.

Secure Application Configuration Issues Report

Lists secure application roles that have nonexistent database roles, or incomplete or disabled rule sets

Rule Set Configuration Issues Report

Lists rule sets that have no rules defined or enabled, which may affect the secure application roles that use them

Powerful database accounts and roles reports

Provide information about powerful database accounts and roles

The DBA_DV_ROLE data dictionary view lists the Oracle Database Vault secure application roles used in privilege management.

Related Topics

Parent topic: Configuring Secure Application Roles for Oracle Database Vault