Re: Roles and Privileges

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0203280454.3a82c354_at_posting.google.com>...
> stevenmgarcia_at_hotmail.com (Steve) wrote in message news:<4c049a8c.0203271843.1f74c6fc_at_posting.google.com>...
> > 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?
>
>
> Typically, in a *properly designed* system (where you don't add
> tables and other objects on the fly) *generating* a script to assign
> appropiate privileges to a role, would be a one-time operation.
> Hopefully, that's not too much work for you.
>
> Regards
>
> Sybrand Bakker
> Senior Oracle DBA

Indeed I created my original synonym and grant scripts by generating them on the fly. It wasn't difficult to do. However it just seems awkward..I thought there was a better way to do this.

Thanks, Steve Received on Thu Mar 28 2002 - 19:05:26 CET