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>