RE: VPDs or schemas

If you are concerned with being parsed to death, VPD is probably the better option. Assuming the security policy is something reusable, i.e. adding a WHERE clause that checks a BRANCH_NO context value to a BRANCH_NO column in the table, you should only end up with one hard-parse for every statement. If you split things up into multiple schemas, you'll end up hard parsing each statement at least once per schema (assuming I am understanding your solution properly).

With either approach, you will need to come up with a way to differentiate users and to associate them with some branch, so I wouldn't think it would be any more difficult to have users logging in to a different schema or having a context set for them. I'm not sure how you would implement logging in to different schemas for different users, but the approaches I can think of off the top of my head tend to be somewhat brittle.

Given that poorly constructed security policies could kill performance, I would tend to let the developers use VPD but insist on working with them to define "sane" security policies. =20

Justin Cave =20
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Uwe Weber Sent: Monday, March 07, 2005 3:08 PM
To: Oracle-L_at_freelists.org
Subject: VPDs or schemas

Hello,

the DBAs at our site are currently reviewing a design document for=20 a new Web-Application. This application will be used by about 2.500 branch offices and around 10.000 users. Oracle will be 10.n. and estimated size of the DB is about 300G.

Development suggests to use a VPD for every branch office.=20 Further on they want to partition every table with one partition per office. Since the application is web-based, I doubt whether it will be possible to use context sensitive security policies.=20

We (the DBAs) are not really happy with this idea, because we feel that this design wil have a negative impact on=20 performance and scalability. We are especially afraid that the database will parse itself to death.

So we are thinking of suggesting a different design (or more of a sketch

until now) that would use a schema for every branch and take care of data seperation by creating views (MVs where it makes sense) and granting appropiate permissions. Keeping the idea of partitioning the base tables on a per office basis sounds good to me. Of course this will cause more work for the developers :->. =20

Any hints, tips or comments on the merits of both 'designs' are very welcome.

Regards,
uwe
  =20
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Mon Mar 07 2005 - 16:04:24 CST