Re: Roles & Privileges

A copy of this was sent to "Mark Wallace" <mwallace*NOSPAM*@*NOSPAM*carr-sheppards.co.uk> (if that email address didn't require changing) On Tue, 22 May 2001 18:11:03 +0100, you wrote:

>I am at the start of setting up a new system. What I want to do is have all
>the application tables, indexes, packages etc, etc... in a central
>application schema. For example called 'Applic'
>This schema has its own tablespace (ApplicData_ts) and temporary tablespace
>(ApplicTemp_ts).
>
>I have now created a couple of users (usera, userb etc...) also using the
>same tablespaces.
>The users must have no rights to create any of there own database objects.
>But must have rights to select, update, execute etc, etc... from the
>applications tables.
>
>How do setup a user to automatically have access to all 'Applic' schemas
>object without having to specify each object individualy as in,
>grant select on tablea to usera
>grant select on tableb to usera
>grant update on tablea to usera
>etc .etc .etc..... for all DB Objects that the application uses.
>
>Do I grant the privileges to a role and then the role to the users. But
>still have to grant each privilege individualy to the role?
>Is there a better way of doing this.
>
>Any suggestions or comments appreciated.
>
>Thanks
>Mark
>

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:646423863863 for a method that uses a DDL event trigger to schedule a job to grant select on newly created objects in a schema. So, if you want USERA to have select on any table in some schema -- even newly created ones -- this supplies an approach.

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://asktom.oracle.com/
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp