Re: A synonym problem

Frank Greene <74200.427_at_CompuServe.COM> writes:

: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?

It would be nice if you could do this with a trigger on sys.dba_roles, but you can't for at least two reasons. You can't have DDL statements in a trigger, and sys.dba_roles is a view. (You'd have to play a lot of games getting the right information out of sys.sysauth$ and sys.user$.)

Further, the conventional wisdom is that you can't have triggers on tables owned by SYS, and it would probably be a Bad Thing To Do, anyway.

You should be able to write a short .sql file that grants the role and creates the private synonyms rather than doing it explicitly in SQL*Plus, though. Then all you'd have to do is '_at_grant_xyz NAME' rather than 'GRANT XYZ TO NAME'.     

Is there a reason why you can't or shouldn't use public synonyms for this? Having a public synonym does NOT make the table itself available to a user if no access has been granted on it. It makes the name of the table public, though.

---
Michael Nolan, Sysop for the DBMS RoundTable on GEnie
nolan_at_notes.tssi.com, dbms_at_genie.geis.com
(posted from nolan_at_helios.unl.edu)