Creating Oracle Virtual Private Database row-level policies

Oracle VPD row-level policies restrict users' access per row for a protected object. This means that two users who execute the same query against, for example, a table may, as a result, receive different number of rows.

Getting ready

See the Getting ready section of the recipe Creating different policy functions.

How to do it...

1. Connect to the database as a user who has appropriate privileges (for example, the user maja):

   $ sqlplus maja
   

2. Create a VPD policy (for example, test_pol1) that protects the hr.emp_vpd_test table in the following way: it restricts SELECT operation based on a policy function (for example, no_access).

   

3. To test VPD policy created in the previous step, connect as the user susan to the database (keep in mind that she has the SELECT ANY TABLE privilege) and try to access data in the table hr.emp_vpd_test.

   

   Figure 17 - Susan can't access data

4. Connect to the database as a user who can create a VPD policy (for example, user...