To create the employee user accounts:

1. In Enterprise Manager, access the Database home page for your target database as user SYS with the SYSDBA administrative privilege.

   See Oracle Database 2 Day DBA for more information.

2. From the Administration menu, select Security, then Users.

3. In the Users Page, click Create.

4. In the Create User page, enter the following information:

   • Name: LDORAN (to create the user account Louise Doran)

   • Profile: DEFAULT

   • Authentication: Password

   • Enter Password and Confirm Password: Enter a password that meets the requirements in Requirements for Creating Passwords.

   • Default Tablespace: USERS

   • Temporary Tablespace: TEMP

   • Status: Unlocked

5. Select the Object Privileges tab.

6. From the Select Object Type list, select Table, and then click Add.

7. In the Add Table Object Privileges page, in the Select Table Objects field, enter the following text:

   OE.ORDERS
   

   Do not include spaces in this text.

8. In the Available Privileges list, select SELECT, and then click Move to move it to the Selected Privileges list. Click OK.

   The Create User page appears, with SELECT privileges for OE.ORDERS listed.

9. Click OK.

   The Users page appears, with user ldoran is listed in the User Name column.

10. Select the selection button for user LDORAN, and from the Actions list, select Create Like. Then, click Go.

11. In the Create User page, enter the following information:

    • Name: LPOPP (to create the user account for Finance Manager Luis Popp.)

    • Enter Password and Confirm Password: Enter a password that meets the requirements in Requirements for Creating Passwords.

12. Click OK.

Both employee accounts have been created, and they have identical privileges. If you check the privileges for user LPOPP, you will see that they are identical to those of user LDORAN's. At this stage, if either of these users performs a SELECT statement on the OE.ORDERS table, he or she will be able to see all of its data.

In Tutorial: Creating a Secure Application Role, you created the sec_admin for that tutorial. You can use that account for this tutorial.

If you have not yet created this account, then follow the steps in Step 2: Create a Security Administrator Account to create sec_admin.

User SYS owns this package, so you must log on as SYS to grant these package privileges to sec_admin. The user sec_admin also must have SELECT privileges on the CUSTOMERS table in the OE schema and the EMPLOYEES table in the HR schema.

To grant sec_admin privileges to use the DBMS_RLS package:

1. In Enterprise Manager, access the Database home page and ensure that you are logged in as user SYS with the SYSDBA role selected.

   See Oracle Database 2 Day DBA for more information.

2. From the Schema menu, then Users.

3. In the Users Page, select the SEC_ADMIN user, and in the View User page, click Edit.

4. In the Edit User page, click Object Privileges.

5. From the Select Object Type list, select Package, and then click Add.

6. In the Add Package Object Privileges page, under Select Package Objects, enter SYS.DBMS_RLS so that sec_admin will have access to the DBMS_RLS package.

7. Under Available Privileges, select EXECUTE, and then click Move to move it to the Selected Privileges list.

8. Click OK.

9. In the Edit User page, from the Select Object Type list, select Table, and then click Add.

10. In the Add Table Object Privileges page, in the Select Table Objects field, enter HR.EMPLOYEES so that sec_admin will have access to the HR.EMPLOYEES table.

11. Under Available Privileges, select SELECT, and then click Move to move it to the Selected Privileges list.

12. Click OK.

    The Edit User page appears. It shows that user sec_admin has object privileges for the HR.EMPLOYEES table and DBMS_RLS PL/SQL package. Ensure that you do not select the grant option for either of these objects.

13. Click Apply.

    All the changes you have made, in this case, the addition of the two object privileges, are applied to the sec_admin user account.

To filter the users, the policy function uses the SYS_CONTEXT PL/SQL function to retrieve session information about users who are logging in to the database.

To create the application context and its package:

1. Select Logout to log out of the database instance.

2. In the Confirmation dialog box, select Logout of (Database Instance) and then select the Display login page after logout check box. Then click Logout.

3. Log in as user sec_admin using the NORMAL role.

4. From the Schema menu, select Programs, then Functions.

5. In the Database Login page, log in as user sec_admin with the NORMAL role selected.

6. From the Schema menu, select Programs, and then Functions.

7. In the Functions page, ensure that the Object Type menu is set to Function, and then click Create.

