set linesize 80;
set pagesize 10000;
set long 50;

/* ************************************************ */
/*                                                  */
/* script to spool a listing to create the grants   */
/*    under my schema                               */
/*   1. generate drop public synonym                */
/*   2. generate create public synonym              */
/*   3. generate grants on public synonym           */
/*                                                  */
/*         @c:\oracle\sql\bld_grnt.sql              */ 
/*                                                  */
/* NOTE: will build all non-sys/system tables       */
/*                                                  */
/* ************************************************ */

set echo off;

/* ********************************* */
/* 1. Generate drop synonym commands */
/* ********************************* */

spool grants.sql;

select 'DROP PUBLIC SYNONYM '||table_name||';'
from all_tables
where OWNER = upper('&&schema')
order by TABLE_NAME ASC;

/* *********************************** */
/* 2. Generate CREATE synonym commands */
/* *********************************** */

select 'CREATE PUBLIC SYNONYM '||table_name||' FOR '||table_name||';'
from all_tables
where OWNER = upper('&&schema')
order by TABLE_NAME ASC;

/* *********************************** */
/* 3. Generate GRANT commands          */
/* *********************************** */

select 'GRANT DELETE, INSERT, SELECT, UPDATE ON '||table_name||' TO PUBLIC;'
from all_tables
where OWNER = upper('&&schema')
order by TABLE_NAME ASC;

spool off;
set heading on;
set pagesize 100;
set termout on;
undefine schema;
set echo on;

commit;
