Re: Synonyms are a pain!

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 Fri May 05 2000 - 00:00:00 CDT