User Matrix dengan Oracle

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

Dan hasilnya bakal kayak gini:

TABLE_NAME | SELECT | INSERT | UPDATE | DELETE | ALTER | EXECUTE |
ACCOUNT    | YES    | NO     | NO     | NO     | NO    | NO      |
ACCOUNT    | NO     | YES    | NO     | NO     | NO    | NO      |
ACCOUNT    | NO     | no     | YES    | NO     | NO    | NO      |

Tapi kayaknya ini kurang informatif. Setelah gugling sana-sini dan tanya sana-sini, ada orang di stackoverflow nge jawab kayak gini:

SELECT 
    DTP.TABLE_NAME,  
    MAX(DECODE(DTP.PRIVILEGE, 'SELECT', 'YES', 'NO')) "SELECT",
    MAX(DECODE(DTP.PRIVILEGE, 'INSERT', 'YES', 'NO')) "INSERT",
    MAX(DECODE(DTP.PRIVILEGE, 'UPDATE', 'YES', 'NO')) "UPDATE",
    MAX(DECODE(DTP.PRIVILEGE, 'DELETE', 'YES', 'NO')) "DELETE",
    MAX(DECODE(DTP.PRIVILEGE, 'ALTER', 'YES', 'NO')) "ALTER",
    MAX(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)
GROUP BY DTP.TABLE_NAME                       
ORDER BY 
    TABLE_NAME

Maka hasilnya akan kayak gini:

ABLE_NAME | SELECT | INSERT | UPDATE | DELETE | ALTER | EXECUTE |
ACCOUNT    | YES    | YES    | YES    | NO     | NO    | NO      |

sekian dulu catatan hari ini.

Source