Score:-1

Users and Roles Extraction in Oracle

ht flag

I've tried to write a query to have a users/roles extraction, i don't have much SQL skills as well as on oracle, so I started with this:

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;

The results look like this:

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

Anybody knows if it's possible to have something like this down, where the multiple GRANTED_ROLE is grouped into a single column?

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

or if another system table exist with the same behavior? the database is an Oracle DB 12c

Thanks a lot

Michael Hampton avatar
cz flag
This doesn't belong here. You can try posting on our sister site [dba.se].
Score:-1
ht flag

I answer myself, I've found the solution on another specific oracle forum:

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;

the key is the listagg that concatenate the A.GRANTED_ROLE's results into a single string, with ; separator.

Cheers

mangohost

Post an answer

Most people don’t grasp that asking a lot of questions unlocks learning and improves interpersonal bonding. In Alison’s studies, for example, though people could accurately recall how many questions had been asked in their conversations, they didn’t intuit the link between questions and liking. Across four studies, in which participants were engaged in conversations themselves or read transcripts of others’ conversations, people tended not to realize that question asking would influence—or had influenced—the level of amity between the conversationalists.