Step 7: Test the EMPLOYEE_ROLE Secure Application Role

You are ready to test the employee_role secure application role by logging on as Matthew and Winston and trying to access the OE.ORDERS table. When Matthew and Winston log on, and before they issue a SELECT statement on the OE.ORDERS table, the sec_roles procedure must be executed for the role verification to take place.

To test the employee_role secure application role, as user MWEISS:  

1. Connect as user mweiss.

   CONNECT mweiss
   Enter password: password
   

2. Enter the following SQL statement to run the sec_roles procedure:

   EXEC sec_admin.sec_roles;
   

   This statement executes the sec_roles procedure for the current session. (In a real world scenario, this statement would be automatically run when the user logs in to the application.)

3. Perform the following SELECT statement on the OE.ORDERS table:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   Matthew has access to the OE.ORDERS table:

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

Now, Winston will try to access the secure application.

To test the employee_role secure application role as user WTAYLOR: 

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

   CONNECT wtaylor
   Enter password: password
   

2. Enter the following SQL statement to run the sec_roles procedure:

   EXEC sec_admin.sec_roles;
   

   This statement executes the sec_roles procedure for the current session.

3. Perform the following SELECT statement on the OE.ORDERS table:

   SELECT COUNT(*) FROM OE.ORDERS;
   

   Because Winston does not report directly to Steven King, he does not have access to the OE.ORDERS table. He will never learn the true number of orders in the ORDERS table, at least not by performing a SELECT statement on it.

   ERROR at line 1:
   ORA-00942: table or view does not exist