SQL script that retrieves all privileges of a specific user.

Below SQL script that retrieves all privileges of a specific user in an Oracle database, including:

• System privileges

• Object privileges

• Roles granted

• Privileges granted through roles (system & object)

-- Set the user you want to investigate
DEFINE target_user = 'PETER';
-- 1. System privileges directly granted to the user
SELECT 
    'SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_SYS_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 2. Object privileges directly granted to the user
SELECT 
    'OBJECT_PRIVILEGE' AS PRIV_TYPE,
    GRANTEE,
    PRIVILEGE,
    OWNER,
    TABLE_NAME AS OBJECT_NAME,
    GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_TAB_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 3. Roles granted to the user
SELECT 
    'ROLE_GRANTED' AS PRIV_TYPE,
    GRANTEE,
    GRANTED_ROLE AS PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS
WHERE 
    GRANTEE = UPPER('&target_user')
UNION ALL
-- 4. System privileges granted via roles
SELECT 
    'ROLE_SYSTEM_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RSP.PRIVILEGE,
    NULL AS OWNER,
    NULL AS OBJECT_NAME,
    RSP.ADMIN_OPTION AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_SYS_PRIVS RSP ON RP.GRANTED_ROLE = RSP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
UNION ALL
-- 5. Object privileges granted via roles
SELECT 
    'ROLE_OBJECT_PRIVILEGE' AS PRIV_TYPE,
    RP.GRANTEE,
    RTP.PRIVILEGE,
    RTP.OWNER,
    RTP.TABLE_NAME AS OBJECT_NAME,
    RTP.GRANTABLE AS "CAN_ADMIN"
FROM 
    DBA_ROLE_PRIVS RP
JOIN 
    ROLE_TAB_PRIVS RTP ON RP.GRANTED_ROLE = RTP.ROLE
WHERE 
    RP.GRANTEE = UPPER('&target_user')
ORDER BY 
    PRIV_TYPE, PRIVILEGE;