Re: Synonyms are a pain!

Sybrand Bakker wrote:
>
> 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
> > >
> > >
> > >
> >
> >

I'm not a fan of synonyms since..

private - can be a pain to manage
public - use more session memory due to negative dependency checking

and both require more work from the dictionary when resolving objects etc..

Now that 'alter session set current_schema = ...' is supported, I'd be looking at that as an opening command in an application..

Cheers

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse