Hi everyone,
I am using below query to output distinct privileges assigned to a use in side ways. Column GRANTED_PRIVILEGES displaying duplicate privileges. Need help on how I could product distinct value for column GRANTED_PRIVILEGES.
SELECT
p.PDB_NAME AS "DB Name",
p.DBID AS "Database ID",
u.USERNAME,
LISTAGG(DISTINCT privs.GRANT_TARGET, ';') WITHIN GROUP (ORDER BY privs.GRANT_TARGET) AS GRANT_TARGET,
RTRIM(XMLAGG(XMLELEMENT(E, privs.PRIVILEGE, ';').EXTRACT('//text()') ORDER BY privs.PRIVILEGE).GETCLOBVAL(), ', ') AS GRANTED_PRIVILEGES,
u.PROFILE
FROM (
-- Recursive query to fetch all inherited roles for a user
WITH ALL_ROLES_FOR_USER AS (
SELECT DISTINCT CONNECT_BY_ROOT GRANTEE AS GRANTED_USER, GRANTED_ROLE
FROM DBA_ROLE_PRIVS
CONNECT BY GRANTEE = PRIOR GRANTED_ROLE
),
-- Fetch all user privileges (direct and inherited)
USER_PRIVS AS (
-- Direct system privileges
SELECT sp.PRIVILEGE, sp.GRANTEE AS USERNAME, sp.GRANTEE AS GRANT_TARGET
FROM DBA_SYS_PRIVS sp
WHERE sp.GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION ALL
-- System privileges via roles
SELECT sp.PRIVILEGE, ar.GRANTED_USER AS USERNAME, sp.GRANTEE AS GRANT_TARGET
FROM DBA_SYS_PRIVS sp
JOIN ALL_ROLES_FOR_USER ar ON sp.GRANTEE = ar.GRANTED_ROLE
UNION ALL
-- Direct object privileges
SELECT tp.PRIVILEGE, tp.GRANTEE AS USERNAME, tp.GRANTEE AS GRANT_TARGET
FROM DBA_TAB_PRIVS tp
WHERE tp.GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
UNION ALL
-- Object privileges via roles
SELECT tp.PRIVILEGE, ar.GRANTED_USER AS USERNAME, ar.GRANTED_ROLE AS GRANT_TARGET
FROM DBA_TAB_PRIVS tp
JOIN ALL_ROLES_FOR_USER ar ON tp.GRANTEE = ar.GRANTED_ROLE
)
-- Associate PDB_NAME and DBID
SELECT
up.USERNAME,
up.PRIVILEGE,
up.GRANT_TARGET,
p.PDB_NAME,
p.DBID
FROM USER_PRIVS up
CROSS JOIN DBA_PDBS p -- Ensures PDB association
) privs
JOIN DBA_USERS u ON privs.USERNAME = u.USERNAME
JOIN DBA_PDBS p ON SYS_CONTEXT('USERENV', 'CON_NAME') = p.PDB_NAME -- Associate correct PDB
WHERE u.USERNAME IN ('ABCD') -- Filter for a specific user
GROUP BY p.PDB_NAME, p.DBID, u.USERNAME, u.PROFILE
ORDER BY p.PDB_NAME, u.USERNAME;
Below is the sample out →

There are multiple “DELETE” in column GRANTED_PRIVILEGES, I want it to appear once only.
Thank you in advance.