Roles and Privileges

I have a user (say userA) who owns about 70 tables
250 procedures
50 sequences
30 views

and a few other objects. Now I also have another user (say userB) who I want to have access to various schema objects of userA. For instance I want userB to have select, insert, update, and delete on the 70 tables.

Currently I'm doing this by creating a synonym for every table, procedure, view, sequence, etc for userB's account like

create synonym userB.table1 for userA.table1; create synonym userB.table2 for userA.table2;

After I create the synonyms, I assign various privileges to these synonym objects, like

grant select, insert, update, delete on table1 to userB; grant select, insert, update, delete on table2 to userB;

Now as one could imagine my synonym sql file will have hundreds of lines in it, and my grants sql file will also have hundreds of lines in it. Is there an easier way to implement this security by using roles?

For instance, I would love to create a role like

create role selecttable;
grant 'select all tables in userA schema' to selecttable;

Is there a way to do this without using the "any" keyword? I don't want to give this role select on ALL tables in the database, just the tables in userA's schema.

I would like to make this so for all the objects in userA's schema, but not across all the objects in the database.

Is there a way? Received on Thu Mar 28 2002 - 03:43:03 CET