DA Morgan <damorgan_at_x.washington.edu> wrote in
news:41d31e3f$1_4_at_127.0.0.1:
> 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,
Then the table was created specifically for the upper management... ;-)
Thanks for you suggestion.
Fred
Received on Thu Dec 30 2004 - 23:11:28 CST
