avoid to create public synonyms

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 Received on Tue Nov 09 2004 - 04:14:48 CST