Stuart: Created page with "Based on a function by Frank Naude

 set pages 0 feedback off verify off lines 500 set echo off set feedback off   accept oldname prompt "Enter user to model new user to: ..."

2013-01-08T11:45:54Z

Created page with "Based on a function by Frank Naude <pre> set pages 0 feedback off verify off lines 500 set echo off set feedback off accept oldname prompt "Enter user to model new user to: ..."

New page

Based on a function by Frank Naude
<pre>
set pages 0 feedback off verify off lines 500
set echo off
set feedback off

accept oldname prompt "Enter user to model new user to: "
accept newname prompt "Enter new user name: "
-- accept psw prompt "Enter new user's password: "

COLUMN generated_password NEW_VALUE generated_password noprint;
select get_random_password(p_password_format=>'LLLNNN?L') as 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');

-- Tablespace Quotas...
select 'alter user &&newname 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 cntrl-d if not correct, press <enter> to create the new user

@create_user_&&newname..sql
</pre>

Create user as a copy of another - Revision history

Stuart: Created page with "Based on a function by Frank Naude
set pages 0 feedback off verify off lines 500 set echo off set feedback off accept oldname prompt "Enter user to model new user to: ..."