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;
