Ameer Khan

Ameer Khan

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>
Categories

Leave a Reply

Your email address will not be published. Required fields are marked *