Re: Roles and Privileges

The use of a role would work good for this . But, beware... if userB ever tries to create PlSql using userA's tables , it will not work. Privileges used in Plsql must be granted directly , not thru a role.

I got a long explaination from Oracle one day that sorta made sense. But, every time I think about this later (like now), it doesn't make any sense.

"Steve" <stevenmgarcia_at_hotmail.com> wrote in message news:4c049a8c.0203281005.3f0ce6e9_at_posting.google.com...
> 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 Fri Mar 29 2002 - 06:46:56 CET