Re: Roles & Privileges

Mark,
 Grant select,insert,update,delete on tables to a role or roles if you want to segregate select from dml operations. Grant the roles to the users.

Build the grants from the data dictionary. e.g. select ' grant select on ' || table_name || ' to your_role;' from dba_objects where owner='APPLIC';

grant your_role to user_a;

"Mark Wallace" <mwallace*NOSPAM*@*NOSPAM*carr-sheppards.co.uk> wrote in message news:W4xO6.305$3S3.6269_at_news.lhr.globix.net...
> 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
>
>
Received on Tue May 22 2001 - 14:27:19 CDT