rem $DBA/copyuser.sql
rem Create a script to copy the user - Create user, Grant roles,
rem Grant system privs
rem
rem ================ Sample output file created by this script: ================
rem ===== create user BRIAN identified by BRIAN
rem ===== default tablespace AOL_DAT temporary tablespace TEMP profile DEFAULT;
rem ===== alter user BRIAN quota unlimited on AOL_DAT;
rem ===== alter user BRIAN quota unlimited on AOL_NDX;
rem ===== alter user BRIAN quota unlimited on TEMP;
rem ===== grant CONNECT to BRIAN;
rem ===== grant ALTER ANY PROCEDURE to BRIAN;
rem ===== grant ALTER ANY TRIGGER to BRIAN;
rem ===== grant CREATE ANY PROCEDURE to BRIAN;
rem ===== grant CREATE ANY TRIGGER to BRIAN;
rem ===== grant DROP ANY PROCEDURE to BRIAN;
rem ===== grant DROP ANY TRIGGER to BRIAN;
rem ===== exit;
rem ============================================================================
set term off echo off heading off feedback off verify off
set pagesize 0 linesize 80 recsep off
define cr=chr(10)
set concat +
spool copyuser.tmp
set concat .
rem ============================================================================
select 'create user '||'&&2'||' identified by '||'&&2 default'||&cr||-
'tablespace '|| default_tablespace ||' temporary tablespace '||-
temporary_tablespace||' profile '||profile||';' from dba_users-
where username=upper('&&1');
rem ============================================================================
select 'alter user '||'&&2'||' quota '||-
decode(max_bytes,-1,'unlimited',max_bytes)|| ' on '||tablespace_name||';'-
from dba_ts_quotas where username=upper('&&1');
rem ============================================================================
select 'grant '||granted_role||' to '||'&&2'||-
decode(admin_option,'YES',' with admin option','')||';'-
from dba_role_privs where grantee=upper('&&1');
rem ============================================================================
select 'grant '||privilege||' to '||'&&2'||-
decode(admin_option,'YES',' with admin option','')||';'-
from dba_sys_privs where grantee=upper('&&1');
rem ============================================================================
select 'exit;' from dual;
rem ============================================================================
spool off;
exit