------------------------------------------------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 15:20 11-Sep-2022 (006)
--
-- The DBT_UPRIVSD.sql script retrieves all directly granted privileges and roles 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")
);


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

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

SET HEAD ON
SELECT GRANTEE,PRIVILEGE
FROM   DBA_SYS_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME'));

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


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

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

SET HEAD ON
SELECT GR.GRANTEE,
       GR.GRANTED_ROLE,
       GR.DEFAULT_ROLE SET_AS_DEFAULT,
       TO_CHAR(US.CTIME,'DD-MON-YYYY HH24:MI') ROLE_CREATED,
       CASE WHEN US.SPARE1=0 
         THEN 'CUSTOM ROLE'
         ELSE 'ORACLE ROLE'
       END ROLE_TYPE
FROM
       DBA_ROLE_PRIVS GR,
       SYS.USER$ US
WHERE
       GR.GRANTEE=UPPER(TRIM('&USERNAME')) AND
       GR.GRANTED_ROLE=US.NAME
ORDER BY GR.GRANTED_ROLE;

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


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

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

SET HEAD ON
COL PRIVILEGE FOR A10 
SELECT GRANTEE, PRIVILEGE,
       OWNER || '.' || TABLE_NAME OBJECT_NAME
FROM   DBA_TAB_PRIVS
WHERE  GRANTEE=UPPER(TRIM('&USERNAME'));

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


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