ORACLE TABLE PRIVILEGE

----- ORACLE OBJECTS PRIVILEGE -----
WITH W_PRIVS AS
(
SELECT GRANTEE
, TABLE_NAME
, NVL(MAX(CASE WHEN PRIVILEGE = 'SELECT' THEN 'S' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'INSERT' THEN 'I' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'UPDATE' THEN 'U' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'DELETE' THEN 'D' END),'_')
||NVL(MAX(CASE WHEN PRIVILEGE = 'EXECUTE' THEN 'E' END),'_') AS PRIVILEGE
FROM USER_TAB_PRIVS
GROUP BY GRANTEE
, TABLE_NAME
ORDER BY 1,2
)
, W_OBJ AS
(
SELECT A.TABLE_NAME
, COUNT(DISTINCT A.GRANTEE) AS GRANTEE_CNT
, MAX(CASE WHEN B.PNO = 1 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_01
, MAX(CASE WHEN B.PNO = 2 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_02
, MAX(CASE WHEN B.PNO = 3 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_03
, MAX(CASE WHEN B.PNO = 4 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_04
, MAX(CASE WHEN B.PNO = 5 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_05
, MAX(CASE WHEN B.PNO = 6 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_06
, MAX(CASE WHEN B.PNO = 7 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_07
, MAX(CASE WHEN B.PNO = 8 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_08
, MAX(CASE WHEN B.PNO = 9 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_09
, MAX(CASE WHEN B.PNO = 10 THEN PRIVILEGE||' / '||A.GRANTEE END) AS GRANTEE_10
FROM W_PRIVS A
,(
SELECT GRANTEE
, ROW_NUMBER() OVER (ORDER BY GRANTEE) AS PNO
FROM W_PRIVS
GROUP BY GRANTEE
) B
WHERE A.GRANTEE = B.GRANTEE
GROUP BY A.TABLE_NAME
)
SELECT /*+ RULE */
A.OBJECT_TYPE
, A.OBJECT_NAME
, B.COMMENTS
, NVL(C.GRANTEE_CNT,0) AS GRANTEE_CNT
, C.GRANTEE_01
, C.GRANTEE_02
, C.GRANTEE_03
, C.GRANTEE_04
, C.GRANTEE_05
, C.GRANTEE_06
, C.GRANTEE_07
, C.GRANTEE_08
, C.GRANTEE_09
, C.GRANTEE_10
FROM USER_OBJECTS A
LEFT JOIN USER_TAB_COMMENTS B
ON A.OBJECT_NAME = B.TABLE_NAME
LEFT JOIN W_OBJ C
ON A.OBJECT_NAME = C.TABLE_NAME
WHERE A.OBJECT_TYPE IN ('TABLE','TABLE PARTITION','VIEW','FUNCTION','PROCEDURE')
ORDER BY 1,2
;