Roles & Privileges

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 - 12:11:03 CDT