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.
- Connect as the user
HRand issue aSELECTstatement against theVIEW_REG_DATA_VPDview 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>
- Here on the view
view_reg_data_vpdwe have the policy"regions_vpd_policy"enforced.Grant. - Connect as
systemand exempt theHRuser from any VPD policy as follows:SQL> conn system Enter password: Connected. SQL> GRANT EXEMPT ACCESS POLICY TO HR; Grant succeeded. SQL>
- Connect as
HRand again issue theSELECTstatement from the step 1 as follows:SQL> select first_name,last_name from view_reg_data_vpd where phone_number='650.507.9833'; FIRST_NAME LAST_NAME -------------------- ------------------------- Donald OConnell SQL>
- Now the
HRuser is exempted from any policy and theSELECTstatement returns data.
Usually exemptions are given to users who may create reports and users who are performing back ups by using expdp or exp. It is highly recommended to implement a mechanism used to grant policy exemption using contexts and secure application roles or other application logic, and to try to refrain, whenever possible, from explicitly granting policy exemption to users. In other words, ensure that you control the exemption within the application code and not Oracle.
It is highly recommended to implement auditing on operations performed by users exempted from VPD policies. For more about auditing see Chapter 8, Tracking and Analysis – Database Auditing.