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

Mark D Powell wrote:

> peine_at_iese.fraunhofer.de (Holger Peine) wrote in message
> news:<cd219406.0306040154.2dca5d1d_at_posting.google.com>...

>> 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?

Go ahead, that's why I was asking ;-)

> but I think your plan needs
> to be re-thought. Consider that roles are collections of privileges
> and are generally best used when multiple users will need the same set
> of privileges. This means only one grant from the object owner needs
> to be issued to the role to support perhaps hundreds of users. This
> reduces the total amount of information stored in the dictionary.

Agreed.

> Your idea to have a role for every end-user goes against this; you
> might as well use direct grants.

The idea was not one role per user containing only that one user (that would indeed be obviously redundant), but one role per user containing 1-5 users each (intention: the set of users who are allowed to act as a substitute for this user, which will of course always include the user himself, plus a few other users). It still sounds plausible to me to model such a substitution-in-rights relationship with a role - am I wrong here?

However, that was only the smaller part of my total roles count, the larger part was the roles intended for row protection. This will not work, as I know now (see the other posting recommending Virtual Private DBs instead and my reply to that).

> Also web servers normally use on ID because they pool the connections
> to the DB keeping the connections open between uses. Connecting and
> disconnecting for every SQL call is generally expensive from a
> performance point of view if your web server.

I'm aware of that. I was planning to use lightweight sessions via the Oracle Call Interface (OCI) to multiplex the multiple user sessions into one physical DB connection.

> Your web server (BEA) may not lend itself to even working like this.

I haven't checked this yet, but I really should check whether BEA supports OCI - thanks for the hint.

> But our web people set
> up one Id per logical web server. We have one for every major
> application area: Warranty, Manufacturing, ... Each of these areas
> has a logical server that can be shut down and restarted without
> affecting the other application. So we have a half-dozen roles

Yes, that seems to be the standard way to do it. As I said above, I now know that I won't use roles for row protection, but (probably) Virtual Private Databases.

However, to model the user substitution relationship, roles still seem like a natural concept to me. Although Andy Hassall (AH) pointed out an implementation limit which is not _too_ far when estimating 25 concurrent users sessions (i.e. 25 active "substitution" roles per session) for my application:

AH> >There also is a hardcoded upper limit of 128 or 512 roles per
AH> >instance.
AH> Not according to the documentation, which states 2,147,483,638 users and
AH> roles. There's the 148 max_enabled_roles limit, but that's per session, 
AH> not instance.

> Good luck with whatever you determine to do.

Thanks for your help with it so far :-)

-- 
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