| Oracle® Database Vault Administrator's Guide 11g Release 1 (11.1) Part Number B31222-06 |
|
|
View PDF |
| Oracle® Database Vault Administrator's Guide 11g Release 1 (11.1) Part Number B31222-06 |
|
|
View PDF |
In this chapter:
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. Oracle Database Vault automatically creates the secure application role to use invoker's rights. All you need to do is to create the role and then associate it with a rule set. The rule definition should validate the user who is trying to log in.
You can run reports on secure application roles that you create in Oracle Database Vault. See "Related Reports and Data Dictionary View" 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 chapters:
Create a rule set that contains at least one rule to set the conditions that grant or deny the role to the user logging in.
When you create the underlying rule for the rule set, remember that Oracle Database Vault automatically builds in invoker's rights for the secure application role. The rule should validate the user who is trying to log in. If the rule must have more complex code to validate the user, you can create a PL/SQL handler, and then attach it to the rule set. See Chapter 5, "Configuring Rule Sets" for more information about rule sets.
Log in to Oracle Database Vault Administrator using a database account that has been granted the Database Vault Owner (DV_OWNER) role.
At a minimum, you must have the DV_ADMIN role. "Starting Oracle Database Vault Administrator" explains how to log in.
In the Administration page, under Database Vault Feature Administration, click Secure Application Roles.
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 Procedure" for more information about the SET_ROLE function.
In the Create (or Edit) Role page, enter the following settings and then click OK.
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 Procedure" 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.
Users who have database administrative privileges can use the DROP ROLE SQL statement 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.
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 Data Dictionary Views" for more information.
To delete a secure application role:
Check and modify any applications that may be using the secure application role that you want to delete.
In the Oracle Database Vault Administration page, select Secure Application Roles.
In the Secure Application Roles page, select the role that you want to remove.
Click Remove.
In the Confirmation page, click Yes.
The process flow for a secure application role that is managed by Oracle Database Vault is as follows:
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 Procedures Within DVSYS.DBMS_MACADM" for more information.
Modify your application to call the role, by using the DVSYS.DBMS_MACSEC_ROLES.SET_ROLE function.
See "SET_ROLE Procedure" for more information.
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.
In this tutorial, you will restrict the SELECT SQL 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 will create an Oracle Database Vault secure application role that will be granted to the user only if the user passes the checks enforced by the rule set that you associate with the secure application role.
In this tutorial:
Log in to SQL*Plus as the Database Vault Account Manager.
For example:
sqlplus dbvacctmgr Enter password: password
Create the following user accounts:
SQL> CREATE USER eabel IDENTIFIED BY password; SQL> CREATE USER ahutton IDENTIFIED BY password; SQL> CREATE USER ldoran IDENTIFIED BY password;
Connect as SYS using the SYSDBA privilege, and then grant these users the CREATE SESSION privilege.
SQL> CONNECT SYS/AS SYSDBA Enter password: password SQL> GRANT CREATE SESSION TO eabel, ahutton, ldoran;
In SQL*Plus, connect as the Database Vault Account Manager.
For example:
SQL> CONNECT dbvacctmgr
Enter password: password
Check the account status of the OE account.
SQL> SELECT USERNAME, ACCOUNT_STATUS FROM DBA_USERS WHERE USERNAME = 'OE';
If the OE account is locked and expired, unlock it and assign it a new password.
SQL> ALTER USER OE ACCOUNT UNLOCK IDENTIFIED BY password;
Log in to Oracle Database Vault Administrator using a database account that has been granted the Database Vault Owner (DV_OWNER) role.
"Starting Oracle Database Vault Administrator" explains how to log in.
In the Administration page, select Rule Sets.
The Rule Sets page appears.
Click Create.
The Create Rule Set page appears.
Enter the following information:
Name: Can Modify Orders
Description: Rule set to control who can modify orders in the OE.ORDERS table
Leave the remaining settings and their defaults, and then click OK.
In the Rule Sets page, select the Can Modify Orders rule set, and then click Edit.
The Edit Rule Set: Can Modify Orders page appears.
Scroll to the bottom of the page and under Rules Associated To The Rule Set, click Create.
Create the following two rules:
| Rule Name | Rule Expression | |
Check IP Address |
DVF.F$CLIENT_IP = '123.45.67.89' |
|
Check Session User |
DVF.F$SESSION_USER IN ('EABEL', 'AHUTTON') |
For the Check IP Address rule, replace 123.45.67.89 with the IP address for your own computer. In a real-world scenario, you would create an expression that includes all the IP addresses for the users who should be allowed access.
Both of these rules use the default factors Client_IP and Session_User. See "Default Factors" for more information about these factors. If these factors have been removed or modified, you can use the following rule expressions instead:
Check IP Address: UPPER(SYS_CONTEXT('USERENV','IP_ADDRESS')) = '123.45.67.89'
Check Session User: UPPER(SYS_CONTEXT('USERENV','SESSION_USER')) IN ('EABEL', 'AHUTTON')
Ensure that the Status setting for the Can Modify Orders table is set to Enabled and Evaluation Options is set to All True.
Click OK.
In Oracle Database Vault, return to the Administration page.
Under Administration, select Secure Application Roles.
The Secure Application Roles page appears.
Click Create.
The Create Role page appears.
In the Role box, enter ORDERS_MGMT to name the role.
Under Rule Set, select Can Modify Orders.
Click OK.
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. Remember that you do not need to create the role using invoker's rights: Oracle Database Vault includes this automatically in the secure application role creation.
In SQL*Plus, connect as user OE.
SQL> CONNECT OE
Enter password: password
Grant the SELECT privilege to the ORDERS_MGMT Database Vault Secure application role.
SQL> GRANT SELECT ON ORDERS TO ORDERS_MGMT;
In SQL*Plus, connect directly to the database as user eabel.
SQL> CONNECT eabel@orcl
Enter password: password
Replace orcl with the name of your database instance.
Set the ORDERS_MGMT role.
SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
Typically, you would embed this call in the application that the user logs in to.
Select from the OE.ORDERS table.
SQL> SELECT COUNT(*) FROM OE.ORDERS;
COUNT(*)
----------
105
Because user eabel is logging directly into the database from the correct IP address and is listed 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 will be able to select from the OE.ORDERS table.
Reconnect as user eabel without specifying the database instance, and then try to select from the OE.ORDERS table again.
SQL> CONNECT eabel
Enter password: password
SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
Error at line 1:
ORA-47305: Rule Set Violation on SET ROLE (Can Modfiy Orders)
...
SQL> SELECT COUNT(*) FROM OE.ORDERS;
ERROR at line 1:
ORA-00942: table or view does not exist
Even though user eabel is a valid user, she has violated the Check IP Address rule in the rule set, so she is not granted the ORDERS_MGMT role. The only way for the IP address to be recognized is to connect by specifying the database instance, as user eabel did in Step 1. (For an explanation about how this works, see Step 7 in "Step 3: Map the Domain Factor Identities to the Client_IP Factor", in Chapter 7.)
Connect as user ldoran and then enter the following statements:
SQL> EXEC DVSYS.DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
SQL> SELECT COUNT(*) FROM OE.ORDERS;
Because user ldoran is not a valid user, she is not granted the ORDERS_MGMT role. Therefore, she cannot select from the OE.ORDERS table.
Log into SQL*Plus as the Database Vault Owner.
For example:
SQL> CONNECT dbvowner
Enter password: password
Delete the ORDERS_MGMT secure application role.
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_ROLE('ORDERS_MGMT');
Enter the following commands in the order shown to remove the Can Modify Orders rule set.
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE_FROM_RULE_SET('Can Modify Orders', 'Check IP Address');
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE_FROM_RULE_SET('Can Modify Orders', 'Check Session User');
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE('Check IP Address');
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE('Check Session User');
SQL> EXEC DVSYS.DBMS_MACADM.DELETE_RULE_SET('Can Modify Orders');
SQL> COMMIT;
Connect as the Database Vault Account Manager and drop the users.
For example:
SQL> CONNECT dbvacctmgr
Enter password: password
SQL> DROP USER eabel;
SQL> DROP USER ahutton;
SQL> DROP USER ldoran;
If uneccessary, lock and expire the OE user account.
SQL> ALTER USER OE ACCOUNT LOCK PASSWORD EXPIRE;
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.
Table 8-1 lists Oracle Database Vault reports that are useful for analyzing Oracle Database Vault secure application roles. See Chapter 16, "Oracle Database Vault Reports" for information about how to run these reports.
Table 8-1 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" |
Provides information about powerful database accounts and roles |
You can use the DBA_DV_ROLE data dictionary view to find the Oracle Database Vault secure application roles used in privilege management. See "DBA_DV_ROLE View" for more information.
