Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: VPD Pre/Post Implementation Issues

RE: VPD Pre/Post Implementation Issues

From: Leslie Tierstein <>
Date: Thu, 19 Aug 2004 13:57:45 -0700
Message-ID: <>

Possible issues:

There is a large potential gotcha especially if you are retro-fitting VPD to a database/application that has already been designed/implemented: Make sure that the policy is applied only in the contexts where it is supposed to be applied. Consider, for example, a multi-company implementation where most users are only supposed to see data for their company. There may be, however, some super-users and/or some contexts where the current user has to see things for multiple companies. This functionality will break if the policy is too broad. This is also particularly likely to be a gotcha if multiple systems are accessing the same data.

As for writing the policy, it typically WILL involve some sort of sub-query. In the above case (VPD restricts user to data by company), for example:

SELECT * FROM product p
 WHERE p.company_id IN (SELECT company_id FROM my_user_permissions u

                         WHERE u.company_id =3D p.company_id
                           AND u.user_id =3D <current user, however

(The above implies that the same user may have permissions to view data from many companies; you'll also have to factor this into your analysis.)

The sub-queries will tend to be deeply nested (hence affecting performance), unless you denormalize the database to include the company_id (or similar attribute) in every table, or you are sure that you are only accessing a detail table in the context of a parent table
-- either via a JOIN or row-by-row access via cursors.

You should also think about setting up SYS_CONTEXT variable(s) to hold the identifying characteristic(s), if this means you will be able to formulate a simpler, more efficient SQL clause for the policy.

Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The first software to power the demand data network phone: 202-261-3549


-----Original Message-----

[] On Behalf Of VIVEK_SHARMA Sent: Wednesday, August 18, 2004 12:47 PM To:; Subject: VPD Pre/Post Implementation Issues

AIM - To Set Row level security using VPD on a Centralized Database

Qs What is the Affect on Database performance? Qs Any General Benchmarks on the Affect on performance due to implementation of VPD?
Qs What type of SQL Queries are most impacted SELCT, INSERT, DELETE, UPDATE by VPD?
Qs Based on nature of Tables Transaction, Master, History etc Are there some best practices to follow when creating policies? Qs What are the major Pre/Post implementation issues?

Oracle 9i=3D20
Application Hybrid in nature
Database - Large in Size a few Hundred GB High Concurrent Transactions Load Periodic Reports Generation=3D20

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.

Archives are at
FAQ is at

Please see the official ORACLE-L FAQ:
To unsubscribe send email to:
put 'unsubscribe' in the subject line.

Archives are at FAQ is at
Received on Thu Aug 19 2004 - 15:55:29 CDT

Original text of this message