VIEW: SYS.DBA_UNUSED_PRIVS

Source

View Text - Preformatted

SELECT cap.name,
 (select u.name from dual where cap.type = 1 or cap.type = 3),
 (select u.name from dual where cap.type = 2 or cap.type = 4),
 (select m.name from sys.system_privilege_map m where priv.syspriv# = -m.privilege),
 (select m.name from sys.table_privilege_map m
  where priv.objpriv# = m.privilege and priv.syspriv# = 0),
 decode(priv.userpriv#, 0, 'INHERIT PRIVILEGES', 1, 'TRANSLATE SQL', NULL),
 decode(priv.userpriv#, 255,
        (select u.name from sys.obj$ o, sys.user$ u
        where o.obj#=priv.obj# and o.owner#=u.user#), NULL),
 decode(priv.userpriv#, 255,
        (select o.name from sys.obj$ o, sys.user$ u where o.obj#=priv.obj#
         and o.owner#=u.user#),
        (select u.name from sys.user$ u where u.user# = priv.obj#)),
 decode(priv.userpriv#, 255,
        (select d.object_type from dba_objects d where d.object_id = priv.obj#), 'USER'),
 (select c.name from sys.col$ c
  where priv.col# !=0 and obj#=priv.obj# and col#=priv.col#),
  priv.option$, pa.path
FROM sys.priv_unused$ priv, sys.priv_capture$ cap, sys.user$ u,
     sys.priv_unused_path$ pa
WHERE priv.capture = cap.id# and u.user#=priv.user# and priv.id# = pa.id#
      and priv.capture = pa.capture

View Text - HTML Formatted

SELECT CAP.NAME
, (SELECT U.NAME
FROM DUAL
WHERE CAP.TYPE = 1 OR CAP.TYPE = 3)
, (SELECT U.NAME
FROM DUAL
WHERE CAP.TYPE = 2 OR CAP.TYPE = 4)
, (SELECT M.NAME
FROM SYS.SYSTEM_PRIVILEGE_MAP M
WHERE PRIV.SYSPRIV# = -M.PRIVILEGE)
, (SELECT M.NAME
FROM SYS.TABLE_PRIVILEGE_MAP M
WHERE PRIV.OBJPRIV# = M.PRIVILEGE
AND PRIV.SYSPRIV# = 0)
, DECODE(PRIV.USERPRIV#
, 0
, 'INHERIT PRIVILEGES'
, 1
, 'TRANSLATE SQL'
, NULL)
, DECODE(PRIV.USERPRIV#
, 255
, (SELECT U.NAME
FROM SYS.OBJ$ O
, SYS.USER$ U
WHERE O.OBJ#=PRIV.OBJ#
AND O.OWNER#=U.USER#)
, NULL)
, DECODE(PRIV.USERPRIV#
, 255
, (SELECT O.NAME
FROM SYS.OBJ$ O
, SYS.USER$ U
WHERE O.OBJ#=PRIV.OBJ#
AND O.OWNER#=U.USER#)
, (SELECT U.NAME
FROM SYS.USER$ U
WHERE U.USER# = PRIV.OBJ#))
, DECODE(PRIV.USERPRIV#
, 255
, (SELECT D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = PRIV.OBJ#)
, 'USER')
, (SELECT C.NAME
FROM SYS.COL$ C
WHERE PRIV.COL# !=0
AND OBJ#=PRIV.OBJ#
AND COL#=PRIV.COL#)
, PRIV.OPTION$
, PA.PATH FROM SYS.PRIV_UNUSED$ PRIV
, SYS.PRIV_CAPTURE$ CAP
, SYS.USER$ U
, SYS.PRIV_UNUSED_PATH$ PA WHERE PRIV.CAPTURE = CAP.ID#
AND U.USER#=PRIV.USER#
AND PRIV.ID# = PA.ID#
AND PRIV.CAPTURE = PA.CAPTURE