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

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?

Holger, I do not want to sound critical, 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. Your idea to have a role for every end-user goes against this; you might as well use direct grants.

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. Your web server (BEA) may not lend itself to even working like this. 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

Good luck with whatever you determine to do.

HTH -- Mark D Powell -- Received on Wed Jun 04 2003 - 08:25:12 CDT