RE: Synonyms

But maybe there's a significant distinction between bespoke/inhouse systems and 
third party /off the shelf applications 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Brooks, Dominic: IT (LDN)
Sent: 02 December 2010 11:20
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Synonyms

Hard coding the schema name when referencing the objects is generally not a 
good practice - completely disagree (from a development perspective). 

I would say that it's not unusual for a complicated application to span more 
than one schema and for the code to need to reference objects in more than one 
schema. Personally I do not like this to be resolved via synonyms (public or 
private).

I think hosting multiple copies of the same application in the same database is 
the exception not the rule, even more so in these days of VMs.


-----Original Message-----
From: Vishal Gupta [mailto:vishal@xxxxxxxxxxxxxxx] 
Sent: 01 December 2010 19:54
To: JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx
Cc: Brooks, Dominic: IT (LDN); oracle-l@xxxxxxxxxxxxx
Subject: Re: Synonyms

Hard coding the scheme name when referencing the objects is generally not a 
good practice. Especially if you want to host multiple copies of same 
application in the same database. 

If you are worried about not having to remember schema passwords, then as a DBA 
account you could issue following command before running release scripts. 

Alter session set current_schema = <schemaowner>;

Few caveats with this approach though. Following will still not work when 
statements in another schema from another account. 

- creating private synonym in another schema
- manipulating DBMS_jobs in another schema. 

Regards,
Vishal

On 1 Dec 2010, at 14:54, "Sheehan, Jeremy" <JEREMY.SHEEHAN@xxxxxxxxxxxxxxxxx> 
wrote:

> --- Surely it's not really public synonyms but granting unnecessary 
> privileges on those objects to public.
> 
> Even then, public synonyms are frowned upon.  We're pretty serious about SOX 
> here and that's what has been declared.  So we follow the rules, you know?  
> 
> -- Is it SOX compliant to have a post release script to remove all public 
> synonyms and replace them with private synonyms for all users?
> 
> That's been done in the past, but for most things, we have the folks just 
> start modifying their code to it references the table in the "owner.table" 
> format.  Makes it easier for deploying code, too.  Not as many passwords to 
> remember (everything can be run by the DBA and not the schema owner).  
> 
> Jeremy 
> 
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Dominic.Brooks@xxxxxxxxxxxxxxxxxxx
> Sent: Wednesday, December 01, 2010 9:36 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: RE: Synonyms
> 
> I love those declarations.
> 
> Surely it's not really public synonyms but granting unnecessary privileges on 
> those objects to public.
> 
> Is it SOX compliant to have a post release script to remove all public 
> synonyms and replace them with private synonyms for all users?
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Sheehan, Jeremy
> Sent: 01 December 2010 14:30
> To: Thomas.Mercadante@xxxxxxxxxxxx; jd@xxxxxxxxxxxxxxxxx; 
> oracle-l@xxxxxxxxxxxxx
> Subject: RE: Synonyms
> 
> It's been declared that public synonyms are bad for any SOX databases where I 
> work.  That means that the DBA's have to comb through any code that needs to 
> be promoted for the forbidden words 'create public synonym'.  It really sucks 
> when a 3rd party application relies heavily on them.  We have to constantly 
> push code back to them for revision.  
> 
> Jeremy 
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Mercadante, Thomas F (LABOR)
> Sent: Wednesday, December 01, 2010 9:20 AM
> To: jd@xxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
> Subject: RE: Synonyms
> 
> Josh,
> 
> I use them all the time.  Standard practice here.  I used to use public 
> synonyms but now use private syns.  The schema who owns the objects creates 
> private syns for the users who need them.
> 
> Tom
> 
> 
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
> Behalf Of Joshua D. Drake
> Sent: Tuesday, November 30, 2010 7:31 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Synonyms
> 
> Hello,
> 
> Kind of out of left field here but I was curious about how many people
> here use Synonyms and the types of synonyms they are normally using,
> i.e; table/view/column?
> 
> Sincerely,
> 
> Joshua D. Drake
> -- 
> PostgreSQL.org Major Contributor
> Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
> Consulting, Training, Support, Custom Development, Engineering
> http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt
> 
> --
> http://www.freelists.org/webpage/oracle-l
> 
> 
> 
> ��i��0���zX���+��n��{�+i�^
> ��i��0���zX���+��n��{�+i�^
> 
> _______________________________________________
> 
> This e-mail may contain information that is confidential, privileged or 
> otherwise protected from disclosure. If you are not an intended recipient of 
> this e-mail, do not duplicate or redistribute it by any means. Please delete 
> it and any attachments and notify the sender that you have received it in 
> error. Unless specifically indicated, this e-mail is not an offer to buy or 
> sell or a solicitation to buy or sell any securities, investment products or 
> other financial product or service, an official confirmation of any 
> transaction, or an official statement of Barclays. Any views or opinions 
> presented are solely those of the author and do not necessarily represent 
> those of Barclays. This e-mail is subject to terms available at the following 
> link: www.barcap.com/emaildisclaimer. By messaging with Barclays you consent 
> to the foregoing.  Barclays Capital is the investment banking division of 
> Barclays Bank PLC, a company registered in England (number 1026167) with its 
> registered office at 1 Churchill Place, London, E14 5HP.  This email may 
> relate to or be sent from other members of the Barclays Group.
> _______________________________________________
> !���
> 0~���+-����
> ������rW�
> †Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^
��i��0���zX���+��n��{�+i�^
��i��0���zX���+��n��{�+i�^

Other related posts: