Oracle Privilege Matrix

Question

I want to make a privilege matrix in Oracle.

This is my query:

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 

and the result looks like this:

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      |

Is there any ways to make the result become like this?

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

Thank you

Answer 1

Take advantage of the fact that "YES" sorts after "NO" and change it to this:

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