Secure Application Roles
A secure application role is a database role that can be enabled by using only a specific, declared PL/SQL procedure. This procedure is usually written by an application developer. In Database Vault, the procedure is implemented by the Database Vault packages. Database Vault changes the implementation of secure application roles to ease their administration, development, and use. You can create secure application roles that are enabled based on the outcome of a rule set. For example, the application can set the role if the associated rule set evaluates to TRUE. If the associated rule set evaluates to FALSE, do not allow the role to be set.
The secure application role is designed to be enabled from an application. In Database Vault, any application or user can execute the SET_ROLE function from the API. Database Vault controls the ability to enable the role with an associated rule set. A secure application role can be assigned system and object privileges just as any other role. In Database Vault, the role is created with separation of duty in mind. System privileges must be assigned by a user with the DBA role and object privileges are assigned for realm objects by a user with the DV_REALM_OWNER role for the specific realm.
Secure application roles provide a way to grant privileges to users only when certain conditions are met. These conditions can be as simple as when connected through a named application or as complex as privileges that are dependent on the user’s job role and the client’s IP address. The user or the application can call the SET_ROLE function. If the rule set returns TRUE, the role is enabled for the user. After the role is enabled, it remains enabled for the duration of the session.
Do not use factors that are evaluated By Access for secure application roles. If the rule set is TRUE when the SET_ROLE function is called, the role is enabled. The role remains enabled even if the factor changes so that the rule set is FALSE.
Using a Secure Application Role
The secure application role is a secure method for allowing the user to be authorized only when the role is enabled. Before the secure application role was introduced in Oracle9i Database, a role could be protected by a password. This role could be enabled from an application with a password, but it required that the password be embedded in the application code. With Oracle9i Database, a secure application role could be enabled by using a secure PL/SQL package. Database Vault enables the security administrator to create a secure application role that is validated by a rule set.
In all the versions, the same general procedure is followed in the application.
Secure Application Role Changes in Database Vault
As in previous releases, a secure application role is enabled by a procedure in a secure package. With Database Vault, the procedure is provided, so you do not have to write it. Instead of writing a procedure, the security administrator uses a rule set to determine whether an application role should be enabled.
The role is automatically created by Cloud Control and can be seen in the DBA_APPLICATION_ROLES view. The package that secures this role is listed in this view as DVSYS.DBMS_MACSEC_ROLES. Instead of granting EXECUTE on the DBMS_SESSION.SET_ROLE procedure to the application account and calling the procedure from the application, call the publicly executable DVSYS.DBMS_MACSEC_ROLES.SET_ROLE procedure to set the role.
Tasks with Secure Application Roles
1. Create a rule set that returns TRUE.
2. For example, the Local_subnet rule has the following rule expression:
DVF.F$CLIENT_IP LIKE '139.185.35.%'
3. Give the role a name. Then assign a rule set to control the role. You can choose only from the already created rule sets.
To create or delete Secure Application Roles use Cloud Control or the Database vault APIs.
The configuration for this example is as follows:
1. Connect as the bea_dvacctmgr user with the DV_ACCTMGR role and the HR user who is an owner in a realm that protects the HR schema.
SQL> connect HR/
Connected. SQL> grant select on hr.employees to hr_emp_clerk; Grant succeeded.
2. Attempt to enable the role from a machine that is not on the local subnet:
SQL> connect kpartner/q1_w2_e3@otherdb Connected. SQL> select * from hr.employees; select * from hr.employees * ERROR at line 1: ORA-01031: insufficient privileges
SQL> exec DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('HR_EMP_CLERK'); BEGIN DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('HR_EMP_CLERK'); END; * ERROR at line 1: ORA-47305: Rule Set Violation on SET ROLE (ALLOW_LOCAL_SUBNET_ACCESS) ORA-06512: at "DVSYS.DBMS_MACUTL", line 37 ORA-06512: at "DVSYS.DBMS_MACUTL", line 359 ORA-06512: at "DVSYS.DBMS_MACSEC", line 215 ORA-06512: at "DVSYS.ROLE_IS_ENABLED", line 4 ORA-06512: at "DVSYS.DBMS_MACSEC_ROLES", line 19 ORA-06512: at line 1
SQL> select dvf.F$Client_IP from DUAL; F$CLIENT_IP -------------------------------------------- 220.127.116.11
SQL> select * from hr.employees 2 where employee_id = 107; select * from hr.employees * ERROR at line 1: ORA-01031: insufficient privileges
Attempt to enable the role from a machine that is on the local subnet:
SQL> connect kpartner/q1_w2_e3@orcl Connected. SQL> select dvf.f$client_ip from dual; F$CLIENT_IP --------------- 18.104.22.168
SQL> exec DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('HR_EMP_CLERK'); PL/SQL procedure successfully completed.
SQL> select first_name, Last_name, email, salary 2 from hr.employees 3 where employee_id = 107; FIRST_NAME LAST_NAME EMAIL SALARY ---------- ---------- -------- ----------- Diana Lorentz DLORENTZ 4,200.00
Reports and Views
Reports and a View Related to Secure Application Roles:
- Secure Application Role Audit Report: Lists audit records generated by the 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: Provides information about powerful database accounts and roles.
- You can use the DBA_DV_ROLE data dictionary view to find the Database Vault secure application roles used in privilege management.
Maintaining Secure Application Roles
You can maintain Secure Application Roles with Cloud Control or with the DVSYS.DBMS_MACADM package:
- CREATE_ROLE: Creates a Database Vault secure application role.
- DELETE_ROLE: Deletes a Database Vault secure application role.
- RENAME_ROLE: Renames a Database Vault secure application role. The name change takes effect wherever the role is used.
- UPDATE_ROLE: Updates a Database Vault secure application role.
The run-time API for use by applications is provided in a separate package to provide better security. The DVSYS.DBMS_MACSEC_ROLES package provides the following functions:
- CAN_SET_ROLE(‘[role]’): Checks whether the user invoking the method is authorized to use the specified Database Vault secure application role. Returns a BOOLEAN value.
‘): Issues the SET ROLE command for a Database Vault secure application role.