Re: object privs granted to roles

dbyy wrote:

> hi everyone,
> I'm looking for a solution for the following problem
>
> We have about 80 table objects in one schema (Oracle 9.2) - called it
> "Calc_Apps".
>
> We created few roles for different groups of users (developer, tester,
> bizuser, guests, apps)
> About 100 users accessing this DB.
>
> We would like to grant object privileges from this schema (Calc_Apps) e.g
> to the role 'developer' ("create", "delete", "insert", "select" and
> "update").
>
> My concern is when we would grant these 5 object privileges of all 80
> tables to the role "developer" (and a different selection of privileges
> to the role "tester" a.s.o.) we would have a) the maintenance of these
> privileges could get cumbersome and b) I fear we would blowup the DD too
> much.
>
> On the other hand, we would like to avoid to grant system privileges like
> "create any ..", "delete any ...", "insert any ..." a.s.o. to a
> particular role because this privileges should only be applied to this
> particular schema ("Calc_Apps").
>
> I wonder is there another (easy) way to grant those (object) privileges
> to a role or can you point me to related part of some documentation?
>
> Any reply much appreciated.
> If you need additional details, please let me know
>
> TIA
> Fred

Roles should be built as heirarchies with lower privileged roles inherited into higher privileged roles.

So, for example, the basic role might be READONLY with only the privileges to CREATE SESSION and SELECT <specific tables>.

Then grant that READONLY role to the DEVELOPER role, etc. right up until you get to the UPPERMANAGEMENT role which should contain only CREATE SESSION and no other privilege. Let 'em select from dual. ;-)

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)