¿Cómo mostrar todos los privilegios de un usuario en Oracle?

Hay varios scripts que se pueden encontrar que harán eso dependiendo de cuán complejo quieras ser. Yo personalmente usaría el script find_all_privs de Pete Finnigan.

Si deseas escribirlo tú mismo, la consulta se vuelve bastante desafiante. Los usuarios pueden ser otorgados privilegios del sistema que son visibles en DBA_SYS_PRIVS. También pueden ser otorgados privilegios de objeto que son visibles en DBA_TAB_PRIVS. Y pueden ser otorgados roles que son visibles en DBA_ROLE_PRIVS (los roles pueden ser predeterminados o no predeterminados y pueden requerir una contraseña, por lo que solo porque a un usuario se le haya otorgado un rol no significa que el usuario necesariamente pueda usar los privilegios que adquirió a través del rol de forma predeterminada). Pero esos roles, a su vez, pueden ser otorgados privilegios del sistema, privilegios de objeto y roles adicionales que se pueden ver al mirar ROLE_SYS_PRIVS, ROLE_TAB_PRIVS y ROLE_ROLE_PRIVS. El script de Pete recorre esas relaciones para mostrar todos los privilegios que terminan fluyendo hacia un usuario.

Otro recurso útil:

http://psoug.org/reference/roles.html

• DBA_SYS_PRIVS
• DBA_TAB_PRIVS
• DBA_ROLE_PRIVS

Mientras la respuesta de Raviteja Vutukuri funciona y es rápida de armar, no es particularmente flexible para variar los filtros y no ayuda demasiado si estás buscando hacer algo programáticamente. Así que armé mi propia consulta:

SELECT
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME,
    LISTAGG(GRANT_TARGET, ',') WITHIN GROUP (ORDER BY GRANT_TARGET) AS GRANT_SOURCES, -- Lista las fuentes del permiso
    MAX(ADMIN_OR_GRANT_OPT) AS ADMIN_OR_GRANT_OPT, -- MAX actúa como un OR booleano eligiendo 'YES' sobre 'NO'
    MAX(HIERARCHY_OPT) AS HIERARCHY_OPT -- MAX actúa como un OR booleano eligiendo 'YES' sobre 'NO'
FROM (
    -- Obtiene todos los roles que un usuario tiene, incluso los heredados
    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
    )
    SELECT
        PRIVILEGE,
        OBJ_OWNER,
        OBJ_NAME,
        USERNAME,
        REPLACE(GRANT_TARGET, USERNAME, 'Direct to user') AS GRANT_TARGET,
        ADMIN_OR_GRANT_OPT,
        HIERARCHY_OPT
    FROM (
        -- Privilegios del sistema otorgados directamente a los usuarios
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- Privilegios del sistema otorgados a los usuarios a través de roles
        SELECT PRIVILEGE, NULL AS OBJ_OWNER, NULL AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, GRANTEE AS GRANT_TARGET, ADMIN_OPTION AS ADMIN_OR_GRANT_OPT, NULL AS HIERARCHY_OPT
        FROM DBA_SYS_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_SYS_PRIVS.GRANTEE
        UNION ALL
        -- Privilegios de objetos otorgados directamente a los usuarios
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, GRANTEE AS USERNAME, GRANTEE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        WHERE GRANTEE IN (SELECT USERNAME FROM DBA_USERS)
        UNION ALL
        -- Privilegios de objetos otorgados a los usuarios a través de roles
        SELECT PRIVILEGE, OWNER AS OBJ_OWNER, TABLE_NAME AS OBJ_NAME, ALL_ROLES_FOR_USER.GRANTED_USER AS USERNAME, ALL_ROLES_FOR_USER.GRANTED_ROLE AS GRANT_TARGET, GRANTABLE, HIERARCHY
        FROM DBA_TAB_PRIVS
        JOIN ALL_ROLES_FOR_USER ON ALL_ROLES_FOR_USER.GRANTED_ROLE = DBA_TAB_PRIVS.GRANTEE
    ) ALL_USER_PRIVS
    -- Ajusta tu filtro aquí
    WHERE USERNAME = 'NOMBRE_USUARIO'
) DISTINCT_USER_PRIVS
GROUP BY
    PRIVILEGE,
    OBJ_OWNER,
    OBJ_NAME,
    USERNAME
;

Ventajas:

• Puedo filtrar fácilmente por muchos datos diferentes, como el objeto, el privilegio, si es a través de un rol particular, etc. cambiando solo esa cláusula WHERE.
• Es una sola consulta, lo que significa que no tengo que componer mentalmente los resultados juntos.
• Resuelve el problema de si pueden otorgar el privilegio o no y si incluye los privilegios para subobjetos (la parte "jerárquica") en diferentes fuentes del privilegio.
• Es fácil ver todo lo que necesito hacer para revocar el privilegio, ya que lista todas las fuentes del privilegio.
• Combina los privilegios de tablas y de sistema en una vista única coherente, lo que nos permite enumerar todos los privilegios de un usuario de una vez.
• Es una consulta, no una función que muestra todo esto en DBMS_OUTPUT o algo así (en comparación con el script vinculado de Pete Finnigan). Esto lo hace útil para uso programático y para exportar.
• El filtro no se repite; solo aparece una vez. Esto facilita el cambio.
• La subconsulta se puede sacar fácilmente si necesitas examinarla por cada GRANT individual.

Versión más simple de una sola consulta de Oracle.

CON data 
     SELECCIONAR granted_role 
         DE   dba_role_privs 
         CONECTAR POR PRIOR granted_role = grantee 
         EMPEZAR CON grantee = '&USER') 
SELECCIONAR 'SYSTEM'     typ, 
       grantee      grantee, 
       privilege    priv, 
       admin_option ad, 
       '--'         tabnm, 
       '--'         colnm, 
       '--'         owner 
DE   dba_sys_privs 
DÓNDE grantee = '&USER' 
        O grantee EN (SELECCIONAR granted_role 
                       DE   data) 
UNIÓN 
SELECCIONAR 'TABLE'    typ, 
       grantee    grantee, 
       privilege  priv, 
       grantable  ad, 
       table_name tabnm, 
       '--'       colnm, 
       owner      owner 
DE   dba_tab_privs 
DÓNDE grantee = '&USER' 
        O grantee EN (SELECCIONAR granted_role 
                       DE   data) 
ORDENAR POR 1;