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

What we do in our environments is to create a regular user role that
has Select, Insert, Update and Delete privileges on the objects owned
by the application schema (maybe execute on procedures/packages). Then
depending on the environment, we will grant that role to the
developers, or the testers or ... regular users in production. I've
once had to create a second role, since in one of the application we
wanted to allow a class of users comming into the database not using
the application (they were coming in using an odbc link) to only be
allowed to view information - so they only had the Select privilege
issued on every table and view. The developers are granted the
resource privilege, on top of the regular user role in the development
database/environment, so they can create objects (tables and views)
under their own schema, and do regular DMLs under the application
schema. Note that developers do not have accounts to login to the
testing database or production, testers don't have accounts in the
development database ...

This works quite well for us.

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@x.washington.edu
(replace 'x' with 'u' to respond)

"GQ" <dbaguy_ott@yahoo.com> wrote in news:1104343037.639250.154170
@c13g2000cwb.googlegroups.com:

> What we do in our environments is to create a regular user role that
> has Select, Insert, Update and Delete privileges on the objects owned
> by the application schema (maybe execute on procedures/packages). Then
> depending on the environment, we will grant that role to the
> developers, or the testers or ... regular users in production. I've
> once had to create a second role, since in one of the application we
> wanted to allow a class of users comming into the database not using
> the application (they were coming in using an odbc link) to only be
> allowed to view information - so they only had the Select privilege
> issued on every table and view. The developers are granted the
> resource privilege, on top of the regular user role in the development
> database/environment, so they can create objects (tables and views)
> under their own schema, and do regular DMLs under the application
> schema. Note that developers do not have accounts to login to the
> testing database or production, testers don't have accounts in the
> development database ...
>
> This works quite well for us.
>
>

It seems to me you have a similiar setup than we have.

And how about the high number of granted object privileges in the DD?
(5 privileges for about 80 objects grated to one role)

Is there another way (shorter) than granting 5 x 80 privilegs to one
role?


TIA
Fred

.... and select from global_name, so they can tell were they connected
too

DA Morgan <damorgan@x.washington.edu> wrote in
news:41d31e3f$1_4@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