VIEW: SYS.ALL_CUBE_MEAS_MAPPINGS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  o.name CUBE_NAME,
  owner_map.map_name CUBE_MAP_NAME,
  m.map_name MAP_NAME,
  m.map_id MAP_ID,
  meas.measure_name MEASURE_NAME,
  s1.syntax_clob MEASURE_EXPRESSION
FROM
  olap_mappings$ m,
  olap_mappings$ owner_map,
  user$ u,
  obj$ o,
  olap_measures$ meas,
  olap_syntax$ s1,
 (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 = 24
  AND m.mapped_object_id = meas.measure_id
  AND m.mapping_owner_id = owner_map.map_id
  AND meas.cube_obj# = 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(+) = 1
  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))

View Text - HTML Formatted

SELECT U.NAME OWNER
, O.NAME CUBE_NAME
, OWNER_MAP.MAP_NAME CUBE_MAP_NAME
, M.MAP_NAME MAP_NAME
, M.MAP_ID MAP_ID
, MEAS.MEASURE_NAME MEASURE_NAME
, S1.SYNTAX_CLOB MEASURE_EXPRESSION FROM OLAP_MAPPINGS$ M
, OLAP_MAPPINGS$ OWNER_MAP
, USER$ U
, OBJ$ O
, OLAP_MEASURES$ MEAS
, OLAP_SYNTAX$ S1
, (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 = 24
AND M.MAPPED_OBJECT_ID = MEAS.MEASURE_ID
AND M.MAPPING_OWNER_ID = OWNER_MAP.MAP_ID
AND MEAS.CUBE_OBJ# = 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(+) = 1
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))