From: "Sybrand Bakker" Subject: Re: Synonyms are a pain! Date: 2000/05/05 Message-ID: <957554932.15915.0.pluto.d4ee154e@news.demon.nl>#1/1 X-NNTP-Posting-Host: sybrandb.demon.nl:212.238.21.78 References: <957469924.8355.0.pluto.d4ee154e@news.demon.nl> X-Priority: 3 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Complaints-To: abuse@nl.demon.net X-Trace: news.demon.nl 957554932 pluto:15915 NO-IDENT sybrandb.demon.nl:212.238.21.78 X-MSMail-Priority: Normal Newsgroups: comp.databases.oracle.server 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 schreef in berichtnieuws qgEQ4.6076$J81.41652@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" > Newsgroups: comp.databases.oracle.server > Sent: Thursday, May 04, 2000 3:53 PM > Subject: Re: Synonyms are a pain! > > > "Sybrand Bakker" wrote in message > news:957469924.8355.0.pluto.d4ee154e@news.demon.nl... > > > > Robert Wagner schreef in berichtnieuws > > a4kQ4.5323$J81.40251@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@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 > > > > > > > >