Oracle 10g script for User/Role/Object Privileges

Imagine you have to drop an Oracle user and create it with all privs/roles again.  This often happens in test cycle of 3rd party products. The privs are sent to user on ad hoc basis to get around the installation errors and then comes requirement to replicate it on another server.  How do you do it?  Attached script will create a spool file for user granted roles, object Privileges

set serveroutput on
set feedback off
set verify off
declare
test varchar2(10000);
h number;
j number := 0;
begin
dbms_output.enable(10000);
–prompt enter the user name accept user
dbms_output.put_line(‘***********************************************************************’);
dbms_output.put_line(‘The Roles granted to the users are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘ROLE_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘***********************************************************************’);
j := 0;
dbms_output.put_line(‘The System privileges are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘SYSTEM_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘************************************************************************’);
j := 0;
dbms_output.put_line(‘The Object level privileges are ‘);
dbms_output.put_line(‘***********************************************************************’);
select DBMS_METADATA.GET_GRANTED_DDL(‘OBJECT_GRANT’,upper(‘&&USER’)) into test from dual;
for i in 1..ceil(length(test)/255)
loop
dbms_output.put_line(substr(test,j,255));
j := j+255;
end loop;
dbms_output.put_line(‘*************************************************************************’);
end;

Sample OUTPUT produced

Enter value for user: SYSTEM
***********************************************************************
The Roles granted to the users are
***********************************************************************
GRANT “DBA” TO “SYSTEM” WITH ADMIN OPTION
GRANT “AQ_ADMINISTRATOR_ROLE” TO “SYSTEM” WITH ADMIN OPTION
GRANT “MGMT_USER” TO “SYSTEM”

***********************************************************************
The System privileges are
***********************************************************************
GRANT GLOBAL QUERY REWRITE TO “SYSTEM”
GRANT CREATE MATERIALIZED VIEW TO “SYSTEM”
GRANT SELECT ANY TABLE TO “SYSTEM”
GRANT CREATE TABLE TO “SYSTEM”
GRANT UNLIMITED TABLESPACE TO”SYSTEM” WITH ADMIN OPTION

************************************************************************
The Object level privileges are
***********************************************************************
GRANT ALTER ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT DELETE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT INSERT ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCEXP” TO “SYSTEM”
GRANT ALTER ON “SYS”.”INCVID” TO “SYSTEM”
GRANT DELETE ON”SYS”.”INCVID” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCVID” TO “SYSTEM”
GRANT INSERT ON”SYS”.”INCVID” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCVID” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCVID” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCVID” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCVID” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCVID” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCVID” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCVID” TO “SYSTEM”
GRANT ALTER ON “SYS”.”INCFIL” TO”SYSTEM”
GRANT DELETE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT INDEX ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT INSERT ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT SELECT ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT UPDATE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT REFERENCES ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT ON COMMIT REFRESH ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT QUERY REWRITE ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT DEBUG ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT FLASHBACK ON “SYS”.”INCFIL” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_ALERT” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_SYS_ERROR” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_RULE_EXIMP” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQ” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQADM” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQ_IMPORT_INTERNAL” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_AQELM” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”DBMS_TRANSFORM_EXIMP” TO “SYSTEM” WITH GRANT OPTION
GRANT EXECUTE ON “SYS”.”SYS_GROUP” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_DEFER_IMPORT_INTERNAL” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”DBMS_REPCAT” TO “SYSTEM”
GRANT EXECUTE ON “SYS”.”SET_TABLESPACE” TO”SYSTEM”
GRANT EXECUTE ON “SYS”.”CHECK_UPGRADE” TO “SYSTEM”
*************************************************************************