Re: avoid to create public synonyms

"Dietmar Brueckmann" <Dietmar.Brueckmann_at_lycos.de> wrote in message news:<2vbjkoF2j8hemU1_at_uni-berlin.de>...
> Hello,
>
> I'm searching for a solution of the following problem.
> I've created a table A_TABLE in the schema A_SCHEMA.
> And I've users THE_USER1 .. THE_USERn.
> The Users got a role THE_USER_ROLE.
>
> To enable my users to "select * A_TABLE" I've done:
>
> grant select on A_TABLE to THE_USER_ROLE;
> create synonym THE_USER1.A_TABLE for A_SCHEMA.A_TABLE;
> ...
> create synonym THE_USERn.A_TABLE for A_SCHEMA.A_TABLE;
>
> I've a mirror schema A_TEST_SCHEMA with testing-data.
>
> So I can test my application by logging into A_TEST_SCHEMA and works fine
> with test-data.
> The users log into THE_USERk and works fine withe the original data by using
> synonyms.
>
> But I'd like to avoid the n "CREATE SYNONYM" statements (for each
> table/view). Is there any way to place it in the role.
> My DBAdmin says no: I should use public synonyms - thats the oracle way.
> But's horrible to see in every schema all public synonyms I haven't any
> access.
>
> Best regards
> Dietmar

Dietmar,
  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

Andrew Received on Tue Nov 09 2004 - 12:54:19 CST