Achei esse script mas perdi a fonte.
Acredite esse script ajuda muitoooo.
set linesize 150
set echo off
set feedback off
set verify off
set head off
define v_newusername = 'NOVO_USUARIO'
define v_newuserpass = 'SENHA_USUARIO_NOVO'
define v_usertocopyfrom = 'USUARIO_ANTIGO'
set termout off
-- Create user, define default tablespace, temporary tablespace and profile
select '-- Create user, define default tablespace, temporary tablespace and profile' from dual
union all
select 'create user &v_newusername identified by &v_newuserpass' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace '|| temporary_tablespace ||
' profile '|| profile ||
';'
from dba_users
where username = upper('&v_usertocopyfrom')
union all
-- Grant Roles to new user
select '-- Grant Roles to new user' from dual
union all
select 'grant '|| granted_role || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grant System Privs...
select '-- Grant System Privs' from dual
union all
select 'grant '|| privilege || ' to &v_newusername' ||
decode(admin_option, 'YES', ' with admin option') || ';'
from dba_sys_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grants on database objects
select '-- Grant on database objects' from dual
union all
select 'grant '|| privilege || ' on ' || owner || '.' || table_name ||' to &v_newusername' ||
decode(grantable, 'YES', ' with admin option') || ';'
from dba_tab_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Grant Column Privs...
select '-- Grant Column Privs' from dual
union all
select 'grant '|| privilege || '(' || column_name || ') on ' || owner || '.' || table_name ||
' to &v_newusername;'
from dba_col_privs
where grantee = upper('&v_usertocopyfrom')
union all
-- Set Default Role...
select '-- Set default role' from dual
union all
select 'alter user &v_newusername default role ' || granted_role ||';'
from dba_role_privs
where grantee = upper('&v_usertocopyfrom')
and default_role = 'YES'
union all
-- Set quotas to user
select '-- Set quotas to new user' from dual
union all
select 'alter user &v_newusername quota ' || max_bytes ||' on ' || tablespace_name || ';'
from dba_ts_quotas
where username = upper('&v_usertocopyfrom');
undefine v_newusername
undefine v_newuserpass
undefine v_usertocopyfrom
set head on
set verify on
set echo on
set termout on
set feedback on