------------------------------------------------------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 15:20 11-Sep-2022 (006)
--
-- The DBT_UPRIVSI.sql script retrieves both directly and indirectly granted privileges to a user or a role.
------------------------------------------------------------------------------------------------------------


-------------------------------------
-- Formatting SQL*Plus Output
-------------------------------------

SET PAGES 5000
SET LINES 200
SET VERIFY OFF
SET SQLNUMBER OFF
SET FEEDBACK OFF
SET NEWPAGE NONE
COL GRANTEE FOR A25 
VAR NONE VARCHAR2(20);
EXECUTE :NONE := '/*' || CHR(10) || '  None' || CHR(10) || '*/'

COL TABHEADER FOR A30
COL VALUE FOR A25
COL COLUMNS FOR A25
COL PRIVILEGE FOR A40 
COL ROLE_CREATED FOR A17
COL SET_AS_DEFAULT FOR A15
COL ROLE_TYPE FOR A12
COL GRANTED_ROLE FOR A30
COL OBJECT_NAME FOR A40


-------------------------------------
-- A variable for storing a username or role
-------------------------------------

PROMPT
ACCEPT USERNAME PROMPT 'USERNAME or ROLE: '

SET HEAD OFF
SELECT CHR(10) || LPAD('*',25,'*') || CHR(10) || '** Account details' || CHR(10) || LPAD('*',25,'*') TABHEADER FROM DUAL;
WITH ACCOUNT_DETAILS AS (
SELECT UPPER(D.NAME) "Database",
       TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI') "Systimestamp",
       DECODE(U.TYPE#,0,'ROLE','USER') "Account Type",
       U.NAME "Account Name",
       TO_CHAR(U.CTIME,'DD-MON-YYYY HH24:MI') "Created",
       CASE WHEN U.TYPE#=0 THEN
         DECODE(U.SPARE1,0,'USER CUSTOM ROLE','DEFAULT ORACLE ROLE')
       END "Role Type",
       DECODE(U.TYPE#,1,TO_CHAR(U.EXPTIME,'DD-MON-YYYY HH24:MI'),NULL) "Expiry Date",
       DECODE(U.TYPE#,1,TO_CHAR(U.LTIME,'DD-MON-YYYY HH24:MI'),NULL) "Lock Date",
       DECODE(U.TYPE#,1,S.STATUS,NULL) "Account Status",
       DECODE(U.TYPE#,1,P.NAME,NULL) "User Profile",
       DECODE(U.TYPE#,1,T1.NAME,NULL) "Default Tablespace",
       DECODE(U.TYPE#,1,T2.NAME,NULL) "Temporary Tablespace"
FROM
       SYS.V_$DATABASE D,
       SYS.USER$ U,
       SYS.TS$ T1,
       SYS.TS$ T2,
       SYS.USER_ASTATUS_MAP S,
       SYS.PROFNAME$ P
WHERE
       U.NAME=UPPER(TRIM('&USERNAME')) AND
       U.DATATS#=T1.TS# AND
       U.TEMPTS#=T2.TS# AND
       U.ASTATUS=S.STATUS# AND
       U.RESOURCE$=P.PROFILE#
)
SELECT DECODE(COUNT(*),0,'Account doest not exist.') VALUE, NULL COLUMNS FROM ACCOUNT_DETAILS
UNION ALL
SELECT * FROM ACCOUNT_DETAILS
UNPIVOT (
VALUE FOR COLUMNS IN ("Database", "Systimestamp", "Account Name", "Account Type", "Role Type", "Created", "Expiry Date", "Lock Date", "Account Status", "User Profile", "Default Tablespace", "Temporary Tablespace")
);


------------------------------------
-- Roles : DBA_ROLE_PRIVS
------------------------------------

SELECT CHR(10) || CHR(10) || LPAD('*',25,'*') || CHR(10) || '** Granted Roles'  || CHR(10) || '** Directly and Indirectly' || CHR(10) || LPAD('*',25,'*') TABHEADER FROM DUAL;

SET HEAD ON
SELECT DISTINCT GRANTEE,GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(TRIM('&USERNAME'))
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
ORDER BY 1,2;

SET HEAD OFF
SELECT CASE 
         WHEN COUNT(*) = 0 THEN :NONE
       END AS DBA_ROLE_PRIVS
FROM   DBA_ROLE_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME'));


------------------------------------
-- System privileges: DBA_SYS_PRIVS
------------------------------------

SELECT CHR(10) || LPAD('*',25,'*') || CHR(10) || '** System privileges'  || CHR(10) || '** Directly and Indirectly' || CHR(10) || LPAD('*',25,'*') TABHEADER FROM DUAL;

SET HEAD ON
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(TRIM('&USERNAME'))
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE,PRIVILEGE
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME')) OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2;

SET HEAD OFF
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(TRIM('&USERNAME'))
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT CASE 
         WHEN COUNT(*) = 0 THEN :NONE
       END AS DBA_SYS_PRIVS
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME')) OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST);


------------------------------------
-- Object privileges: DBA_TAB_PRIVS
------------------------------------

SELECT CHR(10) || LPAD('*',25,'*') || CHR(10) || '** Object privileges' || CHR(10) || '** Directly and Indirectly' || CHR(10) || LPAD('*',25,'*') TABHEADER FROM DUAL;

SET HEAD ON
COL PRIVILEGE FOR A10 
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(TRIM('&USERNAME'))
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT GRANTEE, PRIVILEGE,
       OWNER || '.' || TABLE_NAME OBJECT_NAME
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME')) OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST)
ORDER BY 1,2,3;

SET HEAD OFF
COL PRIVILEGE FOR A10 
WITH ROLE_LIST AS (
SELECT DISTINCT GRANTED_ROLE FROM DBA_ROLE_PRIVS
START WITH GRANTEE=UPPER(TRIM('&USERNAME'))
CONNECT BY PRIOR GRANTED_ROLE=GRANTEE
)
SELECT CASE 
         WHEN COUNT(*) = 0 THEN :NONE
       END AS DBA_TAB_PRIVS
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME')) OR 
       GRANTEE IN (SELECT GRANTED_ROLE FROM ROLE_LIST);
SET HEAD ON


------------------------------------------------------------
-- Script Version 15:20 11-Sep-2022 (006)
------------------------------------------------------------