VIEW: DVSYS.DBA_DV_USER_PRIVS_ALL

Source

View Text - Preformatted

SELECT
    dbu.name
       , decode(ue.name,dbu.name,'DIRECT',ue.name)
       , tpm.name
       , u.name
       , o.name
FROM sys.objauth$ oa,
    sys.obj$ o,
    sys.user$ u,
    sys.user$ ue,
    sys.user$ dbu,
    sys.table_privilege_map tpm
WHERE oa.obj# = o.obj#
  AND oa.col# IS NULL
  AND oa.privilege# = tpm.privilege
  AND u.user# = o.owner#
  AND oa.grantee# = ue.user#
  AND dbu.type# = 1
  AND (oa.grantee# = dbu.user#
        or
       oa.grantee#  in (SELECT /*+ connect_by_filtering */ DISTINCT privilege#
                        FROM (select * from sys.sysauth$ where privilege#>0)
                        CONNECT BY grantee#=prior privilege#
                        START WITH grantee#=dbu.user#))
UNION ALL
SELECT dbu.name
       ,DECODE(ue.name,dbu.name,'DIRECT',ue.name)
       ,spm.name
       ,DECODE (INSTR(spm.name,' ANY '),0, NULL, '%')
       ,DECODE (INSTR(spm.name,' ANY '),0, NULL, '%')
FROM sys.sysauth$ oa,
     sys.user$ ue,
     sys.user$ dbu,
     sys.system_privilege_map spm
WHERE
      oa.privilege# = spm.privilege
  AND oa.grantee# = ue.user#
  AND oa.privilege# < 0
  AND dbu.type# = 1
  AND (oa.grantee# = dbu.user#
        or
       oa.grantee#  in (SELECT /*+ connect_by_filtering */ DISTINCT privilege#
                        FROM (select * from sys.sysauth$ where privilege#>0)
                        CONNECT BY grantee#=prior privilege#
                        START WITH grantee#=dbu.user#))

View Text - HTML Formatted

SELECT DBU.NAME
, DECODE(UE.NAME
, DBU.NAME
, 'DIRECT'
, UE.NAME)
, TPM.NAME
, U.NAME
, O.NAME FROM SYS.OBJAUTH$ OA
, SYS.OBJ$ O
, SYS.USER$ U
, SYS.USER$ UE
, SYS.USER$ DBU
, SYS.TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.COL# IS NULL
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND U.USER# = O.OWNER#
AND OA.GRANTEE# = UE.USER#
AND DBU.TYPE# = 1
AND (OA.GRANTEE# = DBU.USER# OR OA.GRANTEE# IN (SELECT /*+ CONNECT_BY_FILTERING */ DISTINCT PRIVILEGE#
FROM (SELECT *
FROM SYS.SYSAUTH$
WHERE PRIVILEGE#>0) CONNECT BY GRANTEE#=PRIOR PRIVILEGE# START WITH GRANTEE#=DBU.USER#)) UNION ALL SELECT DBU.NAME
, DECODE(UE.NAME
, DBU.NAME
, 'DIRECT'
, UE.NAME)
, SPM.NAME
, DECODE (INSTR(SPM.NAME
, ' ANY ')
, 0
, NULL
, '%')
, DECODE (INSTR(SPM.NAME
, ' ANY ')
, 0
, NULL
, '%') FROM SYS.SYSAUTH$ OA
, SYS.USER$ UE
, SYS.USER$ DBU
, SYS.SYSTEM_PRIVILEGE_MAP SPM WHERE OA.PRIVILEGE# = SPM.PRIVILEGE
AND OA.GRANTEE# = UE.USER#
AND OA.PRIVILEGE# < 0
AND DBU.TYPE# = 1
AND (OA.GRANTEE# = DBU.USER# OR OA.GRANTEE# IN (SELECT /*+ CONNECT_BY_FILTERING */ DISTINCT PRIVILEGE#
FROM (SELECT *
FROM SYS.SYSAUTH$
WHERE PRIVILEGE#>0) CONNECT BY GRANTEE#=PRIOR PRIVILEGE# START WITH GRANTEE#=DBU.USER#))