Granting users access to another user's objects

Hi all!

I am about to implement a more sophisticated security policy at our shop (to be more precise: so far, there is none....).

The user SCOTT owns all the objects for our application (i.e. tables, sequences, procedures, functions).

Now I want to give the users PETE, MARC and JOE access to select and insert into SCOTT's objects.

I create a role R_SCOTT, log in as SCOTT and grant insert and select on all tables to that role. Then I grant select and alter privileges on the sequences to the role as well. (Some tables have LOB colums - i.e. LOB segments - but that should not make a difference, right?).

After that I grant execute privileges on all procedures and functions to PETE, MARC and JOE directly (since they can't be granted through a role, right?).

Then I log in as a DBA and create public synonyms for the tables and sequences in the SCOTT schema, e.g. create public synonym tablename for scott.tablename.

The question is: do I also have to create public synonyms for the packages and procedures as well? PETE, JOE and MARC will access the procedures only through their name (i.e. not specifying execute scott.procedurename).

Do I have to grant rights on sequences to the user or can this be done through a role?

This is 8.1.6 on Win2k. Received on Tue Dec 05 2000 - 18:50:03 CST