Implementing Virtual Private Database

A Virtual Private Database (VPD) makes the database tables seem like they belong to one user when multiple users may actually be using that table. The user is only allowed to see the data they have been given permission to see. For instance, assume that I have a table called EMP with the following data:

EMPID ENAME      SAL  DEPTNO
1001  Bob Smith  1000 10
1002  Jane Smith 1500 20
1003  Jack Smith 1200 20

In my company, a manager is only allowed to see the details of the employees in their department. So the manager of Dept 10 can only see Bob's record and the manager of Dept 20 can only see Jane and Jack's records. Yet the data is stored in the same table. This may be needed because the Vice President of Human Resources can see everyone's records. They are not limited by the department number. In either case, they all issue the following query:

SELECT * FROM emp;

Then if the Dept 10 manager issues the query, the VPD automatically rewrites the query to be the following:

SELECT * FROM emp WHERE deptno=10;

The same holds true for the Dept 20 manager, but the predicate in the WHERE clause will change slightly. No matter which application the manager uses (even SQL*Plus), Oracle will rewrite their query and the user will not be able to breach this security mechanism. The VPD will recognize the VP of HR is not limited to any specific department, so the VPD will not rewrite the query for that individual. The data is all stored in one table, the users all issue the same query, but the VPD lets the see only those records they are allowed to see.

The Oracle Security Guide has much more information. I highly recommend reading this entire document. But pay particular attention to Chapters 13 and 14.