☰

Oracle 中的用户和角色提取

tag-icon • tag-icon database sql oracle
Oracle 中的用户和角色提取

我试图编写一个查询来提取用户/角色，但我的 SQL 技能和 Oracle 技能不太一样，因此我从这个开始：

SELECT A.GRANTEE, A.GRANTED_ROLE, B.ACCOUNT_STATUS FROM DBA_ROLE_PRIVS A
JOIN DBA_USERS B ON A.GRANTEE = B.USERNAME
WHERE B.ACCOUNT_STATUS = 'OPEN'
AND A.GRANTEE NOT IN ('SYS', 'SYSTEM')
ORDER BY GRANTEE;

结果如下：

GRANTEE     GRANTED_ROLE            ACCOUNT_STATUS
PIPPO       CONNECT             OPEN
PLUTO       CONNECT             OPEN
PAPERINO    DATAPUMP_IMP_FULL_DATABASE  OPEN
PAPERINO    DATAPUMP_EXP_FULL_DATABASE  OPEN
ZIOPAPERONE RESOURCE            OPEN
ZIOPAPERONE CONNECT             OPEN
PAPEROGA    CONNECT             OPEN
PAPEROGA    RESOURCE            OPEN

有人知道是否有可能实现这样的功能，将多个 GRANTED_ROLE 分组到单个列中？

GRANTEE         GRANTED_ROLE                                          ACCOUNT_STATUS
PIPPO           CONNECT                                                     OPEN
PLUTO           CONNECT                                                     OPEN
PAPERINO        DATAPUMP_IMP_FULL_DATABASE, DATAPUMP_EXP_FULL_DATABASE      OPEN
ZIOPAPERONE     RESOURCE,  CONNECT                                          OPEN
PAPEROGA        CONNECT, RESOURCE                                           OPEN

或者是否存在具有相同行为的另一个系统表？数据库是 Oracle DB 12c

多谢

答案1

我自己回答，我在另一个特定的 Oracle 论坛上找到了解决方案：

SELECT B.ACCOUNT_STATUS, A.GRANTEE, listagg(A.GRANTED_ROLE, ';') within group (order by A.GRANTED_ROLE) FROM DBA_ROLE_PRIVS A
JOIN DBA_USERS B ON A.GRANTEE = B.USERNAME
WHERE B.ACCOUNT_STATUS = 'OPEN'
GROUP BY A.GRANTEE, B.ACCOUNT_STATUS
ORDER BY GRANTEE;

关键是将 A.GRANTED_ROLE 的结果连接成一个字符串的 listagg，使用；分隔符。

干杯

相关内容