create a new user (with privs) like an existing


set pages 0 feed off veri off lines 500

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: "

-- Create user...
 select 'create user &&newname identified by values '''||password||''''||
 -- select 'create user &&newname identified by &psw'||
 ' 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 '||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;
 /

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: