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.

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.

When you create a secure application role, you associate it with a rule set to determine when the role is enabled or disabled.

1. Connect to the PDB as a user who has been granted the DV_OWNER or DV_ADMIN role.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

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. Execute 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;
   /

   In this specification:

   • role_name can be up to 128 characters in mixed-case, without spaces. Ensure that this name follows the standard Oracle naming conventions for role creation using the CREATE ROLE statement. The DBA_DV_FACTOR data dictionary view lists existing factors. This parameter is mandatory. The DBA_DV_ROLE data dictionary view lists existing security roles.
   • enabled enables or disables the role to be available for use. This parameter is mandatory. DBMS_MACUTL.G_YES makes the role available for enabling; DBMS_MACUTL.G_NO prevents the role from being enabled. The default is DBMS_MACUTL.G_YES. That is, users are allowed to call the DBMS_MACSEC_ROLES.SET_ROLE function to try to enable the role. Note that whether or not the role will be enabled depends on the evaluation result of the associated rule set.
   • rule_set_name is a mandatory rule set that the DBMS_MACSEC_ROLES.SET_ROLE procedure will use to determine if the role should be enabled or disabled. If the rule set evaluates to true, then Oracle Database Vault enables the role for the database session. If the rule set evaluates to false, then the role is not enabled. The DBA_DV_RULE_SET data dictionary view lists existing rule sets.
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. Execute 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.

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.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

2. Find the secure application role and check its definition.
   For example:

   SELECT * FROM DBA_DV_ROLE ORDER BY ROLE; 

3. Execute 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;
   /

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.
   For example:

   CONNECT c##sec_admin_owen@pdb_name
   Enter password: password

   To find the available PDBs, query the PDB_NAME column of the DBA_PDBS data dictionary view. To check the current container, run the show con_name command.

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. Execute the DBMS_MACADM.DELETE_ROLE procedure to delete the role.
   For example:

   EXEC DBMS_MACADM.DELETE_ROLE('access_hr_employees');

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.

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

• 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.
• Step 1: Create Users for This Tutorial
  First, you must create users for the tutorial.
• Step 2: Enable the OE User Account
  The OE schema will be used for this tutorial.
• 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.
• 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.
• Step 5: Grant the SELECT Privilege to the Secure Application Role
  The secure application role must be granted the SELECT privilege.
• Step 6: Test the Database Vault Secure Application Role
  With all the components in place, you can test the Database Vault secure application role.
• 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.

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.

Oracle Database Vault provides reports and a data dictionary view that you can use to analyze Oracle Database Vault secure application roles.