[Home] [Help]
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
114, 'SQL TRANSLATION PROFILE',
115, 'UNIFIED AUDIT POLICY',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
o.namespace,
o.defining_edition,
decode(bitand(o.flags, 196608),
65536, 'METADATA LINK', 131072, 'OBJECT LINK', 'NONE'),
case when o.type# in (4,5,7,8,9,11,12,13,14,22,87,114) then
decode(bitand(o.flags, 1048576), 0, 'Y', 1048576, 'N', 'Y')
else null end,
decode(bitand(o.flags, 4194304), 4194304, 'Y', 'N')
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
and
(
o.owner# in (userenv('SCHEMAID'), 1 /* PUBLIC */)
or
(
/* non-procedural objects */
o.type# not in (7, 8, 9, 11, 12, 13, 14, 28, 29, 30, 56, 93)
and
o.obj# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# in (3 /* DELETE */, 6 /* INSERT */,
7 /* LOCK */, 9 /* SELECT */,
10 /* UPDATE */, 12 /* EXECUTE */,
11 /* USAGE */, 16 /* CREATE */,
17 /* READ */, 18 /* WRITE */ ))
)
or
(
o.type# in (7, 8, 9, 28, 29, 30, 56) /* prc, fcn, pkg */
and
(
exists (select null from sys.objauth$ oa
where oa.obj# = o.obj#
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# in (12 /* EXECUTE */, 26 /* DEBUG */))
or
exists (select null from v$enabledprivs
where priv_number in (
-144 /* EXECUTE ANY PROCEDURE */,
-141 /* CREATE ANY PROCEDURE */,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# in (19) /* partitioned table objects */
and
(
exists (select bo# from tabpart$ where obj# = o.obj# and
bo# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# in (9 /* SELECT */, 17 /* READ */))
)
or
exists (select bo# from tabcompart$ where obj# = o.obj# and
bo# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# in (9 /* SELECT */, 17 /* READ */))
)
)
)
or
(
o.type# in (34) /* sub-partitioned table objects */
and
exists (select cp.bo# from tabsubpart$ sp, tabcompart$ cp
where sp.obj# = o.obj# and sp.pobj# = cp.obj# and
cp.bo# in (select obj# from sys.objauth$
where grantee# in (select kzsrorol from x$kzsro)
and privilege# in (9 /* SELECT */, 17 /* READ */))
)
)
or
(
o.type# in (12) /* trigger */
and
(
exists (select null from sys.trigger$ t, sys.objauth$ oa
where bitand(t.property, 24) = 0
and t.obj# = o.obj#
and oa.obj# = t.baseobject
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
-152 /* CREATE ANY TRIGGER */,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# = 11 /* pkg body */
and
(
exists (select null
from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep,
sys.objauth$ oa
where specobj.owner# = o.owner#
and specobj.name = o.name
and specobj.type# = 9 /* pkg */
and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
and oa.obj# = specobj.obj#
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
-141 /* CREATE ANY PROCEDURE */,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# in (22) /* library */
and
exists (select null from v$enabledprivs
where priv_number in (
-189 /* CREATE ANY LIBRARY */,
-190 /* ALTER ANY LIBRARY */,
-191 /* DROP ANY LIBRARY */,
-192 /* EXECUTE ANY LIBRARY */
)
)
)
or
(
/* index, table, view, synonym, table partn, indx partn, */
/* table subpartn, index subpartn, cluster */
o.type# in (1, 2, 3, 4, 5, 19, 20, 34, 35)
and
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 */)
)
)
or
(
o.type# in (1) /* index */
and
exists (select i.obj# from ind$ i
where i.obj# = o.obj#
and exists (select null from sys.objauth$ oa
where oa.obj# = i.bo#
and oa.grantee# in (select kzsrorol
from x$kzsro)
)
)
)
or
( o.type# = 6 /* sequence */
and
exists (select null from v$enabledprivs
where priv_number = -109 /* SELECT ANY SEQUENCE */)
)
or
( o.type# = 13 /* type */
and
(
exists (select null from sys.objauth$ oa
where oa.obj# = o.obj#
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# in (12 /* EXECUTE */, 26 /* DEBUG */))
or
exists (select null from v$enabledprivs
where priv_number in (-184 /* EXECUTE ANY TYPE */,
-181 /* CREATE ANY TYPE */,
-241 /* DEBUG ANY PROCEDURE */))
)
)
or
(
o.type# = 14 /* type body */
and
(
exists (select null
from sys."_ACTUAL_EDITION_OBJ" specobj, sys.dependency$ dep,
sys.objauth$ oa
where specobj.owner# = o.owner#
and specobj.name = o.name
and specobj.type# = 13 /* type */
and dep.d_obj# = o.obj# and dep.p_obj# = specobj.obj#
and oa.obj# = specobj.obj#
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# = 26 /* DEBUG */)
or
exists (select null from v$enabledprivs
where priv_number in (
-181 /* CREATE ANY TYPE */,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
or
(
o.type# = 23 /* directory */
and
exists (select null from v$enabledprivs
where priv_number in (
-177 /* CREATE ANY DIRECTORY */,
-178 /* DROP ANY DIRECTORY */
)
)
)
or
(
o.type# = 42 /* summary jjf table privs have to change to summary */
and
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 */)
)
)
or
(
o.type# = 32 /* indextype */
and
exists (select null from v$enabledprivs
where priv_number in (
-205 /* CREATE INDEXTYPE */ ,
-206 /* CREATE ANY INDEXTYPE */ ,
-207 /* ALTER ANY INDEXTYPE */ ,
-208 /* DROP ANY INDEXTYPE */
)
)
)
or
(
o.type# = 33 /* operator */
and
exists (select null from v$enabledprivs
where priv_number in (
-200 /* CREATE OPERATOR */ ,
-201 /* CREATE ANY OPERATOR */ ,
-202 /* ALTER ANY OPERATOR */ ,
-203 /* DROP ANY OPERATOR */ ,
-204 /* EXECUTE OPERATOR */
)
)
)
or
(
o.type# = 44 /* context */
and
exists (select null from v$enabledprivs
where priv_number in (
-222 /* CREATE ANY CONTEXT */,
-223 /* DROP ANY CONTEXT */
)
)
)
or
(
o.type# = 48 /* resource consumer group */
and
exists (select null from v$enabledprivs
where priv_number in (12) /* switch consumer group privilege */
)
)
or
(
o.type# = 46 /* rule set */
and
exists (select null from v$enabledprivs
where priv_number in (
-251, /* create any rule set */
-252, /* alter any rule set */
-253, /* drop any rule set */
-254 /* execute any rule set */
)
)
)
or
(
o.type# = 55 /* XML schema */
and
1 = (select /*+ NO_MERGE */ xml_schema_name_present.is_schema_present(o.name, u2.id2) id1 from (select /*+ NO_MERGE */ userenv('SCHEMAID') id2 from dual) u2)
/* we need a sub-query instead of the directy invoking
* xml_schema_name_present, because inside a view even the function
* arguments are evaluated as definers rights.
*/
)
or
(
o.type# = 59 /* rule */
and
exists (select null from v$enabledprivs
where priv_number in (
-258, /* create any rule */
-259, /* alter any rule */
-260, /* drop any rule */
-261 /* execute any rule */
)
)
)
or
(
o.type# = 62 /* evaluation context */
and
exists (select null from v$enabledprivs
where priv_number in (
-246, /* create any evaluation context */
-247, /* alter any evaluation context */
-248, /* drop any evaluation context */
-249 /* execute any evaluation context */
)
)
)
or
(
o.type# IN (66, 100) /* scheduler job or file watcher */
and
exists (select null from v$enabledprivs
where priv_number = -265 /* create any job */
)
)
or
(
o.type# IN (67, 79) /* scheduler program or chain */
and
exists (select null from v$enabledprivs
where priv_number in (
-265, /* create any job */
-266 /* execute any program */
)
)
)
or
(
o.type# = 68 /* scheduler job class */
and
exists (select null from v$enabledprivs
where priv_number in (
-268, /* manage scheduler */
-267 /* execute any class */
)
)
)
or (o.type# in (69, 72, 74, 101))
/* scheduler windows, scheduler groups, schedules and destinations */
/* no privileges are needed to view these objects */
or
(
o.type# = 81 /* file group */
and
exists (select null from v$enabledprivs
where priv_number in (
-277, /* manage any file group */
-278 /* read any file group */
)
)
)
or
(
o.type# = 57 /* edition */
)
or
(
o.type# = 82 /* mining model */
and
exists (select null from v$enabledprivs
where priv_number in (
-292, /* drop any mining model */
-293, /* select any mining model */
-294 /* alter any mining model */
)
)
)
or
(
o.type# in (87) /* assembly */
and
exists (select null from v$enabledprivs
where priv_number in (
-282 /* CREATE ANY ASSEMBLY */,
-283 /* ALTER ANY ASSEMBLY */,
-284 /* DROP ANY ASSEMBLY */,
-285 /* EXECUTE ANY ASSEMBLY */
)
)
)
or
(
o.type# = 92 /* cube dimension */
and
exists (select null from v$enabledprivs
where priv_number in (
-302, /* ALTER ANY PRIMARY DIMENSION */
-303, /* CREATE ANY PRIMARY DIMENSION */
-304, /* DELETE ANY PRIMARY DIMENSION */
-305, /* DROP ANY PRIMARY DIMENSION */
-306, /* INSERT ANY PRIMARY DIMENSION */
-307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
or
(
o.type# = 93 /* cube */
and
(o.obj# in
( select obj# /* directly granted privileges */
from sys.objauth$
where grantee# in ( select kzsrorol from x$kzsro )
)
or
(
exists (select null from v$enabledprivs
where priv_number in (
-309, /* ALTER ANY CUBE */
-310, /* CREATE ANY CUBE */
-311, /* DROP ANY CUBE */
-312, /* SELECT ANY CUBE */
-313 /* UPDATE ANY CUBE */
)
)
)
)
and /* require access to all Dimensions of the Cube */
( 1 = ( SELECT decode(have_all_dim_access, null, 1, have_all_dim_access)
FROM
( SELECT
obj#,
MIN(have_dim_access) have_all_dim_access
FROM
( SELECT
c.obj# obj#,
( CASE
WHEN
( do.owner# in ( userenv('SCHEMAID'), 1 ) /* public objects */
or do.obj# in
( select obj# /* directly granted privileges */
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 (
-302, /* ALTER ANY PRIMARY DIMENSION */
-303, /* CREATE ANY PRIMARY DIMENSION */
-304, /* DELETE ANY PRIMARY DIMENSION */
-305, /* DROP ANY PRIMARY DIMENSION */
-306, /* INSERT ANY PRIMARY DIMENSION */
-307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
)
THEN 1
ELSE 0
END ) have_dim_access
FROM
olap_cubes$ c,
dependency$ d,
obj$ do
WHERE
do.obj# = d.p_obj#
AND do.type# = 92 /* CUBE DIMENSION */
AND c.obj# = d.d_obj#
)
GROUP BY obj# ) da
WHERE
o.obj#=da.obj#(+)
)
)
)
or
(
o.type# = 94 /* measure folder */
and
exists (select null from v$enabledprivs
where priv_number in (
-315, /* CREATE ANY MEASURE FOLDER */
-316, /* DELETE ANY MEASURE FOLDER */
-317, /* DROP ANY MEASURE FOLDER */
-318, /* INSERT ANY MEASURE FOLDER */
-393, /* SELECT ANY MEASURE FOLDER */
-394 /* ALTER ANY MEASURE FOLDER */
)
)
)
or
(
o.type# = 95 /* cube build process */
and
exists (select null from v$enabledprivs
where priv_number in (
-320, /* CREATE ANY BUILD PROCESS */
-321, /* DROP ANY BUILD PROCESS */
-322, /* UPDATE ANY BUILD PROCESS */
-395, /* SELECT ANY BUILD PROCESS */
-396 /* ALTER ANY BUILD PROCESS */
)
)
)
or
(
o.type# = 114 /* sql translation profile */
and
(
exists (select null from sys.objauth$ oa
where oa.obj# = o.obj#
and oa.grantee# in (select kzsrorol from x$kzsro)
and oa.privilege# in (0 /* ALTER */, 29 /* USE */))
or
exists (select null from v$enabledprivs
where priv_number in (
-335 /* CREATE ANY SQL TRANSLATION PROFILE */,
-336 /* ALTER ANY SQL TRANSLATION PROFILE */,
-337 /* USE ANY SQL TRANSLATION PROFILE */,
-338 /* DROP ANY SQL TRANSLATION PROFILE */
)
)
)
)
)
SELECT U.NAME
, O.NAME
, O.SUBNAME
, O.OBJ#
, O.DATAOBJ#
,
DECODE(O.TYPE#
, 0
, 'NEXT OBJECT'
, 1
, 'INDEX'
, 2
, 'TABLE'
, 3
, 'CLUSTER'
,
4
, 'VIEW'
, 5
, 'SYNONYM'
, 6
, 'SEQUENCE'
,
7
, 'PROCEDURE'
, 8
, 'FUNCTION'
, 9
, 'PACKAGE'
,
11
, 'PACKAGE BODY'
, 12
, 'TRIGGER'
,
13
, 'TYPE'
, 14
, 'TYPE BODY'
,
19
, 'TABLE PARTITION'
, 20
, 'INDEX PARTITION'
, 21
, 'LOB'
,
22
, 'LIBRARY'
, 23
, 'DIRECTORY'
, 24
, 'QUEUE'
,
28
, 'JAVA SOURCE'
, 29
, 'JAVA CLASS'
, 30
, 'JAVA RESOURCE'
,
32
, 'INDEXTYPE'
, 33
, 'OPERATOR'
,
34
, 'TABLE SUBPARTITION'
, 35
, 'INDEX SUBPARTITION'
,
40
, 'LOB PARTITION'
, 41
, 'LOB SUBPARTITION'
,
42
, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM SUM$ S
WHERE S.OBJ#=O.OBJ#
AND BITAND(S.XPFLAGS
, 8388608) = 8388608)
,
'MATERIALIZED VIEW')
,
43
, 'DIMENSION'
,
44
, 'CONTEXT'
, 46
, 'RULE SET'
, 47
, 'RESOURCE PLAN'
,
48
, 'CONSUMER GROUP'
,
55
, 'XML SCHEMA'
, 56
, 'JAVA DATA'
,
57
, 'EDITION'
, 59
, 'RULE'
,
60
, 'CAPTURE'
, 61
, 'APPLY'
,
62
, 'EVALUATION CONTEXT'
,
66
, 'JOB'
, 67
, 'PROGRAM'
, 68
, 'JOB CLASS'
, 69
, 'WINDOW'
,
72
, 'SCHEDULER GROUP'
, 74
, 'SCHEDULE'
, 79
, 'CHAIN'
,
81
, 'FILE GROUP'
, 82
, 'MINING MODEL'
, 87
, 'ASSEMBLY'
,
90
, 'CREDENTIAL'
, 92
, 'CUBE DIMENSION'
, 93
, 'CUBE'
,
94
, 'MEASURE FOLDER'
, 95
, 'CUBE BUILD PROCESS'
,
100
, 'FILE WATCHER'
, 101
, 'DESTINATION'
,
114
, 'SQL TRANSLATION PROFILE'
,
115
, 'UNIFIED AUDIT POLICY'
,
'UNDEFINED')
,
O.CTIME
, O.MTIME
,
TO_CHAR(O.STIME
, 'YYYY-MM-DD:HH24:MI:SS')
,
DECODE(O.STATUS
, 0
, 'N/A'
, 1
, 'VALID'
, 'INVALID')
,
DECODE(BITAND(O.FLAGS
, 2)
, 0
, 'N'
, 2
, 'Y'
, 'N')
,
DECODE(BITAND(O.FLAGS
, 4)
, 0
, 'N'
, 4
, 'Y'
, 'N')
,
DECODE(BITAND(O.FLAGS
, 16)
, 0
, 'N'
, 16
, 'Y'
, 'N')
,
O.NAMESPACE
,
O.DEFINING_EDITION
,
DECODE(BITAND(O.FLAGS
, 196608)
,
65536
, 'METADATA LINK'
, 131072
, 'OBJECT LINK'
, 'NONE')
,
CASE WHEN O.TYPE# IN (4
, 5
, 7
, 8
, 9
, 11
, 12
, 13
, 14
, 22
, 87
, 114) THEN
DECODE(BITAND(O.FLAGS
, 1048576)
, 0
, 'Y'
, 1048576
, 'N'
, 'Y')
ELSE NULL END
,
DECODE(BITAND(O.FLAGS
, 4194304)
, 4194304
, 'Y'
, 'N')
FROM SYS."_CURRENT_EDITION_OBJ" O
, SYS.USER$ U
WHERE O.OWNER# = U.USER#
AND O.LINKNAME IS NULL
AND (O.TYPE# NOT IN (1 /* INDEX - HANDLED BELOW */
,
10 /* NON-EXISTENT */)
OR
(O.TYPE# = 1
AND 1 = (SELECT 1
FROM SYS.IND$ I
WHERE I.OBJ# = O.OBJ#
AND I.TYPE# IN (1
, 2
, 3
, 4
, 6
, 7
, 9))))
AND O.NAME != '_NEXT_OBJECT'
AND O.NAME != '_DEFAULT_AUDITING_OPTIONS_'
AND BITAND(O.FLAGS
, 128) = 0
AND
(
O.OWNER# IN (USERENV('SCHEMAID')
, 1 /* PUBLIC */)
OR
(
/* NON-PROCEDURAL OBJECTS */
O.TYPE# NOT IN (7
, 8
, 9
, 11
, 12
, 13
, 14
, 28
, 29
, 30
, 56
, 93)
AND
O.OBJ# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (3 /* DELETE */
, 6 /* INSERT */
,
7 /* LOCK */
, 9 /* SELECT */
,
10 /* UPDATE */
, 12 /* EXECUTE */
,
11 /* USAGE */
, 16 /* CREATE */
,
17 /* READ */
, 18 /* WRITE */ ))
)
OR
(
O.TYPE# IN (7
, 8
, 9
, 28
, 29
, 30
, 56) /* PRC
, FCN
, PKG */
AND
(
EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ OA
WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# IN (12 /* EXECUTE */
, 26 /* DEBUG */))
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-144 /* EXECUTE ANY PROCEDURE */
,
-141 /* CREATE ANY PROCEDURE */
,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
OR
(
O.TYPE# IN (19) /* PARTITIONED TABLE OBJECTS */
AND
(
EXISTS (SELECT BO#
FROM TABPART$
WHERE OBJ# = O.OBJ# AND
BO# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (9 /* SELECT */
, 17 /* READ */))
)
OR
EXISTS (SELECT BO#
FROM TABCOMPART$
WHERE OBJ# = O.OBJ# AND
BO# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (9 /* SELECT */
, 17 /* READ */))
)
)
)
OR
(
O.TYPE# IN (34) /* SUB-PARTITIONED TABLE OBJECTS */
AND
EXISTS (SELECT CP.BO#
FROM TABSUBPART$ SP
, TABCOMPART$ CP
WHERE SP.OBJ# = O.OBJ#
AND SP.POBJ# = CP.OBJ# AND
CP.BO# IN (SELECT OBJ#
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND PRIVILEGE# IN (9 /* SELECT */
, 17 /* READ */))
)
)
OR
(
O.TYPE# IN (12) /* TRIGGER */
AND
(
EXISTS (SELECT NULL
FROM SYS.TRIGGER$ T
, SYS.OBJAUTH$ OA
WHERE BITAND(T.PROPERTY
, 24) = 0
AND T.OBJ# = O.OBJ#
AND OA.OBJ# = T.BASEOBJECT
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# = 26 /* DEBUG */)
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-152 /* CREATE ANY TRIGGER */
,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
OR
(
O.TYPE# = 11 /* PKG BODY */
AND
(
EXISTS (SELECT NULL
FROM SYS."_ACTUAL_EDITION_OBJ" SPECOBJ
, SYS.DEPENDENCY$ DEP
,
SYS.OBJAUTH$ OA
WHERE SPECOBJ.OWNER# = O.OWNER#
AND SPECOBJ.NAME = O.NAME
AND SPECOBJ.TYPE# = 9 /* PKG */
AND DEP.D_OBJ# = O.OBJ#
AND DEP.P_OBJ# = SPECOBJ.OBJ#
AND OA.OBJ# = SPECOBJ.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# = 26 /* DEBUG */)
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-141 /* CREATE ANY PROCEDURE */
,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
OR
(
O.TYPE# IN (22) /* LIBRARY */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-189 /* CREATE ANY LIBRARY */
,
-190 /* ALTER ANY LIBRARY */
,
-191 /* DROP ANY LIBRARY */
,
-192 /* EXECUTE ANY LIBRARY */
)
)
)
OR
(
/* INDEX
, TABLE
, VIEW
, SYNONYM
, TABLE PARTN
, INDX PARTN
, */
/* TABLE SUBPARTN
, INDEX SUBPARTN
, CLUSTER */
O.TYPE# IN (1
, 2
, 3
, 4
, 5
, 19
, 20
, 34
, 35)
AND
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 */)
)
)
OR
(
O.TYPE# IN (1) /* INDEX */
AND
EXISTS (SELECT I.OBJ#
FROM IND$ I
WHERE I.OBJ# = O.OBJ#
AND EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ OA
WHERE OA.OBJ# = I.BO#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
)
)
)
OR
( O.TYPE# = 6 /* SEQUENCE */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -109 /* SELECT ANY SEQUENCE */)
)
OR
( O.TYPE# = 13 /* TYPE */
AND
(
EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ OA
WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# IN (12 /* EXECUTE */
, 26 /* DEBUG */))
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-184 /* EXECUTE ANY TYPE */
,
-181 /* CREATE ANY TYPE */
,
-241 /* DEBUG ANY PROCEDURE */))
)
)
OR
(
O.TYPE# = 14 /* TYPE BODY */
AND
(
EXISTS (SELECT NULL
FROM SYS."_ACTUAL_EDITION_OBJ" SPECOBJ
, SYS.DEPENDENCY$ DEP
,
SYS.OBJAUTH$ OA
WHERE SPECOBJ.OWNER# = O.OWNER#
AND SPECOBJ.NAME = O.NAME
AND SPECOBJ.TYPE# = 13 /* TYPE */
AND DEP.D_OBJ# = O.OBJ#
AND DEP.P_OBJ# = SPECOBJ.OBJ#
AND OA.OBJ# = SPECOBJ.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# = 26 /* DEBUG */)
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-181 /* CREATE ANY TYPE */
,
-241 /* DEBUG ANY PROCEDURE */
)
)
)
)
OR
(
O.TYPE# = 23 /* DIRECTORY */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-177 /* CREATE ANY DIRECTORY */
,
-178 /* DROP ANY DIRECTORY */
)
)
)
OR
(
O.TYPE# = 42 /* SUMMARY JJF TABLE PRIVS HAVE TO CHANGE TO SUMMARY */
AND
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 */)
)
)
OR
(
O.TYPE# = 32 /* INDEXTYPE */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-205 /* CREATE INDEXTYPE */
,
-206 /* CREATE ANY INDEXTYPE */
,
-207 /* ALTER ANY INDEXTYPE */
,
-208 /* DROP ANY INDEXTYPE */
)
)
)
OR
(
O.TYPE# = 33 /* OPERATOR */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-200 /* CREATE OPERATOR */
,
-201 /* CREATE ANY OPERATOR */
,
-202 /* ALTER ANY OPERATOR */
,
-203 /* DROP ANY OPERATOR */
,
-204 /* EXECUTE OPERATOR */
)
)
)
OR
(
O.TYPE# = 44 /* CONTEXT */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-222 /* CREATE ANY CONTEXT */
,
-223 /* DROP ANY CONTEXT */
)
)
)
OR
(
O.TYPE# = 48 /* RESOURCE CONSUMER GROUP */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (12) /* SWITCH CONSUMER GROUP PRIVILEGE */
)
)
OR
(
O.TYPE# = 46 /* RULE SET */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-251
, /* CREATE ANY RULE SET */
-252
, /* ALTER ANY RULE SET */
-253
, /* DROP ANY RULE SET */
-254 /* EXECUTE ANY RULE SET */
)
)
)
OR
(
O.TYPE# = 55 /* XML SCHEMA */
AND
1 = (SELECT /*+ NO_MERGE */ XML_SCHEMA_NAME_PRESENT.IS_SCHEMA_PRESENT(O.NAME
, U2.ID2) ID1
FROM (SELECT /*+ NO_MERGE */ USERENV('SCHEMAID') ID2
FROM DUAL) U2)
/* WE NEED A SUB-QUERY INSTEAD OF THE DIRECTY INVOKING
* XML_SCHEMA_NAME_PRESENT
, BECAUSE INSIDE A VIEW EVEN THE FUNCTION
* ARGUMENTS ARE EVALUATED AS DEFINERS RIGHTS.
*/
)
OR
(
O.TYPE# = 59 /* RULE */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-258
, /* CREATE ANY RULE */
-259
, /* ALTER ANY RULE */
-260
, /* DROP ANY RULE */
-261 /* EXECUTE ANY RULE */
)
)
)
OR
(
O.TYPE# = 62 /* EVALUATION CONTEXT */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-246
, /* CREATE ANY EVALUATION CONTEXT */
-247
, /* ALTER ANY EVALUATION CONTEXT */
-248
, /* DROP ANY EVALUATION CONTEXT */
-249 /* EXECUTE ANY EVALUATION CONTEXT */
)
)
)
OR
(
O.TYPE# IN (66
, 100) /* SCHEDULER JOB OR FILE WATCHER */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -265 /* CREATE ANY JOB */
)
)
OR
(
O.TYPE# IN (67
, 79) /* SCHEDULER PROGRAM OR CHAIN */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-265
, /* CREATE ANY JOB */
-266 /* EXECUTE ANY PROGRAM */
)
)
)
OR
(
O.TYPE# = 68 /* SCHEDULER JOB CLASS */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-268
, /* MANAGE SCHEDULER */
-267 /* EXECUTE ANY CLASS */
)
)
)
OR (O.TYPE# IN (69
, 72
, 74
, 101))
/* SCHEDULER WINDOWS
, SCHEDULER GROUPS
, SCHEDULES
AND DESTINATIONS */
/* NO PRIVILEGES ARE NEEDED TO VIEW THESE OBJECTS */
OR
(
O.TYPE# = 81 /* FILE GROUP */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-277
, /* MANAGE ANY FILE GROUP */
-278 /* READ ANY FILE GROUP */
)
)
)
OR
(
O.TYPE# = 57 /* EDITION */
)
OR
(
O.TYPE# = 82 /* MINING MODEL */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-292
, /* DROP ANY MINING MODEL */
-293
, /* SELECT ANY MINING MODEL */
-294 /* ALTER ANY MINING MODEL */
)
)
)
OR
(
O.TYPE# IN (87) /* ASSEMBLY */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-282 /* CREATE ANY ASSEMBLY */
,
-283 /* ALTER ANY ASSEMBLY */
,
-284 /* DROP ANY ASSEMBLY */
,
-285 /* EXECUTE ANY ASSEMBLY */
)
)
)
OR
(
O.TYPE# = 92 /* CUBE DIMENSION */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-302
, /* ALTER ANY PRIMARY DIMENSION */
-303
, /* CREATE ANY PRIMARY DIMENSION */
-304
, /* DELETE ANY PRIMARY DIMENSION */
-305
, /* DROP ANY PRIMARY DIMENSION */
-306
, /* INSERT ANY PRIMARY DIMENSION */
-307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
OR
(
O.TYPE# = 93 /* CUBE */
AND
(O.OBJ# IN
( SELECT OBJ# /* DIRECTLY GRANTED PRIVILEGES */
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO )
)
OR
(
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-309
, /* ALTER ANY CUBE */
-310
, /* CREATE ANY CUBE */
-311
, /* DROP ANY CUBE */
-312
, /* SELECT ANY CUBE */
-313 /* UPDATE ANY CUBE */
)
)
)
)
AND /* REQUIRE ACCESS TO ALL DIMENSIONS OF THE CUBE */
( 1 = ( SELECT DECODE(HAVE_ALL_DIM_ACCESS
, NULL
, 1
, HAVE_ALL_DIM_ACCESS)
FROM
( SELECT
OBJ#
,
MIN(HAVE_DIM_ACCESS) HAVE_ALL_DIM_ACCESS
FROM
( SELECT
C.OBJ# OBJ#
,
( CASE
WHEN
( DO.OWNER# IN ( USERENV('SCHEMAID')
, 1 ) /* PUBLIC OBJECTS */
OR DO.OBJ# IN
( SELECT OBJ# /* DIRECTLY GRANTED PRIVILEGES */
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 (
-302
, /* ALTER ANY PRIMARY DIMENSION */
-303
, /* CREATE ANY PRIMARY DIMENSION */
-304
, /* DELETE ANY PRIMARY DIMENSION */
-305
, /* DROP ANY PRIMARY DIMENSION */
-306
, /* INSERT ANY PRIMARY DIMENSION */
-307 /* SELECT ANY PRIMARY DIMENSION */
)
)
)
)
THEN 1
ELSE 0
END ) HAVE_DIM_ACCESS
FROM
OLAP_CUBES$ C
,
DEPENDENCY$ D
,
OBJ$ DO
WHERE
DO.OBJ# = D.P_OBJ#
AND DO.TYPE# = 92 /* CUBE DIMENSION */
AND C.OBJ# = D.D_OBJ#
)
GROUP BY OBJ# ) DA
WHERE
O.OBJ#=DA.OBJ#(+)
)
)
)
OR
(
O.TYPE# = 94 /* MEASURE FOLDER */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-315
, /* CREATE ANY MEASURE FOLDER */
-316
, /* DELETE ANY MEASURE FOLDER */
-317
, /* DROP ANY MEASURE FOLDER */
-318
, /* INSERT ANY MEASURE FOLDER */
-393
, /* SELECT ANY MEASURE FOLDER */
-394 /* ALTER ANY MEASURE FOLDER */
)
)
)
OR
(
O.TYPE# = 95 /* CUBE BUILD PROCESS */
AND
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-320
, /* CREATE ANY BUILD PROCESS */
-321
, /* DROP ANY BUILD PROCESS */
-322
, /* UPDATE ANY BUILD PROCESS */
-395
, /* SELECT ANY BUILD PROCESS */
-396 /* ALTER ANY BUILD PROCESS */
)
)
)
OR
(
O.TYPE# = 114 /* SQL TRANSLATION PROFILE */
AND
(
EXISTS (SELECT NULL
FROM SYS.OBJAUTH$ OA
WHERE OA.OBJ# = O.OBJ#
AND OA.GRANTEE# IN (SELECT KZSROROL
FROM X$KZSRO)
AND OA.PRIVILEGE# IN (0 /* ALTER */
, 29 /* USE */))
OR
EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (
-335 /* CREATE ANY SQL TRANSLATION PROFILE */
,
-336 /* ALTER ANY SQL TRANSLATION PROFILE */
,
-337 /* USE ANY SQL TRANSLATION PROFILE */
,
-338 /* DROP ANY SQL TRANSLATION PROFILE */
)
)
)
)
)
|
|
|
|