Granting exemptions from VPD policies

Normally, once a policy is declared on an object it cannot be bypassed regardless of the user's privileges.

However, there are situations when a user has to have access rights on all data from an object that has a policy applied. In this recipe, we will show how to make an exemption from VPD policies.

In this recipe we will exempt the user HR from all the policies declared within the HR schema.

How to do it...

1. Connect as the user HR and issue a SELECT statement against the VIEW_REG_DATA_VPD view as follows:

   SQL> conn HR
   Enter password:
   Connected.
   SQL> select first_name,last_name from view_reg_data_vpd where phone_number='650.507.9833';
   
   no rows selected
   
   SQL>
   

2. Here on the view view_reg_data_vpd we have the policy "regions_vpd_policy" enforced.

   Grant.
   

3. Connect as system and exempt the HR user from any VPD policy as follows:

   SQL> conn system
   Enter password:
   Connected.
   SQL> GRANT EXEMPT ACCESS POLICY TO HR;
   
   Grant succeeded.
   
   SQL>
   

4. Connect as...