Re: 500 users, 10,000 roles: Performance problems to be expected?

From: Dr. Holger Peine <peine_at_iese.fraunhofer.de>
Date: Tue, 10 Jun 2003 08:32:32 +0200
Message-ID: <3ee57b89@news.fhg.de>

andrewst wrote:

> 
> Originally posted by Dr. Holger Peine

>> andrewst wrote:
>>
>> >
>> > Originally posted by Holger Peine
>> >> Hello,
>> >> I'm designing the user/role/privilege security for an existing
>> Oracle
>> >> 8i database accessed through an application server (Bea
>> Weblogic). So
>> >> far, the AS uses one DB user for all accesses, i.e. end users
>> are
>> >> managed in the AS only, not in the DB.
>> >> I plan to change that and make a dedicated account for each end
>> user
>> >> (about 500) plus maybe 10,000 roles (one role per user and a
>> few roles
>> >> per row in the central data table, which is expected to hold a
>> few
>> >> thousand rows). Of the 500 users, maybe 20 are connected at the
>> same
>> >> time (on the average). Is this sensible from point of view
>> of
>> >> performance?
>> > "a few roles per row"??? What is THAT all about?
>>
>> One or two roles per row: One role "Users allowed to write this row"
>> and sometimes a second role "Users allowed to read this row".
>> Does that make sense?
>>
>> >
>> > --
>> > Posted via
>> http://dbforums.com/http://dbforums.com
>>

> That's not how roles work. A role can only have access privileges > granted at the object level (i.e. whole table, view) not at row level.

Thanks for the clear information. I was not aware of this limitation (if my original question did not make it clear, my database knowledge was purely theoretical until lately - I'm a security expert now asked for a DB security design). OK, so my initial idea of using roles for row-level protection will not work (I was already suspicious of that, that's why I asked). Thanks again.

> To control access down to that level you should look at Fine Grained > Access Control (aka Row Level Security aka Virtual Private Database).

Yes, I've read about VPDs (that's how Oracle 8.1.7 calls it, anyway). Seems like this is the way to go here.

• So, is it feasible to have 500 (or maybe 1000) users, each with their own personal VPD (i.e. subset of rows they are allowed to read and write)?

Thanks for your advice,
Holger.

-- 
Dr. Holger Peine
Fraunhofer IESE, Kaiserslautern, Germany
Phone +49-6301-707-134, Fax -200 (shared)
www.iese.fraunhofer.de/Staff/peine    -- PGP key on request or via 
pgp.mit.edu

Received on Tue Jun 10 2003 - 01:32:32 CDT