Step 5: Test the OE Protections Realm

Now that you have created a realm to protect the OE schema, you are ready to test it. You do not need to restart the database session, because any protections you define in Oracle Database Vault take effect right away.

To test the OE Protections realm: 

1. Connect to SQL*Plus as user SYS using the SYSDBA privilege.

   CONNECT SYS/AS SYSDBA
   Enter password: password
   Connected.
   

2. Try selecting from the OE.CUSTOMERS table.

   SELECT COUNT(*) FROM OE.CUSTOMERS;
   

   The following output should appear:

   ERROR at line 1:
   ORA-01031: insufficient privileges
   

   The OE Protections realm prevents the administrative user from accessing the OE.CUSTOMERS table. Because you defined the OE Protections realm to protect the entire schema, the administrative user does not have access to any of the other tables in OE, either.

3. Connect as user SCOTT.

   CONNECT SCOTT
   Enter password: password
   Connected.
   

4. Try selecting from the OE.CUSTOMERS table.

   SELECT COUNT(*) FROM OE.CUSTOMERS;
   

   The following output should appear:

     COUNT(*)
   ----------
          319
   

   The OE Protections realm does not apply to user SCOTT because user OE has explicitly granted this user the SELECT privilege on the OE.CUSTOMERS table. Oracle Database Vault sets up the protections you need, but does not override the explicit privileges you have defined. SCOTT still can query this table.