Re: synonym help

> I would like to have someone help with auto-populate synonym for hundreds of
> tables on a new db.
>
> I had used one way like:
>
> create public synonym ABC for ABC
> /
> grant insert,select,update,delete on ABC to public
> /
> create public synonym ACTION for ACTION
> /
> grant insert,select,update,delete on ACTION to public
> /
>
> and so on and so forth. But this needs to edit every table in the code.
> Is there any way to automate this code to search all tables, create public
> synonym of it and grant right on it to public?
>
> Any help and comments are very appreciated.

Write a stored procedure that opens a cursor into dba_tables. Then write a couple of functions that return True or False depending on whether a corresponding synonym or grant exists. Then based on the returned value use dynamic SQL to create the missing item.

Write it once. Run it as a cron job once a day or once a week and never worry about it again.

Daniel A. Morgan Received on Mon Feb 05 2001 - 04:46:54 CET