Вопросы Теги

Извлечение пользователей и ролей в 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
задан 6 July 2021 в 16:06
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;

ключ - listagg, который объединяет результаты A.GRANTED_ROLE в одну строку, с разделителем ;.

Cheers

-1
ответ дан 28 July 2021 в 15:26

Теги

Похожие вопросы