SELECT
u.name OWNER,
o.name CUBE_NAME,
m.map_name MAP_NAME,
m.map_id MAP_ID,
s1.syntax_clob QUERY,
s2.syntax_clob WHERE_CLAUSE,
s3.syntax_clob FROM_CLAUSE,
decode(i1.option_num_value, '1', 'Y', 'N') IS_SOLVED,
i2.option_value AGGREGATION_METHOD
FROM
olap_mappings$ m,
user$ u,
obj$ o,
olap_syntax$ s1,
olap_syntax$ s2,
olap_syntax$ s3,
olap_impl_options$ i1,
olap_impl_options$ i2,
(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
-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
m.map_type = 22
AND m.mapping_owner_id = o.obj#
AND o.obj# = da.obj#(+)
AND o.owner# = u.user#
AND m.map_id = s1.owner_id(+)
AND m.map_type = s1.owner_type(+)
AND s1.ref_role(+) = 3
AND m.map_id = s2.owner_id(+)
AND m.map_type = s2.owner_type(+)
AND s2.ref_role(+) = 21
AND m.map_id = s3.owner_id(+)
AND m.map_type = s3.owner_type(+)
AND s3.ref_role(+) = 22
AND m.map_id = i1.owning_objectid(+)
AND m.map_type = i1.object_type(+)
AND i1.option_type(+) = 11
AND m.map_id = i2.owning_objectid(+)
AND m.map_type = i2.object_type(+)
AND i2.option_type(+) = 21
AND (o.owner# in (userenv('SCHEMAID'), 1) -- public objects
or o.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 (-309, -- ALTER ANY CUBE
-311, -- DROP ANY CUBE
-312, -- SELECT ANY CUBE
-313) -- UPDATE ANY CUBE
)
)
)
AND ((have_all_dim_access = 1) OR (have_all_dim_access is NULL))
SELECT
U.NAME OWNER
,
O.NAME CUBE_NAME
,
M.MAP_NAME MAP_NAME
,
M.MAP_ID MAP_ID
,
S1.SYNTAX_CLOB QUERY
,
S2.SYNTAX_CLOB WHERE_CLAUSE
,
S3.SYNTAX_CLOB FROM_CLAUSE
,
DECODE(I1.OPTION_NUM_VALUE
, '1'
, 'Y'
, 'N') IS_SOLVED
,
I2.OPTION_VALUE AGGREGATION_METHOD
FROM
OLAP_MAPPINGS$ M
,
USER$ U
,
OBJ$ O
,
OLAP_SYNTAX$ S1
,
OLAP_SYNTAX$ S2
,
OLAP_SYNTAX$ S3
,
OLAP_IMPL_OPTIONS$ I1
,
OLAP_IMPL_OPTIONS$ I2
,
(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
-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
M.MAP_TYPE = 22
AND M.MAPPING_OWNER_ID = O.OBJ#
AND O.OBJ# = DA.OBJ#(+)
AND O.OWNER# = U.USER#
AND M.MAP_ID = S1.OWNER_ID(+)
AND M.MAP_TYPE = S1.OWNER_TYPE(+)
AND S1.REF_ROLE(+) = 3
AND M.MAP_ID = S2.OWNER_ID(+)
AND M.MAP_TYPE = S2.OWNER_TYPE(+)
AND S2.REF_ROLE(+) = 21
AND M.MAP_ID = S3.OWNER_ID(+)
AND M.MAP_TYPE = S3.OWNER_TYPE(+)
AND S3.REF_ROLE(+) = 22
AND M.MAP_ID = I1.OWNING_OBJECTID(+)
AND M.MAP_TYPE = I1.OBJECT_TYPE(+)
AND I1.OPTION_TYPE(+) = 11
AND M.MAP_ID = I2.OWNING_OBJECTID(+)
AND M.MAP_TYPE = I2.OBJECT_TYPE(+)
AND I2.OPTION_TYPE(+) = 21
AND (O.OWNER# IN (USERENV('SCHEMAID')
, 1) -- PUBLIC OBJECTS
OR O.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 (-309
, -- ALTER ANY CUBE
-311
, -- DROP ANY CUBE
-312
, -- SELECT ANY CUBE
-313) -- UPDATE ANY CUBE
)
)
)
AND ((HAVE_ALL_DIM_ACCESS = 1) OR (HAVE_ALL_DIM_ACCESS IS NULL))
|
|
|