How to extract user DDL and all privileges of a user in oracle

We often require to extract information such as User DDL and all privileges granted including roles, system and object privileges, tablespace quota etc. Using the following method we can extract it easily.

Create a .sql file and paste the following code inside.

Note:- While executing this sql make sure to enter the username in UPPERCASE.

vi info.sql

-- Connect to target database and execute with a user that has DBA privileges   -- Example for user "CRPDTA" - Make sure to put the username in uppercase.   SET LONGCHUNKSIZE 20000 PAGESIZE 0 FEEDBACK OFF VERIFY OFF TRIMPOOL 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 /

 

Output:-

Login into database and run the sql we created above.. We used username as “PY920”

sqlplus / as sysdba SQL> @info.sql Enter value for user_in_uppercase: PY920    -- Note: Profile    CREATE PROFILE "UNLIM_LOG"     LIMIT          COMPOSITE_LIMIT UNLIMITED          SESSIONS_PER_USER UNLIMITED          CPU_PER_SESSION UNLIMITED          CPU_PER_CALL UNLIMITED          LOGICAL_READS_PER_SESSION UNLIMITED          LOGICAL_READS_PER_CALL UNLIMITED          IDLE_TIME UNLIMITED          CONNECT_TIME UNLIMITED          PRIVATE_SGA UNLIMITED          FAILED_LOGIN_ATTEMPTS UNLIMITED          PASSWORD_LIFE_TIME UNLIMITED          PASSWORD_REUSE_TIME UNLIMITED          PASSWORD_REUSE_MAX UNLIMITED          PASSWORD_VERIFY_FUNCTION DEFAULT          PASSWORD_LOCK_TIME UNLIMITED          PASSWORD_GRACE_TIME UNLIMITED          INACTIVE_ACCOUNT_TIME DEFAULT          PASSWORD_ROLLOVER_TIME DEFAULT ;    -- Note: Create user statement    CREATE USER "PY920" IDENTIFIED BY VALUES 'S:E927BDEC46440F4264941D55A483B1C07A522B852D94203349093C5B2F2D;T:5FC638A2DA78087F85E7BABF9EA95404BD400DC6923181FE420BC91A8A11C6CD4B5D95103E1FB27E01981266C7B3F43C9AE5851EE4AFFC7BDC6066E63598D80C51295AC44FAD0651B66B72A4C2E0E639'       DEFAULT TABLESPACE "PY920T"       TEMPORARY TABLESPACE "TEMP2"       PROFILE "UNLIM_LOG";  ALTER USER "PY920" LOCAL TEMPORARY TABLESPACE "TEMP2";    -- Note: TBS quota   DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "PY920" QUOTA UNLIMITED ON "PY920I"';   EXECUTE IMMEDIATE SQLSTR; EXCEPTION   WHEN OTHERS THEN     IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''PY920I'' AND CONTENTS = ''TEMPORARY''';       EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT;       IF TEMP_COUNT = 1 THEN RETURN;       ELSE RAISE;       END IF;     ELSE       RAISE;     END IF; END; /   DECLARE   TEMP_COUNT NUMBER;   SQLSTR VARCHAR2(200); BEGIN   SQLSTR := 'ALTER USER "PY920" QUOTA UNLIMITED ON "PY920T"';   EXECUTE IMMEDIATE SQLSTR; EXCEPTION   WHEN OTHERS THEN     IF SQLCODE = -30041 THEN       SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES               WHERE TABLESPACE_NAME = ''PY920T'' 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 "JDEUSER" TO "PY920";    GRANT "JDE_ROLE" TO "PY920";      -- Note: No sysdba administrative Privilege found!      -- Note: No System Privileges found!      -- Note: No Object Privileges found!   SQL>