RE: Synonyms
- From: <Dominic.Brooks@xxxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Dec 2010 11:20:23 +0000
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
>
> --
> //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�^
Other related posts:
