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.

First, you must create users for the tutorial.

1. Log in to SQL*Plus as a user who has been granted the DV_ACCTMGR role.

   For example:

   sqlplus bea_dvacctmgr
   Enter password: password
   

   In a multitenant environment, you must connect to the appropriate pluggable database (PDB).

   For example:

   sqlplus bea_dvacctmgr@hrpdb
   Enter password: password
   

   To find the available PDBs, query the DBA_PDBS data dictionary view. To check the current PDB, run the show con_name command.

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;
   

   Follow the guidelines in Oracle Database Security Guide to replace password with a password that is secure.

The OE schema will be used for this tutorial.

1. In SQL*Plus, connect as the DV_ACCTMGR user.

   For example:

   CONNECT bea_dvacctmgr -- Or, CONNECT bea_dvacctmgr@hrpdb
   Enter password: password
   

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;

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

1. Log in to Oracle Database Vault Administrator from Cloud Control as a user who has been granted the DV_OWNER or DV_ADMIN role and the SELECT ANY DICTIONARY privilege. Logging in to Oracle Database Vault from Oracle Enterprise Cloud Control explains how to log in.
2. In the Administration page, select Rule Sets.

   The Rule Sets page appears.

3. Click Create.

   The Create Rule Set page appears.

4. Enter the following information:

   • Name: Enter Can Modify Orders.

   • Description: Enter Rule set to control who can modify orders in the OE.ORDERS table.

   • Status: Select Enabled.

   • Evaluation Options: Select All True.

5. Leave the remaining settings and their defaults, and then click Next to go to the Associate with Rules page.
6. Click Create Rule and in the Create Rule dialog box, enter the following settings:

   • Name: Check IP Address

   • Expression: DVF.F$CLIENT_IP = 'your_IP_address'

   For the Check IP Address rule, replace your_IP_address with the IP address for your 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.

   This rule uses the default factor Client_IP. If this factor has been removed, then you can use the following rule expression instead:

   UPPER(SYS_CONTEXT('USERENV','IP_ADDRESS')) = 'your_IP_address'
   

7. Click OK.
8. Click Create Rule again and in the Create Rule dialog box, enter the following settings:

   • Name: Check Session User

   • Expression: DVF.F$SESSION_USER IN ('EABEL','AHUTTON')

   This rule uses the default factor Session_User. If this factor have been removed or modified, you can use the following rule expression instead:

   UPPER(SYS_CONTEXT('USERENV','SESSION_USER')) IN ('EABEL','AHUTTON')
   

9. Click OK.
10. Click Done, then click Finish.

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

1. In Oracle Database Vault, return to the Administration page.
2. Under Administration, select Secure Application Roles.

   The Secure Application Roles page appears.

3. Click Create.

   The Create Role page appears.

4. In the Role box, enter ORDERS_MGMT to name the role.
5. Under Rule Set, select Can Modify Orders.
6. Click OK.

The secure application role must be granted the SELECT privilege.

1. In SQL*Plus, connect as user OE.

   CONNECT OE -- Or, CONNECT OE@hrpdb
   Enter password: password
   

2. Grant the SELECT privilege to the ORDERS_MGMT Database Vault Secure application role.

   GRANT SELECT ON ORDERS TO ORDERS_MGMT;

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

1. In SQL*Plus, connect directly to the database as user eabel.

   connect eabel@orcl
   Enter password: password
   

   Replace orcl with the name of your database instance.

2. Set the ORDERS_MGMT role.

   EXEC DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
   

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

3. Select from the OE.ORDERS table.

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following output should appear:

     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 can select from the OE.ORDERS table.

4. Reconnect as user eabel without specifying the database instance, and then try to select from the OE.ORDERS table again.

   CONNECT eabel 
   Enter password: password
   
   EXEC DBMS_MACSEC_ROLES.SET_ROLE('ORDERS_MGMT');
   

   The following output should appear:

   Error at line 1: 
   ORA-47305: Rule Set Violation on SET ROLE (Can Modfiy Orders)
   ...
   

   Next:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following output should appear:

   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 cannot enable 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 9 in Step 3: Map the Domain Factor Identities to the Client_IP Factor, in Configuring Factors.)

5. Connect as user ldoran.

   CONNECT ldoran -- Or, CONNECT ldoran@hrpdb
   Enter password: password
   

6. 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.

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

1. Log in to Oracle Database Vault Administrator from Cloud Control as a user who has been granted the DV_OWNER or DV_ADMIN role and the SELECT ANY DICTIONARY privilege. Logging in to Oracle Database Vault from Oracle Enterprise Cloud Control explains how to log in.
2. Delete the ORDERS_MGMT secure application role: From the Secure Application Roles page, select the ORDERS_MGMT secure application role, and then click Delete, and then Yes in the Confirmation dialog box.
3. Select the Rule Sets page, select the Can Modify Orders rule set, and then click Delete.
4. In the Confirmation dialog box, select Yes to remove the rule set.
5. Select the Rules page, select the Check IP Address and Check Session User rules, and then select Delete. Select Yes in the Confirmation box.

   Hold the Control key down to select multiple rules.

6. In SQL*Plus, connect as the Database Vault Account Manager and drop the users.

   For example:

   CONNECT bea_dvacctmgr -- Or, CONNECT bea_dvacctmgr@hrpdb
   Enter password: password
   
   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;