8. In the Create Function page, enter the following information:

   • Name: F_POLICY_ORDERS

   • Schema: SEC_ADMIN

   • Source: Delete the empty function code that has been provided, and then enter the following code (but not the line numbers on the left side of the code) to create a function that checks whether the user who has logged on is a sales representative.

     The f_policy_orders function uses the SYS_CONTEXT PL/SQL function to get the session information of the user. It then compares this information with the job ID of that user in the HR.EMPLOYEES table, for which sec_admin has SELECT privileges.

     (schema in varchar2,
     tab in varchar2)
     return varchar2 
     as 
      v_job_id   varchar2(20);
      v_user     varchar2(100);
      predicate  varchar2(400);
      
     begin
      v_job_id  := null;
      v_user    := null;
      predicate := '1=2';
     
     v_user := lower(sys_context('userenv','session_user'));
     
      select lower(job_id) into v_job_id from hr.employees
        where lower(email) = v_user;
      
      if  v_job_id='sa_rep' then
         predicate := '1=1';
      else 
         null; 
      end if;
     
      return predicate;
     
      exception 
       when no_data_found then 
        null;
     end;

     In this specification:

     • (schema in varchar2, tab in varchar2): Defines parameters for the schema (schema) and table (tab) that must be protected. Notice that the function does not mention the OE.ORDERS table. The ACCESSCONTROL_ORDERS policy that you create in Step 5: Create the ACCESSCONTROL_ORDERS Virtual Private Database Policy uses these parameters to specify the OE schema and ORDERS table. Ensure that you create the schema parameter first, followed by the tab parameter.

     • return varchar2: Returns the string that will be used for the WHERE predicate clause. Always use VARCHAR2 as the data type for this return value.

     • as ... predicate: Defines variables to store the job ID, user name of the user who has logged on, and predicate values.

     • begin ... return predicate: Encompasses the creation of the WHERE predicate, starting the with the BEGIN clause for the v_job_id and v_user settings.

     • v_job_id varchar2(20) and v_user varchar2(100): Sets the v_job_id and v_user variables to null, and the predicate variable to 1=2, that is, to a false value. At this stage, no WHERE predicate can be generated until these variables pass the tests starting with select lower(job_id) into v_job_id.

     • v_user := lower(sys_context...: Uses the SYS_CONTEXT function to retrieve the session information of the user and write it to the v_user variable.

     • select lower(job_id) into v_job_id...end if: Checks if the user is a sales representative by comparing the job ID with the user who has logged on. If the job ID of the user who has logged on is sa_rep (sales representative), then the predicate variable is set to 1=1. In other words, the user, by being a sales representative, has passed the test.

     • return predicate: Returns the WHERE predicate, which translates to WHERE role_of_user_logging_on IS "sa_rep". Oracle Database appends this WHERE predicate onto any SELECT statement that users LDORAN and LPOPP issue on the OE.ORDERS table.

     • exception ... null: Provide an EXCEPTION clause for cases where a user without the correct privileges has logged on.

9. Click OK.

To increase performance, add the CONTEXT_SENSITIVE parameter to the policy, so that Oracle Database only executes the f_policy_orders function when the content of the application context changes, in this case, when a new user logs on. Oracle Database only activates the policy when a user performs a SQL SELECT statement on the ORDERS table. Hence, the user cannot run the INSERT, UPDATE, and DELETE statements, because the policy does not allow him or her to do so.

To create the ACCESSCONTROL_ORDERS Virtual Private Database policy:

1. From the Security menu, select Virtual Private Database Policies.

2. In the Virtual Private Database Policies page, click Create.

3. In the Create Policy page, under General, enter the following:

   • Policy Name: ACCESSCONTROL_ORDERS

   • Object Name: OE.ORDERS

   • Policy Type: Select CONTEXT_SENSITIVE.

     This type reevaluates the policy function at statement run-time if it detects context changes since the last use of the cursor. For session pooling, where multiple clients share a database session, the middle tier must reset the context during client switches. Note that Oracle Database does not cache the value that the function returns for this policy type; it always runs the policy function during statement parsing. The CONTEXT_SENSITIVE policy type applies to only one object.

     To enable the Policy Type, select the Enabled box.

4. Under Policy Function, enter the following:

   • Policy Function: Enter the name of the function that generates a predicate for the policy, in this case, SEC_ADMIN.F_POLICY_ORDERS.

   • Long Predicate: Do not select this box.

     Typically, you select this box to return a predicate with a length of up to 32K bytes. By not selecting this box, Oracle Database limits the predicate to 4000 bytes.

5. Under Enforcement, select the SELECT option and deselect the remaining options that already may be selected.

6. Do not select any options under Security Relevant Columns.

7. Click OK.

   The Virtual Private Database Policies page appears, with the ACCESSCONTROL_ORDERS policy listed in the list of policies.

8. Do not log out of Enterprise Manager.

To test the ACCESSCONTROL_ORDERS policy:

1. Start SQL*Plus.

   From a command prompt, enter the following command to start SQL*Plus, and log in as Sales Representative Louise Doran, whose user name is ldoran:

   sqlplus ldoran
   Enter password: password
   

   SQL*Plus starts, connects to the default database, and then displays a prompt.

   For detailed information about starting SQL*Plus, see Oracle Database 2 Day DBA.

2. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following results should appear for Louise. As you can see, Louise is able to access all the orders in the OE.ORDERS table.

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

3. Connect as Finance Manager Luis Popp.

   CONNECT lpopp
   Enter password: password
   

4. Enter the following SELECT statement:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   The following result should appear, because Mr. Popp, who is not a sales representative, does not have access to the data in the OE.ORDERS table. Because Mr. Popp does not have access, Oracle Database only allows him access to 0 rows.

   COUNT(*)
   --------
          0
   

5. Exit SQL*Plus:

   EXIT