rem $DBA/copyusg.sql
rem
rem Creates:
rem   1) A SQL script which will allow SYS to grant all privileges on the table
rem      objects to the SYSTEM account (so that SYSTEM can then grant the object
rem      privileges to the new user account being created).
rem   2) A SQL script which will allow SYSTEM to grant the privileges held by
rem      the user being copied on the table columns to the account being
rem      created.
rem
rem Create a list of column grants for this user
rem
set echo off term off feedback off heading off verify off
set pagesize 0
set concat +
spool copyuser_col_SYSTEM.tmp
set concat .
select distinct 'grant all on '||owner||'.'||table_name||'('||column_name||
') to SYSTEM with grant option;' from dba_col_privs where grantee = '&&1';
select 'exit;' from dual;
spool off
set concat +
spool copyusg.tmp
set concat .
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to '||'&&2'||
decode(grantable,'YES',' with grant option','')||' /* Grantor='||grantor||' */;'
from dba_col_privs where grantee = '&&1'
order by grantor,table_name,column_name;
select 'exit;' from dual;
spool off
exit