VIEW: SYS.ALL_IDENTIFIERS

Source

View Text - Preformatted

select OWNER, NAME, SIGNATURE, TYPE, OBJECT_NAME, OBJECT_TYPE,
       USAGE, USAGE_ID, LINE, COL, USAGE_CONTEXT_ID,
       ORIGIN_CON_ID
from INT$DBA_IDENTIFIERS
where
  (
    OWNER = SYS_CONTEXT('USERENV', 'CURRENT_USER')
    or OWNER = 'PUBLIC'
    or
    (
      (
         (
          (OBJECT_TYPE#
           in (7 /* proc */, 8 /* func */, 9 /* pkg */, 13 /* type */,
               22 /* library */))
          and
          OBJ_ID(OWNER, OBJECT_NAME, OBJECT_TYPE#, OBJECT_ID) in
          (select obj# from sys.objauth$
                     where grantee# in (select kzsrorol from x$kzsro)
                       and privilege# in (12 /* EXECUTE */, 26 /* DEBUG */))
        )
        or
        (
          (OBJECT_TYPE# in (11 /* package body */, 14 /* type body */))
          and
          exists
          (
            select null from sys.obj$ specobj, sys.objauth$ oa, sys.user$ u
            where specobj.owner# = u.user#
              and u.name = OWNER
              and specobj.name = OBJECT_NAME
              and specobj.type# = decode(OBJECT_TYPE#,
                                         11 /* pkg body */, 9 /* pkg */,
                                         14 /* type body */, 13 /* type */,
                                         null)
              and oa.obj# = specobj.obj#
              and oa.grantee# in (select kzsrorol from x$kzsro)
              and oa.privilege# = 26 /* DEBUG */)
        )
        or
        (
          (OBJECT_TYPE# = 12 /* trigger */)
          and
          exists
          (
            select null
            from sys.trigger$ t, sys.obj$ tabobj, sys.objauth$ oa, sys.user$ u
            where t.obj# = OBJ_ID(OWNER, OBJECT_NAME, 12, OBJECT_ID)
              and tabobj.obj# = t.baseobject
              and tabobj.owner# = u.user#
              and u.name = OWNER
              and oa.obj# = tabobj.obj#
              and oa.grantee# in (select kzsrorol from x$kzsro)
              and oa.privilege# = 26 /* DEBUG */)
        )
        or
        exists
        (
          select null from sys.sysauth$
          where grantee# in (select kzsrorol from x$kzsro)
          and
          (
            (
              /* procedure */
              (OBJECT_TYPE# = 7 or OBJECT_TYPE# = 8 or OBJECT_TYPE# = 9)
              and
              (
                privilege# = -144 /* EXECUTE ANY PROCEDURE */
                or
                privilege# = -141 /* CREATE ANY PROCEDURE */
                or
                privilege# = -241 /* DEBUG ANY PROCEDURE */
              )
            )
            or
            (
              /* package body */
              OBJECT_TYPE# = 11 and
              (
                privilege# = -141 /* CREATE ANY PROCEDURE */
                or
                privilege# = -241 /* DEBUG ANY PROCEDURE */
              )
            )
            or
            (
              /* type */
              OBJECT_TYPE# = 13
              and
              (
                privilege# = -184 /* EXECUTE ANY TYPE */
                or
                privilege# = -181 /* CREATE ANY TYPE */
                or
                privilege# = -241 /* DEBUG ANY PROCEDURE */
              )
            )
            or
            (
              /* type body */
              OBJECT_TYPE# = 14 and
              (
                privilege# = -181 /* CREATE ANY TYPE */
                or
                privilege# = -241 /* DEBUG ANY PROCEDURE */
              )
            )
            or
            (
              /* triggers */
              OBJECT_TYPE# = 12 and
              (
                privilege# = -152 /* CREATE ANY TRIGGER */
                or
                privilege# = -241 /* DEBUG ANY PROCEDURE */
              )
            )
            or
            (
              /* library */
              OBJECT_TYPE# = 22 and
              (
                privilege# = -189 /* CREATE ANY LIBRARY */
                or
                privilege# = -192 /* EXECUTE ANY LIBRARY */
              )
            )
          )
        )
      )
    )
  )

View Text - HTML Formatted

SELECT OWNER
, NAME
, SIGNATURE
, TYPE
, OBJECT_NAME
, OBJECT_TYPE
, USAGE
, USAGE_ID
, LINE
, COL
, USAGE_CONTEXT_ID
, ORIGIN_CON_ID FROM INT$DBA_IDENTIFIERS WHERE ( OWNER = SYS_CONTEXT('USERENV'
, 'CURRENT_USER') OR OWNER = 'PUBLIC' OR ( ( ( (OBJECT_TYPE# IN (7 /* PROC */
, 8 /* FUNC */
, 9 /* PKG */
, 13 /* TYPE */
, 22 /* LIBRARY */)) AND OBJ_ID(OWNER
, OBJECT_NAME
, OBJECT_TYPE#
, OBJECT_ID) IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (12 /* EXECUTE */
, 26 /* DEBUG */)) ) OR ( (OBJECT_TYPE# IN (11 /* PACKAGE BODY */
, 14 /* TYPE BODY */)) AND EXISTS ( SELECT NULL
FROM SYS.OBJ$ SPECOBJ
, SYS.OBJAUTH$ OA
, SYS.USER$ U
WHERE SPECOBJ.OWNER# = U.USER#
AND U.NAME = OWNER
AND SPECOBJ.NAME = OBJECT_NAME
AND SPECOBJ.TYPE# = DECODE(OBJECT_TYPE#
, 11 /* PKG BODY */
, 9 /* PKG */
, 14 /* TYPE BODY */
, 13 /* TYPE */
, NULL)
AND OA.OBJ# = SPECOBJ.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# = 26 /* DEBUG */) ) OR ( (OBJECT_TYPE# = 12 /* TRIGGER */) AND EXISTS ( SELECT NULL
FROM SYS.TRIGGER$ T
, SYS.OBJ$ TABOBJ
, SYS.OBJAUTH$ OA
, SYS.USER$ U
WHERE T.OBJ# = OBJ_ID(OWNER
, OBJECT_NAME
, 12
, OBJECT_ID)
AND TABOBJ.OBJ# = T.BASEOBJECT
AND TABOBJ.OWNER# = U.USER#
AND U.NAME = OWNER
AND OA.OBJ# = TABOBJ.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# = 26 /* DEBUG */) ) OR EXISTS ( SELECT NULL
FROM SYS.SYSAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO) AND ( ( /* PROCEDURE */ (OBJECT_TYPE# = 7 OR OBJECT_TYPE# = 8 OR OBJECT_TYPE# = 9) AND ( PRIVILEGE# = -144 /* EXECUTE ANY PROCEDURE */ OR PRIVILEGE# = -141 /* CREATE ANY PROCEDURE */ OR PRIVILEGE# = -241 /* DEBUG ANY PROCEDURE */ ) ) OR ( /* PACKAGE BODY */ OBJECT_TYPE# = 11 AND ( PRIVILEGE# = -141 /* CREATE ANY PROCEDURE */ OR PRIVILEGE# = -241 /* DEBUG ANY PROCEDURE */ ) ) OR ( /* TYPE */ OBJECT_TYPE# = 13 AND ( PRIVILEGE# = -184 /* EXECUTE ANY TYPE */ OR PRIVILEGE# = -181 /* CREATE ANY TYPE */ OR PRIVILEGE# = -241 /* DEBUG ANY PROCEDURE */ ) ) OR ( /* TYPE BODY */ OBJECT_TYPE# = 14 AND ( PRIVILEGE# = -181 /* CREATE ANY TYPE */ OR PRIVILEGE# = -241 /* DEBUG ANY PROCEDURE */ ) ) OR ( /* TRIGGERS */ OBJECT_TYPE# = 12 AND ( PRIVILEGE# = -152 /* CREATE ANY TRIGGER */ OR PRIVILEGE# = -241 /* DEBUG ANY PROCEDURE */ ) ) OR ( /* LIBRARY */ OBJECT_TYPE# = 22 AND ( PRIVILEGE# = -189 /* CREATE ANY LIBRARY */ OR PRIVILEGE# = -192 /* EXECUTE ANY LIBRARY */ ) ) ) ) ) ) )