Export user details including grants

This writes to a script called create.<userName>.sql which can then be executed to create the user in another db.

Note that it may throw an error if the dba_java_policy view does not exist. Simply edit the output file if that happens.

set echo off;
set linesize 80;
set pagesize 9999;
set feedback off;
set heading off;
set verify off;
set timing off;

accept user prompt "Enter User Name: ";

spool create.&user..sql;

select 'create user '||username||' identified by values '||chr(39)||password||chr(39)||'
       default tablespace '||default_tablespace||' temporary tablespace '||temporary_tablespace||' profile '||profile||';'
       from sys.dba_users
       where username = upper('&user');


select 'grant '||privilege||' to '||grantee||' with admin option;'
       from sys.dba_sys_privs
       where grantee = upper('&user')
         and admin_option = 'YES';

select 'grant '||privilege||' to '||grantee||' ;'
       from sys.dba_sys_privs
       where grantee = upper('&user')
         and admin_option = 'NO';


select 'grant '||granted_role||' to '||grantee||' with admin option;'
       from sys.dba_role_privs
       where grantee = upper('&user')
         and admin_option = 'YES';

select 'grant '||granted_role||' to '||grantee||';'
       from sys.dba_role_privs
       where grantee = upper('&user')
         and admin_option = 'NO';

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

select 'alter user &user default role all ; ' from dual ;

select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||grantee||' with grant option ;' 
       from dba_tab_privs 
       where grantee = upper('&user')
         and grantable = 'YES';

select 'grant '||privilege||' on '||owner||'."'||table_name||'" to '||grantee||' ;' 
       from dba_tab_privs 
       where grantee = upper('&user')
         and grantable = 'NO';

select kind||' '||type_schema||' '||type_name||' '||name||' '||action||' 'enabled from dba_java_policy where grantee = upper('&user');

spool off;