Users recreation with quotas, privileges and roles

It is quite often in some Oracle shops to refresh databases or a specific schemas of a database using the traditional exp/imp method while retaining the same roles and privileges and other resources is a nerve racking job. Here are some scripts to make life easy for such a DBA to retain the user quotas, privileges and roles (QPR). These scripts generate the sql statements to recreate the QPRs and are named in a sequential order and are to be run in the same sequetial order. Its a known fact that there are misspellings in the script names, which were created when it was a real tizzy situation.

------------------------------
--- 01_useR_recreation.sql ---
------------------------------

set line 150
select 'create user '||username||' identified by values '||
''''||password||''''||' default tablespace '||
DEFAULT_TABLESPACE||' temporary tablespace '||
TEMPORARY_TABLESPACE||' profile '||PROFILE||';' from dba_users 
order by username;

-------------------------------------
--- 02_useR_Quota_recreateion.sql ---
-------------------------------------

set serveroutput on size 1000000 
set line 150
declare
a number;
cursor q is select username,max_bytes,tablespace_name 
from dba_ts_quotas order by username;
begin
for i in q
loop
if i.max_bytes=-1 then
dbms_output.put_line('alter user '||i.username||
' quota unlimited'||' on '||i.tablespace_name||';');
else
dbms_output.put_line('alter user '||i.username||
' quota '||i.max_bytes||' on '||i.tablespace_name||';');
end if;
end loop;
end;
/


---------------------------------------
--- 03_useR_sys_priv_recreation.sql ---
---------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor priv (user_nm in varchar2) is select 
distinct(grantee),privilege from dba_sys_privs where 
grantee=user_nm and grantee not in 
('CONNECT','SYSTEM','RESOURCE','SYS','EXP_FULL_DATABASE',
'TSMSYS','RECOVERY_CATALOG_OWNER','SCHEDULER_ADMIN',
'AQ_ADMINISTRATOR_ROLE','DIP','OEM_ADVISOR','DBA',
'IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP','OUTLN') 
order by grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' to '
||i.username||';');
end loop;
end loop;
end;
/


------------------------------------
--- 04_uSer_role_recreateion.sql ---
------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users order 
by 1;
cursor role (user_nm in varchar2) is select distinct 
GRANTEE,granted_role from dba_role_privs where grantee 
not in ('DBSNMP','DIP','OUTLN','SYS','SYSTEM','TSMSYS',
'EXP_FULL_DATABASE','DBA','SELECT_CATALOG_ROLE',
'IMP_FULL_DATABASE','EXECUTE_CATALOG_ROLE',
'LOGSTDBY_ADMINISTRATOR') and grantee=user_nm order by 
grantee;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in role(i.username)
loop
dbms_output.put_line('grant '||j.granted_role||' to '
||i.username||';');
end loop;
end loop;
end;
/


-----------------------------------------
--- 05_user_tab_privs_recreateion.sql ---
-----------------------------------------

set serveroutput on size 1000000
set line 150
declare
cursor user_name is select username from dba_users 
order by 1;
cursor tab_priv (user_nm in varchar2) is select 
distinct(grantee),owner,table_name,privilege
from dba_tab_privs where grantee not in ('SYSTEM',
'PUBLIC','SELECT_CATALOG_ROLE','GATHER_SYSTEM_STATISTICS',
'EXP_FULL_DATABASE','SYS','EXECUTE_CATALOG_ROLE',
'DELETE_CATALOG_ROLE','LOGSTDBY_ADMINISTRATOR',
'AQ_USER_ROLE','AQ_ADMINISTRATOR_ROLE','HS_ADMIN_ROLE',
'DBA','IMP_FULL_DATABASE','OEM_MONITOR','DBSNMP',
'OUTLN') and grantee=user_nm order by grantee,owner;
a number;
b varchar2(100);
begin
for i in user_name
loop
for j in tab_priv(i.username)
loop
dbms_output.put_line('grant '||j.privilege||' on '||j.owner
||'.'||j.table_name||' to '||i.username||';');
end loop;
end loop;
end;
/

Here is a bonus script to only recreate the passwords of users in a database.

----------------------------------------
--- user_old_password_recreation.sql ---
----------------------------------------

set pages 0
set line 100

select 'alter user '||username||' identified by values '||
''''||password||''''||';' from dba_users order by username;

PS: Please format the lines of code once they have been pasted in your editor as the lines may have been broken down to the next ones while publishing the scripts in a web page