SQL & PL/SQL

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.