Re: dba question - synonym

In article <80ev5i$3u2$1_at_nnrp1.deja.com>,   Nandakumar <N.Kumar_at_rocketmail.com> wrote:

> i wonder if there is a way to create a PUBLIC synonym for every
> table on its creation.
>
> say, when a table TABLE_A is created by user USER_A, there should a
> PUBLIC synonym TABLE_A be created with link to USER_A.TABLE_A.

No, there is no magic "init.ora" parameter (or whatever) to do this automatically.
Although there are ways you could do this with scripts, you have solved the issue that USER_A and USER_B can both have a table with the same name, since there can only be one public synonym with any given name.

Assuming you ensure that there are no naming conflicts, then you could create a SQL script using the ouput from SELECT 'CREATE PUBLIC SYNONYM '||table_name||

       ' FOR '||owner||'.'||table_name FROM dba_tables; however you would probably want to exclude all tables owned by SYS or SYSTEM by using a WHERE clause.
Secondly the existence of a public synonym does not mean that the table can be accessed. Tables privileges (SELECT, INSERT,..) have to be granted to public as well. If the grants to PUBLIC were done at the same time as table creation then you might want to change the above SELECT statement to use dba_tab_privs instead and only create synonyms if some privilege had been granted to public.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Nov 11 1999 - 13:09:41 CST