Skip to content
May 14, 2015 / doganay

GENERATE USER CREATE SCRIPT (COMPLETE)

undefine USERNAME

prompt 
prompt COMPLETE USER CREATE GENERATION SCRIPT by
prompt MUSTAFA DOGANAY
prompt http://doganay.worpress.com
prompt 

accept USERNAME prompt 'USERNAME......: '

set linesize 1000 pages 1000 feedback off

select chr(10)||'CREATE USER '||u1.name||chr(10)||
'IDENTIFIED BY VALUES '''||u1.password||''''||chr(10)||
'DEFAULT TABLESPACE '||t1.name||chr(10)||
'TEMPORARY TABLESPACE '||t2.name||chr(10)||
'PROFILE '||u2.profile||chr(10)||
decode(astatus,0,'ACCOUNT UNLOCK',1,'ACCOUNT UNLOCK PASSWORD EXPIRE',8,'ACCOUNT LOCK',9,'ACCOUNT LOCK PASSWORD EXPIRE','')||';'||chr(10)
from sys.user$ u1, dba_users u2, sys.ts$ t1, sys.ts$ t2
where
u1.user#=u2.user_id and
u1.DATATS#=t1.ts# and
u1.TEMPTS#=t2.ts# and
u1.name=upper(trim('&&USERNAME'))
--
--
union all
--
--
select 'grant sysdba to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysdba='TRUE'
union all
select 'grant sysoper to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysoper='TRUE'
union all
select 'grant sysasm to '||username||';' from v$pwfile_users where username=upper(trim('&&USERNAME')) and sysasm='TRUE'
union all
select '' from dual
union all
--
--
select 'grant '||granted_role||' to '||grantee||decode(admin_option,'YES',' with admin option','')||';'||chr(10)
from dba_role_privs
where grantee=upper(trim('&&USERNAME'))
--
--
union all
--
--
SELECT 'ALTER USER '
 || name
 || ' DEFAULT ROLE '
 || CASE defrole
 WHEN 0
 THEN
 'NONE;' || CHR (10)
 WHEN 1
 THEN
 'ALL;' || CHR (10)
 WHEN 2
 THEN
 (SELECT RTRIM (
 XMLAGG (
 XMLELEMENT (r, granted_role, ',').EXTRACT (
 '//text()')),
 ',')
 || ';'
 || CHR (10) || CHR (10)
 FROM dba_role_privs
 WHERE grantee = name AND default_role = 'YES')
 WHEN 3
 THEN
 (SELECT 'ALL EXCEPT '
 || RTRIM (
 XMLAGG (
 XMLELEMENT (r, granted_role, ',').EXTRACT (
 '//text()')),
 ',')
 || ';'
 || CHR (10) || CHR (10)
 FROM dba_role_privs
 WHERE grantee = name AND default_role = 'NO')
 END
 FROM sys.user$
 WHERE name = UPPER (TRIM ('&&USERNAME'))
--
--
union all
--
--
select '' from dual
--
--
union all
--
--
select 'grant '||privilege||' to '||grantee||decode(admin_option,'YES',' with admin option','')||';'
from dba_sys_privs
where grantee=upper(trim('&&USERNAME'))
--
--
union all
--
--
select '' from dual
--
--
union all
--
--
select 'alter user '||username||' quota '||decode(max_bytes,-1,'UNLIMITED',max_bytes)||' on '||tablespace_name||';' from DBA_ts_quotas
where username=upper(trim('&&USERNAME'))
--
--
union all
--
--
select '' from dual
--
--
union all
--
--
select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||decode(grantable,'YES',' with grant option','')||';'
from dba_tab_privs
where grantee=upper(trim('&&USERNAME'))
--
--
union all
--
--
select '' from dual
--
--
union all
--
--
select 'DECLARE'||chr(10)||
' KEYNUM NUMBER;'||chr(10)||
'BEGIN'||chr(10)||
' SYS.DBMS_JAVA.'||KIND||'_PERMISSION('||chr(10)||
' grantee => '''||grantee||''''||chr(10)||
' ,permission_type => '''||type_name||''''||chr(10)||
' ,permission_name => '''||name||''''||chr(10)||
' ,permission_action => '''||action||''''||chr(10)||
' ,key => KEYNUM'||chr(10)||
' );'||chr(10)||
'END;'||chr(10)||
'/'||chr(10)
from sys.dba_java_policy
where 
grantee = upper(trim('&&USERNAME'))
and enabled='ENABLED'
--
--
union all
--
--
select 'BEGIN'||chr(10)||
' SYS.DBMS_RESOURCE_MANAGER.clear_pending_area();'||chr(10)||
' SYS.DBMS_RESOURCE_MANAGER.create_pending_area();'||chr(10)||
' SYS.DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP'||'('''||grantee||''','''||granted_group||''','||decode(grant_option,'YES','TRUE','FALSE')||');'||chr(10)||
' SYS.DBMS_RESOURCE_MANAGER.submit_pending_area();'||chr(10)||
'END;'||chr(10)||
'/'||chr(10)
from dba_rsrc_consumer_group_privs
where 
grantee = upper(trim('&&USERNAME'))
--
--
union all
--
--
select 'ALTER USER '||client||' GRANT CONNECT THROUGH '||proxy||';' from dba_proxies
where client=upper(trim('&&USERNAME'));

set feedback on

undefine USERNAME
Advertisements

One Comment

Leave a Comment
  1. doganay / Jul 8 2015 1:13 pm

    Reblogged this on Mustafa DOGANAY's Blog and commented:

    added quota part:
    select ‘alter user ‘||username||’ quota ‘||decode(max_bytes,-1,’UNLIMITED’,max_bytes)||’ on ‘||tablespace_name||’;’ from DBA_ts_quotas
    where username=upper(trim(‘&&USERNAME’))

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: