user.clone.sql

<- Scripts

-- user.clone.sql
-- Purpose: Create a Clone user account script.

set pages ;
set linesize 128;
set feedback off;
set verify off;

 clear screen
PROMPT +--------------------------+
PROMPT | Create Clone User Script |
PROMPT +--------------------------+
PROMPT
ACCEPT v_ExistingUser PROMPT 'Existing Username to clone: ' DEFAULT 'SCOTT'
ACCEPT v_NewUser      PROMPT 'New Username to create: '     DEFAULT 'X15'
ACCEPT v_NewUserPW    PROMPT 'New Username password: '      DEFAULT '-999999'
PROMPT

-- Confirm
PROMPT
PROMPT Create user &v_NewUser from &v_ExistingUser?
PROMPT
ACCEPT v_Confirm PROMPT 'Press Enter to Clone'
PROMPT

spool C:\app\scripts\sql\spool\createUser.&v_NewUser..sql

prompt -- C:\app\scripts\sql\spool\createUser.&v_NewUser..sql
prompt

select 'create user &v_NewUser identified by "&v_NewUserPW"'||
       ' default tablespace '  ||default_tablespace||
       ' temporary tablespace '||temporary_tablespace||
       ' profile '|| profile||';'
from sys.dba_users
where username = upper('&v_ExistingUser');

-- roles
select 'grant '||granted_role||' to &v_NewUser'||decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&v_ExistingUser');

-- sys privileges
select 'grant '||privilege||' to  &v_NewUser'||decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&v_ExistingUser');

-- tabl privileges
select 'grant '||privilege||' on '||owner||'.'||table_name||' to &v_NewUser;'
from sys.dba_tab_privs
where grantee = upper('&v_ExistingUser');


-- col privileges
select 'grant '||privilege||' on '||owner||'.'||table_name||
'('||column_name||') to &v_NewUser;'
from sys.dba_col_privs
where grantee = upper('&v_ExistingUser');

-- tablespace quotas
select 'alter user '||username||' quota '||
       decode(MAX_BYTES,-1,'UNLIMITED', to_char(MAX_BYTES/(1024*1024),'9999') || 'M')||' on '||tablespace_name||';'
from sys.dba_ts_quotas
where username = upper('&v_ExistingUser');


-- default roles
set serveroutput on;
declare defroles varchar2(4000);
begin
   for c1 in (select * from sys.dba_role_privs
              where grantee = upper('&v_ExistingUser')
              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  &v_NewUser default role '||defroles||';');
end;
/

set serveroutput off;
spool off;

-- End
set head on
set pages 0
PROMPT
PROMPT Run C:\app\scripts\sql\spool\createUser.&v_NewUser..sql
PROMPT *** Process Completed ***
PROMPT