Re: A synonym problem

In article <3hbmhq$pp6$1_at_mhadg.production.compuserve.com>, Frank Greene <74200.427_at_CompuServe.COM> says:
>
>Problem with synonyms --
>
>User_one owns table table_one. We create role role_one which,
>among other things, grants select on table_one.
>
>Now create a new user user_two and grant role_one to that user.
>
>When we log on as user_two, apparently the only way to access
>table_one
>is via something like
>
> SELECT * FROM USER_ONE.TABLE_ONE;
>
>That is, fully qualify the table name.
>
>Real bummer for code developers. THE QUESTION: is there any way
>to
>create a private synonym which is attached to to a role in such a
>fashion that when the role is granted to a new user, the table
>synonyms
>come along with it?

I wish I could do that too. I don't believe it is possible, though.

A workaround I have started to use is as follows:

Create an ID for each "system" that will own all tables for that "system." This would be equivalent to your 'User_one' above. Create a table that can be seen by everyone, or at least by those IDs that grant things to other IDs, that looks something like this:

  create table Required_Synonyms

    (System_Owner  varchar2(...),
     Granted_Role  varchar2(...),
     Req_Synonym   varchar2(...),
     Base_Table    varchar2(...)

When you grant a user some roles, you also run a program that goes through the above table and creates the synonyms that are required for each role by issuing a 'Create synonym userid.Req_Synonym for System_Owner.Base_Table' for each record that applies. The Base_Table may of course be a view that restricts what can be seen or whatever.

This method is terribly inefficient, but does get the job done.

Good Luck!

Jeff Qualls
Delco Electronics Corp. Received on Thu Feb 09 1995 - 15:21:06 CET