dbawiki

I believe the original script was devised by Frank Naude

set pages 0 feedback off verify off lines 500
set echo off
set feedback off


accept newname prompt "Enter new user name: "
accept oldname prompt "Enter user to model new user from: "

col generated_password new_value generated_password nopri;
select dbms_random.string('a',7)||'#'||dbms_random.string('x',8) generated_password
from dual
/

prompt ###                                ________
prompt ### New Username is           ==>  &&newname.
prompt ### Password for this user is ==>  &&generated_password.
prompt ###                                ________

spool create_user_&&newname..sql
-- Create user...
select 'spool create_user_&&newname..log' from dual;
select 'set echo on'     from dual;
select 'set verify on'     from dual;
select 'set feedback on'   from dual;

select 'create user &&newname identified by &&generated_password.'||
       ' default tablespace '||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||
       ' profile '||profile||';'
from   sys.dba_users
where  username = upper('&&oldname')
/

-- Grant Roles...
select 'grant '||granted_role||' to &&newname'||decode(admin_option, 'YES', ' with admin option')||';'
from   sys.dba_role_privs
where  grantee = upper('&&oldname')
/


-- Grant System Privs...
select 'grant '||privilege||' to &&newname'||decode(admin_option, 'YES', ' with admin option')||';'
from   sys.dba_sys_privs
where  grantee = upper('&&oldname')
/


-- Grant Table Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
from   sys.dba_tab_privs
where  grantee = upper('&&oldname')
/


-- Grant Column Privs...
select 'grant '||privilege||' on '||owner||'.'||table_name||'('||column_name||') to &&newname;'
from   sys.dba_col_privs
where  grantee = upper('&&oldname')
/


-- Set tablespace Quotas...
select 'alter user '||username||' quota '||decode(max_bytes, -1, 'unlimited', max_bytes)||' on '||tablespace_name||';'
from   sys.dba_ts_quotas
where  username = upper('&&oldname')
/

-- Set Default Role...
set serveroutput on
declare
defroles varchar2(4000);
begin
    for c1 in (
              select *
              from   sys.dba_role_privs
              where  grantee      = upper('&&oldname')
              and    default_role = 'YES'
              )
    loop
        if length(defroles) > 0 then
            defroles := defroles||','||c1.granted_role;
        else
            defroles := defroles||c1.granted_role;
        end if;
    end loop;
    dbms_output.put_line('alter user &&newname default role '||defroles||';');
end;
/

spool off
pause Press CTRL-C if not correct or ENTER to create the new user

@create_user_&&newname..sql