| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: VPD Pre/Post Implementation Issues
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
identified>);
(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
Leslie Tierstein
Senior Consultant
Vision Chain, Inc.
The first software to power the demand data network
phone: 202-261-3549
=20
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of VIVEK_SHARMA
Sent: Wednesday, August 18, 2004 12:47 PM
To: oracledba_at_lazydba.com; oracle-l_at_freelists.org
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?
Environment:-
Oracle 9i=3D20
Application Hybrid in nature
Database - Large in Size a few Hundred GB High Concurrent Transactions
Load Periodic Reports Generation=3D20
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlput 'unsubscribe' in the subject line.
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request_at_freelists.org
 |
 |