-1

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

bind2lrz
  • 7
  • 5

1 Answers1

-1

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

bind2lrz
  • 7
  • 5