Wednesday 21 April 2010

Creating public synonym of all the tables under SYSADM schema

Creating public synonym for a single table is easy in oracle but at times we might  need to create public synonym for all the tables under a schema. In
Peoplesoft, we might use it for below purpose:
        a. To help other Database users to directly access SYSADM tables without  prefixing owner name eg. sysadm.PSOPRDEFN.
        b. To create 2nd access id and granting rights on tables of primary access id.

1. Generating Script for Creating Public synonyms
        Open the SQL Tool and run the following commands.

        SQL> Spool synonyms.sql;

        SQL> select 'create public synonym ' ||table_name||  ' for ' ||owner||'.'||table_name ||';' from dba_tables where owner='SYSADM';

        SQL> spool off;

        Open synonyms.sql in a text editor.
        This file would contain the sql to create public synonym for all the tables in SYSADM schema.There would be multiple entries as shown below
        'CREATEPUBLICSYNONYM'||TABLE_NAME||'FOR'||OWNER||'.'||TABLE_NAME||';' .
        These entries can be simple replaced with blank space.

        We are left with script that be executed to create public synonym of all SYSADM'S table.

2. Granting rights on these public synonyms to another userid.

        SQL> Spool grantsyn.sql;

        SQL> select 'grant select on ' ||table_name|| ' to sysadm2 ;' from dba_tables where owner='SYSADM';

        SQL> spool off;

        OR

        SQL> Spool grantsyn.sql;

        SQL> select 'grant select on ' ||synonym_name|| ' to  sysadm2;' from dba_synonyms where owner='SYSADM';

        SQL> spool off;

3. Run synonyms.sql
4. Run grantsyn.sql

1 comment:

Unknown said...

awful piece of information, I had come to know about your blog from my friend vimal, mumbai,i have read atleast 13 posts of yours by now, and let me tell you, your blog gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a million once again, Regards, Synonyms



Get our exclusive subscription-only PeopleSoft tips once per month!

How was the blog? Help us in getting better