VIEW: SYS.ALL_COL_PRIVS_RECD

Source

View Text - Preformatted

select ue.name, u.name, o.name, c.name, ur.name, tpm.name,
       decode(mod(oa.option$,2), 1, 'YES', 'NO'), 'NO'
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     sys.user$ ue, sys.col$ c, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and oa.obj# = c.obj#
  and oa.col# = c.col#
  and bitand(c.property, 32) = 0 /* not hidden column */
  and oa.col# is not null
  and oa.privilege# = tpm.privilege
  and oa.grantee# in (select kzsrorol from x$kzsro)
  and bitand(nvl(oa.option$, 0), 4) = 0 /* Local, and maybe Common, Privs */
union all
/* Common Privileges */
select ue.name, u.name, o.name, c.name, ur.name, tpm.name,
       decode(bitand(oa.option$,16), 16, 'YES', 'NO'), 'YES'
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     sys.user$ ue, sys.col$ c, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and oa.obj# = c.obj#
  and oa.col# = c.col#
  and bitand(c.property, 32) = 0 /* not hidden column */
  and oa.col# is not null
  and oa.privilege# = tpm.privilege
  and oa.grantee# in (select kzsrorol from x$kzsro)
  and bitand(oa.option$,8) = 8 /* Common, and maybe Local, priv */

View Text - HTML Formatted

SELECT UE.NAME
, U.NAME
, O.NAME
, C.NAME
, UR.NAME
, TPM.NAME
, DECODE(MOD(OA.OPTION$
, 2)
, 1
, 'YES'
, 'NO')
, 'NO' FROM SYS.OBJAUTH$ OA
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ UR
, SYS.USER$ UE
, SYS.COL$ C
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND OA.GRANTEE# = UE.USER#
AND U.USER# = O.OWNER#
AND OA.OBJ# = C.OBJ#
AND OA.COL# = C.COL#
AND BITAND(C.PROPERTY
, 32) = 0 /* NOT HIDDEN COLUMN */
AND OA.COL# IS NOT NULL
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND BITAND(NVL(OA.OPTION$
, 0)
, 4) = 0 /* LOCAL
,
AND MAYBE COMMON
, PRIVS */ UNION ALL /* COMMON PRIVILEGES */ SELECT UE.NAME
, U.NAME
, O.NAME
, C.NAME
, UR.NAME
, TPM.NAME
, DECODE(BITAND(OA.OPTION$
, 16)
, 16
, 'YES'
, 'NO')
, 'YES' FROM SYS.OBJAUTH$ OA
, SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
, SYS.USER$ UR
, SYS.USER$ UE
, SYS.COL$ C
, TABLE_PRIVILEGE_MAP TPM WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTOR# = UR.USER#
AND OA.GRANTEE# = UE.USER#
AND U.USER# = O.OWNER#
AND OA.OBJ# = C.OBJ#
AND OA.COL# = C.COL#
AND BITAND(C.PROPERTY
, 32) = 0 /* NOT HIDDEN COLUMN */
AND OA.COL# IS NOT NULL
AND OA.PRIVILEGE# = TPM.PRIVILEGE
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND BITAND(OA.OPTION$
, 8) = 8 /* COMMON
,
AND MAYBE LOCAL
, PRIV */