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@(protected)
[mailto:oracle-l-bounce@(protected)] On Behalf Of VIVEK_SHARMA
Sent: Wednesday, August 18, 2004 12:47 PM
To: oracledba@(protected); oracle-l@(protected)
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
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected) put
'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ------
To unsubscribe send email to: oracle-l-request@(protected)
put 'unsubscribe ' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- --
