Kadang dalam sebuah aplikasi kita membutuhkan matrix privilege pada sebuah schema. Nah untuk nampilinnya kita bisa pake query kayak gini:
SELECT
DTP.TABLE_NAME,
DECODE(DTP.PRIVILEGE, 'SELECT', 'YES', 'NO') "SELECT",
DECODE(DTP.PRIVILEGE, 'INSERT', 'YES', 'NO') "INSERT",
DECODE(DTP.PRIVILEGE, 'UPDATE', 'YES', 'NO') "UPDATE",
DECODE(DTP.PRIVILEGE, 'DELETE', 'YES', 'NO') "DELETE",
DECODE(DTP.PRIVILEGE, 'ALTER', 'YES', 'NO') "ALTER",
DECODE(DTP.PRIVILEGE, 'EXECUTE', 'YES', 'NO') "EXECUTE"
FROM
SYS.DBA_TAB_PRIVS DTP
WHERE
DTP.GRANTEE = 'SUPPLIER'
OR DTP.GRANTEE IN (SELECT DRP.GRANTED_ROLE
FROM dba_role_privs DRP
START WITH grantee = 'SUPPLIER'
CONNECT BY PRIOR DRP.GRANTED_ROLE = DRP.GRANTEE)
ORDER BY
TABLE_NAME
Continue reading →