Hello Community,
I am trying to use a script I found somewhere last year that would allow me to easily duplicate a user in another database. It uses UNION ALL to join multiple GET_GRANTED_DDL statements, but unfortunately, if one of those statements fails, all subsequent statements do not appear to execute.
Here is the script::
ACCEPT USER_NAME PROMPT 'ENTER USERNAME: '
DEFINE USER_NAME
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true);
set head off
set pages 0
set verify off
set long 9999999
spool /tmp/copy_&USER_NAME\.sql
SELECT DBMS_METADATA.GET_DDL('USER', USERNAME) || '/' DDL
FROM DBA_USERS
where username = upper('&USER_NAME')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('ROLE_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
where username = upper('&USER_NAME')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('SYSTEM_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
where username = upper('&USER_NAME')
UNION ALL
SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT', USERNAME) || '/' DDL
FROM DBA_USERS
where username = upper('&USER_NAME');
spool off;
And here is the output when no roles have been granted. Note that if I run the next select manually, I do get back granted system privileges.
SQL> @copy_user
ENTER USERNAME: john
DEFINE USER_NAME = "john" (CHAR)
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
CREATE USER "JOHN" IDENTIFIED BY VALUES '<password value>'
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP";
/
ERROR:
ORA-31608: specified object of type ROLE_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 3244
ORA-06512: at "SYS.DBMS_METADATA", line 4960
ORA-06512: at line 1
I looked at UNION and INTERSECT but these don't appear to be valid substitutes for UNION ALL. Can anyone suggest another method of running this script so that null values won't cause it to fail? Apologies if this has been asked before. I could not locate a discussion of this issue.
Thanks,
John