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