Question: How to extract User DDL and all privileges granted including roles, system and object privileges, tablespace quota and non-default profile using dbms_metadata.get_granted_ddl?
Connect to target database and execute with DBA privileges. Example for user “TEST” – Make sure to put the username in uppercase.
set longchunksize 20000 pagesize 0 feedback off verify off trimspool on column Extracted_DDL format a1000 EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE); EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE); undefine User_in_Uppercase; set linesize 1000 set long 2000000000 select (case when ((select count(*) from dba_users where username = '&&User_in_Uppercase' and profile <> 'DEFAULT') > 0) then chr(10)||' -- Note: Profile'||(select dbms_metadata.get_ddl('PROFILE', u.profile) AS ddl from dba_users u where u.username = '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: Default profile, no need to create!') end ) from dual UNION ALL select (case when ((select count(*) from dba_users where username = '&User_in_Uppercase') > 0) then ' -- Note: Create user statement'||dbms_metadata.get_ddl ('USER', '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: User not found!') end ) Extracted_DDL from dual UNION ALL select (case when ((select count(*) from dba_ts_quotas where username = '&User_in_Uppercase') > 0) then ' -- Note: TBS quota'||dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: No TS Quotas found!') end ) from dual UNION ALL select (case when ((select count(*) from dba_role_privs where grantee = '&User_in_Uppercase') > 0) then ' -- Note: Roles'||dbms_metadata.get_granted_ddl ('ROLE_GRANT', '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: No granted Roles found!') end ) from dual UNION ALL select (case when ((select count(*) from V$PWFILE_USERS where username = '&User_in_Uppercase' and SYSDBA='TRUE') > 0) then ' -- Note: sysdba'||chr(10)||to_clob (' GRANT SYSDBA TO '||'"'||'&User_in_Uppercase'||'"'||';') else to_clob (chr(10)||' -- Note: No sysdba administrative Privilege found!') end ) from dual UNION ALL select (case when ((select count(*) from dba_sys_privs where grantee = '&User_in_Uppercase') > 0) then ' -- Note: System Privileges'||dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: No System Privileges found!') end ) from dual UNION ALL select (case when ((select count(*) from dba_tab_privs where grantee = '&User_in_Uppercase') > 0) then ' -- Note: Object Privileges'||dbms_metadata.get_granted_ddl ('OBJECT_GRANT', '&User_in_Uppercase') else to_clob (chr(10)||' -- Note: No Object Privileges found!') end ) from dual /
Enter value for user_in_uppercase: TEST -- Note: Profile CREATE PROFILE "MONITORING_PROFILE" LIMIT COMPOSITE_LIMIT DEFAULT SESSIONS_PER_USER DEFAULT CPU_PER_SESSION DEFAULT CPU_PER_CALL DEFAULT LOGICAL_READS_PER_SESSION DEFAULT LOGICAL_READS_PER_CALL DEFAULT IDLE_TIME DEFAULT CONNECT_TIME DEFAULT PRIVATE_SGA DEFAULT FAILED_LOGIN_ATTEMPTS UNLIMITED PASSWORD_LIFE_TIME DEFAULT PASSWORD_REUSE_TIME DEFAULT PASSWORD_REUSE_MAX DEFAULT PASSWORD_VERIFY_FUNCTION DEFAULT PASSWORD_LOCK_TIME DEFAULT PASSWORD_GRACE_TIME DEFAULT ; -- Note: Create user statement CREATE USER "TEST" IDENTIFIED BY VALUES '2B316C212D67' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" PROFILE "MONITORING_PROFILE"; -- Note: TBS quota DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "TEST" QUOTA 104857600 ON "USERS"'; EXECUTE IMMEDIATE SQLSTR; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY'''; EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT; IF TEMP_COUNT = 1 THEN RETURN; ELSE RAISE; END IF; ELSE RAISE; END IF; END; / -- Note: Roles GRANT "SELECT_CATALOG_ROLE" TO "TEST"; -- Note: sysdba GRANT SYSDBA TO "TEST"; -- Note: System Privileges GRANT UNLIMITED TABLESPACE TO "TEST"; -- Note: Object Privileges GRANT SELECT ON "SYS"."DBA_TABLES" TO "TEST"; SQL>