Re: Synonyms are a pain!

From: Robert Wagner <RobertWagner_at_alum.mit.edu>
Date: 2000/05/08
Message-ID: <_6DR4.7564$J81.46291@newsr1.maine.rr.com>#1/1

Sybrand:

What makes me very nervous is the following statement on pg 467 of "Oracle 8: The Complete Reference":

>>

Privileges that are granted to users via roles cannot be used as the basis for views, procedures, functions, packages, or foreign keys. When creating these types of database objects, you must rely on direct grants of the necessary privileges.
<<<

Let's just talk about Views, to keep it simple. Does this statement that I can't EXECUTE a view where that privilege has been granted through a role?

---

Stepping back from my specific problem: There seems to a distinct lack of information about how to handle this problem, but it must be something that many (most?) developers deal with: How to allow a distinct group of users access to a specific database. Obviously, Roles, Privileges, Synonyms, etc. are all involved here. But I haven't found any book which talks about the best ways to make everything work together to accomplish the goal.

Any comments?

Thanks >>>> Robert

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:957554932.15915.0.pluto.d4ee154e_at_news.demon.nl...
> Robert,
>
> Several remarks about this:
> - grants (don't forget select) usually go to a role, not to an user
> directly.
> If you need pl/sql to manipulate data, usually the pl/sql is owned by the
> owner of the tables (and of course: needs synonyms and execute grants)
> - the way the parser works, private synonyms are resolved before public
> synonyms, so private synonyms are going to be faster
> - I once read the optimizer would consider different synonyms as different
> objects, hence it would not reuse any existing sql. I have never been able
> to find proof for that
> - the advantage arises when you have a production and a development schema
> in your database, in that case private synonyms are very handy.
> - I once wrote a script which did create synonyms automatically based on
> grants without connecting to the target user. I wrote that script at a
> customer, I don't work there anymore, and I switched systems at home, so I
> probably really need to dig deep. Using that script, regenerating private
> synonyms simply was pushing a button. So no administrative burden.
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>
>
>
> Robert Wagner <RobertWagner_at_alum.mit.edu> schreef in berichtnieuws
> qgEQ4.6076$J81.41652_at_newsr1.maine.rr.com...
> > Sybrand:
> >
> > I'm a little uncertain about private synonyms. I'd have to create a set
 for
> > each user, right? Then I'd have to grant the usual privileges (INSERT,
> > DELETE, UPDATE, DELETE) to each user.
> >
> > If this is all correct, with 10-30 possible users, does it make any
 sense
 to
> > use private synonyms?
> >
> > Thanks very much >>> Robert
> >
> > ----- Original Message -----
> > From: "Sybrand Bakker" <postbus_at_sybrandb.demon.nl>
> > Newsgroups: comp.databases.oracle.server
> > Sent: Thursday, May 04, 2000 3:53 PM
> > Subject: Re: Synonyms are a pain!
> >
> >
> > "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
> > news:957469924.8355.0.pluto.d4ee154e_at_news.demon.nl...
> > >
> > > Robert Wagner <RobertWagner_at_alum.mit.edu> schreef in berichtnieuws
> > > a4kQ4.5323$J81.40251_at_newsr1.maine.rr.com...
> > > > Here's what I understand now:
> > > >
> > > > In order to allow any user other than a schema owner to access a
 schema
> > > > object, it's necessary to create a synonym for each and every object
 or
 to
> > > > use the syntax
> > > >
> > > > schema_owner.object_name
> > > >
> > > > when referencing it. To make matters worse, the synonyms don't
 export
 with
> > > > the schema they reference, so they're a pain to ship to a new
 installation.
> > > >
> > > > Is there any simpler, more reliable way to allow a designated group
 of
 users
> > > > to access a schema's objects without using the above syntax?
> > > >
> > > >
> > > > Cheers >>>>>>>>>>>> Robert R. Wagner
> > > > ExpanTest, Inc.
> > > > 22 Monument Square, Suite 503
> > > > Portland, ME 04101-4031
> > > > http://www.gwi.net/expantest/
> > > > robertwagner_at_alum.mit.edu
> > > >
> > > >
> > >
> > > The general and more reliable way is to write a script that simply
 generates
> > > synonyms for all objects you have been granted access to and you are
 set.
> > > You should use either private or public synonyms, using the
> > > owner.object_name notation makes you extreemly inflexible.
> > >
> > > Hth,
> > >
> > > Sybrand Bakker, Oracle DBA
> > >
> > >
> > >
> >
> >
>
>
Received on Mon May 08 2000 - 00:00:00 CDT