Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!drn.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!not-for-mail From: joel-garry@home.com (Joel Garry) Newsgroups: comp.databases.oracle.misc Subject: Re: avoid to create public synonyms Date: 11 Nov 2004 17:02:59 -0800 Organization: http://groups.google.com Lines: 55 Message-ID: <91884734.0411111702.1cc4af90@posting.google.com> References: <2vbjkoF2j8hemU1@uni-berlin.de> <2vek06F2k6eb1U1@uni-berlin.de> NNTP-Posting-Host: 67.116.125.178 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1100221379 1053 127.0.0.1 (12 Nov 2004 01:02:59 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 12 Nov 2004 01:02:59 +0000 (UTC) Xref: dp-news.maxwell.syr.edu comp.databases.oracle.misc:114191 "Dietmar Brueckmann" wrote in message news:<2vek06F2k6eb1U1@uni-berlin.de>... > "Andrew" schrieb im Newsbeitrag > news:b165f5d8.0411091054.726db836@posting.google.com... > > "Dietmar Brueckmann" wrote in message > news:<2vbjkoF2j8hemU1@uni-berlin.de>... > > > From a management aspect your DBA is correct. Creating one (1) > > public synonym per table and managing access through roles is much > > more manageable than creating Private synonyms for each user. What > > exactly are you trying to prevent/ allow for your users? If you need > > to dynamically determine what the users should have access to look at > > Virtual Private Databases(VPD) / Fine Grained Access Control(FGAC) on > > OTN > > > > If security is your concern check the following white paper. It does > > not directly address this but may give you some more ideas. > > > http://www.oracle.com/technology/deploy/security/oracle9i/pdf/9ir2_checklist > .pdf > > > > I've looked at this papers - thanks. > > But my simple problem is to avoid modifing my aplications-SELECT/INSERT etc. > for testcase: > > Testdata for me > SELECT * from A_TEST_SCHEMA.A_TABLE; > > and for production: > SELECT * from A_SCHEMA.A_TABLE; > > I'd like to write everytime: > SELECT * from A_TABLE; > to prevent me for making mistakes in modifying SQL-strings. > > For that I need synonyms. In using public synonyms I'd be afraid of mixing > "hot" data to my test-schema. > select * from a_table - sees it the table A_TABLE or because of synonym > A_SCHEMA.A_TABLE. > > Possibly I can use "SYS_CONTEXT Function" as shown in one of the articles. > But the easy readability of my SQL-statements is the highest important > thing. > Thats why I'm thinking I'm going on to generate all private synonyms are > necessary. > But I've asked. Thanks for answering me. You didn't ask, but you might tell your DBA to look at the discussion about performance of private v public synonyms at www.ixora.com.au jg -- @home.com is bogus. Funnel cakes - donuts with a touch of chaos theory