Oracle: Scripts to generate DDL for Roles and Users

Preface

Often we need to copy users/roles from one database to other database especially when you are deploying a new application to a higher environment. Below are 2 scripts which can generate scripts for you to build the roles and users. You can modify these scripts to generate scripts for multiple users at the same time by changing USERNAME= or ROLE= to USERNAME in (”.”) or ROLE in (”,”) respectively.

Script

SCRIPT1 : build_user_script.sql

SET HEAD OFF 
SET PAGES 0 
SET LONG 9999999 
SPOOL build_user.sql 
PROMPT Enter USERNAME Name 
ACCEPT USERNAME 
SELECT DBMS_METADATA.GET_DDL(’USER’, USERNAME) || ‘/’ DDL 
FROM DBA_USERS 
WHERE USERNAME = ‘&USERNAME’ 
UNION ALL 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’ROLE_GRANT’, USERNAME) || ‘/’ DDL 
FROM DBA_USERS 
where username =’&USERNAME’ 
UNION ALL 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’SYSTEM_GRANT’, USERNAME) || ‘/’ DDL 
FROM DBA_USERS 
WHERE USERNAME =’&USERNAME’ 
UNION ALL 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’OBJECT_GRANT’, USERNAME) || ‘/’ DDL 
FROM DBA_USERS 
WHERE USERNAME =’&USERNAME’ 
; 
SPOOL OFF;

EXIT;

SCRIPT2: build_role_script.sql

SET HEAD OFF 
SET PAGES 0 
SET LONG 9999999

PROMPT ENTER ROLE NAME 
ACCEPT ROLENAME

SPOOL create_my_roles.sql

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, ‘SQLTERMINATOR’, true);

REM GET ROLE DDLs FIRST 
SELECT DBMS_METADATA.GET_DDL(’ROLE’, role)||’;’ 
FROM DBA_ROLES 
WHERE ROLE = ‘&ROLENAME’; 
/

REM FETCH SYSTEM GRANTS/PRIVS FOR THIS ROLE 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’SYSTEM_GRANT’, role) 
FROM ROLE_SYS_PRIVS 
WHERE ROLE = ‘&ROLENAME’; 
/ 
REM FETCH OBJECT GRANTS FOR THIS ROLE 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’OBJECT_GRANT’, role) 
FROM ROLE_TAB_PRIVS 
WHERE ROLE = ‘&ROLENAME’; 
/ 
REM FETCH ROLES GRANTED TO THIS ROLE 
SELECT DBMS_METADATA.GET_GRANTED_DDL(’ROLE_GRANT’, role) 
FROM ROLE_ROLE_PRIVS 
WHERE ROLE = ‘&ROLENAME’; 
/

SPOOL OFF; 
EXIT;

How to Run

cd (Change Directory) to a directory where you want to generate the spool files. Save the above files with given names.

Connect to sqlplus with a DBA account and run the script on the prompt :

SQL>@build_role_script.sql

SQL>@build_user_script.sql

Result

Below are few lines from the output generated by the scripts.

GRANT “GATHER_SYSTEM_STATISTICS”  TO “DBA”; 
GRANT “SCHEDULER_ADMIN” TO “DBA” WITH ADMIN OPTION; 
GRANT “WM_ADMIN_ROLE” TO “DBA”; 
GRANT “JAVA_ADMIN” TO “DBA”; 
GRANT “JAVA_DEPLOY” TO “DBA”; 
GRANT “XDBADMIN” TO “DBA”; 
GRANT “XDBWEBSERVICES” TO “DBA”; 
GRANT “OLAP_DBA” TO “DBA”;