Step 6: Test the ACCESSCONTROL_ORDERS Virtual Private Database Policy

At this stage, you are ready to test the accesscontrol_orders policy by logging on as each user and attempting to select data from the ORDERS table.

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 Starting SQL*Plus and Connecting to the Database.

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 results 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