EMPID ENAME SAL DEPTNO 1001 Bob Smith 1000 10 1002 Jane Smith 1500 20 1003 Jack Smith 1200 20In 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.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.