select u.name, c.name, o.name,
decode(ac.name, null, col.name, ac.name), cc.pos#
from sys.user$ u, sys.con$ c, sys.col$ col, sys.ccol$ cc, sys.cdef$ cd,
sys."_CURRENT_EDITION_OBJ" o, sys.attrcol$ ac
where c.owner# = u.user#
and c.con# = cd.con#
and (cd.type# < 14 or cd.type# > 17) /* don't include supplog cons */
and (cd.type# != 12) /* don't include log group cons */
and cd.con# = cc.con#
and cc.obj# = col.obj#
and cc.intcol# = col.intcol#
and cc.obj# = o.obj#
and (c.owner# = userenv('SCHEMAID')
or cd.obj# in (select obj#
from sys.objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or /* user has system privileges */
exists (select null from v$enabledprivs
where priv_number in (-45 /* LOCK ANY TABLE */,
-47 /* SELECT ANY TABLE */,
-397/* READ ANY TABLE */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
and col.obj# = ac.obj#(+)
and col.intcol# = ac.intcol#(+)
SELECT U.NAME
, C.NAME
, O.NAME
,
DECODE(AC.NAME
, NULL
, COL.NAME
, AC.NAME)
, CC.POS#
FROM SYS.USER$ U
, SYS.CON$ C
, SYS.COL$ COL
, SYS.CCOL$ CC
, SYS.CDEF$ CD
,
SYS."_CURRENT_EDITION_OBJ" O
, SYS.ATTRCOL$ AC
WHERE C.OWNER# = U.USER#
AND C.CON# = CD.CON#
AND (CD.TYPE# < 14 OR CD.TYPE# > 17) /* DON'T INCLUDE SUPPLOG CONS */
AND (CD.TYPE# != 12) /* DON'T INCLUDE LOG GROUP CONS */
AND CD.CON# = CC.CON#
AND CC.OBJ# = COL.OBJ#
AND CC.INTCOL# = COL.INTCOL#
AND CC.OBJ# = O.OBJ#
AND (C.OWNER# = USERENV('SCHEMAID')
OR CD.OBJ# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO
)
)
OR /* USER HAS SYSTEM PRIVILEGES */
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
,
-47 /* SELECT ANY TABLE */
,
-397/* READ ANY TABLE */
,
-48 /* INSERT ANY TABLE */
,
-49 /* UPDATE ANY TABLE */
,
-50 /* DELETE ANY TABLE */)
)
)
AND COL.OBJ# = AC.OBJ#(+)
AND COL.INTCOL# = AC.INTCOL#(+)
|
|
|