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

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. To control access down to that level you should look at Fine Grained Access Control (aka Row Level Security aka Virtual Private Database).

Typically there will be far FEWER roles than there are users.

--
Posted via http://dbforums.com