jquery - Oracle特权矩阵

我想在Oracle中建立一个特权矩阵。

这是我的查询：

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

结果看起来像这样：

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      |

有什么方法可以使结果变成这样？

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

谢谢

最佳答案

利用“是”在“否”之后排序的事实，并将其更改为：

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