Re: Synonyms
Date: Thu, 02 Dec 2010 15:23:18 +1100
Message-ID: <4CF71F36.2080406_at_iinet.net.au>
Agreed on the "declarations" bit. But I've moved away from public synonyms as well. For a great many reasons, I'll just enumerate a few:
1- They make life easier for hackers. Note: they are not the ONLY factor there,
just one of them. But every little bit helps.
2- They slow down parsing and execution. Steve Adams had an article on this in
his web site but it's now gone. I've tested his conclusions all the way to 10g
and they were still valid.
3- They effectively stop one from being able to consolidate multiple
applications into one instance. Like it or not, consolidation is a fact of
modern life. I'd rather embrace than fight it, quite frankly.
4- Writing code with "schema.table_name" for every table reference is a RPITA
and unnecessary, so a lot of folks omit it and just code "table_name". Using
public synonyms to circumvent reference problems for users of such applications
is a no-no: it leads to complex grant/authorization setups.
For this last one I simply setup a login trigger that alters the session for every application user to point to the schema where the app's tables live. Problem solved: the changed default schema points to the correct tables even if there are multiple schemas with common table names. Grants via role are still valid and effective on the logon user, not the current_schema user.
It is then child's play to setup tight security even with multiple copies of the same app in the same instance: setup a role for all logins of a given app, make all such logins point by default to the correct schema via ALTER SESSION SET CURRENT_SCHEMA and you are laughing.
-- Cheers Nuno Souto in wet Sydney, Australia dbvision_at_iinet.net.au Dominic.Brooks_at_barclayscapital.com wrote,on my timestamp of 2/12/2010 1:35 AM:Received on Wed Dec 01 2010 - 22:23:18 CST
> 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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sheehan, Jeremy
> Sent: 01 December 2010 14:30
> To: Thomas.Mercadante_at_labor.ny.gov; jd_at_commandprompt.com; oracle-l_at_freelists.org
> 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
>
>
-- http://www.freelists.org/webpage/oracle-l