VIEW: SYS.ALL_CUBE_DESCRIPTIONS

Source

View Text - Preformatted

SELECT
  u.name OWNER,
  CASE d.owning_object_type
  WHEN 4 -- ASSIGNMENT
  THEN (SELECT o.name || '.' || m.model_name || '.' || a.member_name
        FROM olap_model_assignments$ a, olap_models$ m, obj$ o
        WHERE d.owning_object_id = a.assignment_id
              AND m.model_id = a.model_id
              AND m.owning_obj_type = 11
              AND m.owning_obj_id = o.obj#
        )
  WHEN 3 -- model
  THEN (SELECT o.name || '.' || m.model_name
        FROM olap_models$ m, obj$ o
        WHERE d.owning_object_id = m.model_id
              AND m.owning_obj_type = 11
              AND m.owning_obj_id = o.obj#
        )
  WHEN 14 -- hier_level
  THEN (SELECT o.name || '.' || h.hierarchy_name || '.' || dl.level_name
        FROM olap_hier_levels$ hl, olap_dim_levels$ dl,
             olap_hierarchies$ h, obj$ o
        WHERE d.owning_object_id = hl.hierarchy_level_id
              AND hl.dim_level_id = dl.level_id
              AND hl.hierarchy_id = h.hierarchy_id
              AND h.dim_obj# = o.obj#
        )
  WHEN 13 -- hierarchy
  THEN (SELECT o.name || '.' || h.hierarchy_name
        FROM olap_hierarchies$ h, obj$ o
        WHERE d.owning_object_id = h.hierarchy_id
              AND h.dim_obj# = o.obj#
       )
  WHEN 12 -- dim_level
  THEN (SELECT o.name || '.' || dl.level_name
        FROM olap_dim_levels$ dl, obj$ o
        WHERE d.owning_object_id = dl.level_id
              AND dl.dim_obj# = o.obj#
       )
  WHEN 15 -- attribute
  THEN (SELECT o.name || '.' || a.attribute_name
        FROM olap_attributes$ a, obj$ o
        WHERE d.owning_object_id = a.attribute_id
              AND a.dim_obj# = o.obj#
       )
  WHEN 6 -- calc_member
  THEN (SELECT o.name || '.' || c.member_name
        FROM OLAP_CALCULATED_MEMBERS$ c, obj$ o
        WHERE d.owning_object_id = c.member_id
              AND c.dim_obj# = o.obj#
       )
  WHEN 11 -- dimension
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  ELSE null
  END AS OBJECT_NAME,
  decode(d.owning_object_type, '4', 'ASSIGNMENT',
                               '3', 'MODEL',
                               '14', 'HIERARCHY LEVEL',
                               '13', 'HIERARCHY',
                               '12', 'DIMENSION LEVEL',
                               '15', 'ATTRIBUTE',
                               '6', 'CALCULATION MEMBER',
                               '11', 'DIMENSION') OBJECT_TYPE,
  d.description_type DESCRIPTION_TYPE,
  d.description_value DESCRIPTION_VALUE,
  d.language LANGUAGE
FROM
  olap_descriptions$ d,
  user$ u,
  obj$ o
WHERE
  d.description_class is null
  AND d.obj# = o.obj#
  AND o.owner# = u.user#
  AND d.owning_object_type in (3, 4, 6, 11, 12, 13, 14, 15)
  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 (-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
                        )
              )
            )
UNION ALL
SELECT
  u.name OWNER,
  CASE d.owning_object_type
  WHEN 2 -- measure
  THEN (SELECT o.name || '.' || m.measure_name
        FROM olap_measures$ m, olap_cubes$ c, obj$ o
        WHERE d.owning_object_id = m.measure_id
              AND m.cube_obj# = c.obj#
              AND c.obj# = o.obj#
        )
  WHEN 1 -- cube
  THEN (SELECT o.name
        FROM obj$ o
        WHERE d.owning_object_id = o.obj#
       )
  ELSE null
  END AS OBJECT_NAME,
  decode(d.owning_object_type, '1', 'CUBE',
                               '2', 'MEASURE') OBJECT_TYPE,
  d.description_type DESCRIPTION_TYPE,
  d.description_value DESCRIPTION_VALUE,
  d.language LANGUAGE
FROM
  olap_descriptions$ d,
  user$ u,
  obj$ o,
  (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
  d.description_class is null
  AND d.obj# = o.obj#
  AND o.owner# = u.user#
  AND o.obj#=da.obj#(+)
  AND d.owning_object_type in (1, 2)
  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))
UNION ALL
SELECT
  u.name OWNER,
  o2.name OBJECT_NAME,
  'MEASURE FOLDER' OBJECT_TYPE,
  d.description_type DESCRIPTION_TYPE,
  d.description_value DESCRIPTION_VALUE,
  d.language LANGUAGE
FROM
  olap_descriptions$ d,
  user$ u,
  obj$ o,
  obj$ o2
WHERE
  d.description_class is null
  AND d.obj# = o.obj#
  AND o.owner# = u.user#
  AND d.owning_object_id = o2.obj#
  AND d.owning_object_type = 10 -- MEASURE FOLDER
  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 (-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   -- user has access to cubes in measure folder
              ( exists (select null from olap_meas_folder_contents$ mfc, olap_measures$ m
                        where mfc.measure_folder_obj# = o2.obj#
                          and m.measure_id  = mfc.object_id
                          and (
                              m.cube_obj# in
                                ( select obj#  -- directly granted authorization
                                  from sys.objauth$
                                  where grantee# in ( select kzsrorol from x$kzsro )
                                )
                              )
                       )
              )
            )
UNION ALL
SELECT
  u.name OWNER,
  o2.name OBJECT_NAME,
  'BUILD PROCESS' OBJECT_TYPE,
  d.description_type DESCRIPTION_TYPE,
  d.description_value DESCRIPTION_VALUE,
  d.language LANGUAGE
FROM
  olap_descriptions$ d,
  user$ u,
  obj$ o,
  obj$ o2
WHERE
  d.description_class is null
  AND d.obj# = o.obj#
  AND o.owner# = u.user#
  AND d.owning_object_id = o2.obj#
  AND d.owning_object_type = 8 --BUILD_PROCESS
  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 (-321, -- DROP ANY BUILD PROCESS
                                              -322, -- UPDATE ANY BUILD PROCESS
                                              -395, -- SELECT ANY BUILD PROCESS
                                              -396) -- ALTER ANY BUILD PROCESS
                        )
              )
            )

View Text - HTML Formatted

SELECT U.NAME OWNER
, CASE D.OWNING_OBJECT_TYPE WHEN 4 -- ASSIGNMENT THEN (SELECT O.NAME || '.' || M.MODEL_NAME || '.' || A.MEMBER_NAME
FROM OLAP_MODEL_ASSIGNMENTS$ A
, OLAP_MODELS$ M
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = A.ASSIGNMENT_ID
AND M.MODEL_ID = A.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = O.OBJ# ) WHEN 3 -- MODEL THEN (SELECT O.NAME || '.' || M.MODEL_NAME
FROM OLAP_MODELS$ M
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = M.MODEL_ID
AND M.OWNING_OBJ_TYPE = 11
AND M.OWNING_OBJ_ID = O.OBJ# ) WHEN 14 -- HIER_LEVEL THEN (SELECT O.NAME || '.' || H.HIERARCHY_NAME || '.' || DL.LEVEL_NAME
FROM OLAP_HIER_LEVELS$ HL
, OLAP_DIM_LEVELS$ DL
, OLAP_HIERARCHIES$ H
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = HL.HIERARCHY_LEVEL_ID
AND HL.DIM_LEVEL_ID = DL.LEVEL_ID
AND HL.HIERARCHY_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = O.OBJ# ) WHEN 13 -- HIERARCHY THEN (SELECT O.NAME || '.' || H.HIERARCHY_NAME
FROM OLAP_HIERARCHIES$ H
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = H.HIERARCHY_ID
AND H.DIM_OBJ# = O.OBJ# ) WHEN 12 -- DIM_LEVEL THEN (SELECT O.NAME || '.' || DL.LEVEL_NAME
FROM OLAP_DIM_LEVELS$ DL
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = DL.LEVEL_ID
AND DL.DIM_OBJ# = O.OBJ# ) WHEN 15 -- ATTRIBUTE THEN (SELECT O.NAME || '.' || A.ATTRIBUTE_NAME
FROM OLAP_ATTRIBUTES$ A
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = A.ATTRIBUTE_ID
AND A.DIM_OBJ# = O.OBJ# ) WHEN 6 -- CALC_MEMBER THEN (SELECT O.NAME || '.' || C.MEMBER_NAME
FROM OLAP_CALCULATED_MEMBERS$ C
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = C.MEMBER_ID
AND C.DIM_OBJ# = O.OBJ# ) WHEN 11 -- DIMENSION THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) ELSE NULL END AS OBJECT_NAME
, DECODE(D.OWNING_OBJECT_TYPE
, '4'
, 'ASSIGNMENT'
, '3'
, 'MODEL'
, '14'
, 'HIERARCHY LEVEL'
, '13'
, 'HIERARCHY'
, '12'
, 'DIMENSION LEVEL'
, '15'
, 'ATTRIBUTE'
, '6'
, 'CALCULATION MEMBER'
, '11'
, 'DIMENSION') OBJECT_TYPE
, D.DESCRIPTION_TYPE DESCRIPTION_TYPE
, D.DESCRIPTION_VALUE DESCRIPTION_VALUE
, D.LANGUAGE LANGUAGE FROM OLAP_DESCRIPTIONS$ D
, USER$ U
, OBJ$ O WHERE D.DESCRIPTION_CLASS IS NULL
AND D.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND D.OWNING_OBJECT_TYPE IN (3
, 4
, 6
, 11
, 12
, 13
, 14
, 15)
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 (-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 ) ) ) UNION ALL SELECT U.NAME OWNER
, CASE D.OWNING_OBJECT_TYPE WHEN 2 -- MEASURE THEN (SELECT O.NAME || '.' || M.MEASURE_NAME
FROM OLAP_MEASURES$ M
, OLAP_CUBES$ C
, OBJ$ O
WHERE D.OWNING_OBJECT_ID = M.MEASURE_ID
AND M.CUBE_OBJ# = C.OBJ#
AND C.OBJ# = O.OBJ# ) WHEN 1 -- CUBE THEN (SELECT O.NAME
FROM OBJ$ O
WHERE D.OWNING_OBJECT_ID = O.OBJ# ) ELSE NULL END AS OBJECT_NAME
, DECODE(D.OWNING_OBJECT_TYPE
, '1'
, 'CUBE'
, '2'
, 'MEASURE') OBJECT_TYPE
, D.DESCRIPTION_TYPE DESCRIPTION_TYPE
, D.DESCRIPTION_VALUE DESCRIPTION_VALUE
, D.LANGUAGE LANGUAGE FROM OLAP_DESCRIPTIONS$ D
, USER$ U
, OBJ$ O
, (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 D.DESCRIPTION_CLASS IS NULL
AND D.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND O.OBJ#=DA.OBJ#(+)
AND D.OWNING_OBJECT_TYPE IN (1
, 2)
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)) UNION ALL SELECT U.NAME OWNER
, O2.NAME OBJECT_NAME
, 'MEASURE FOLDER' OBJECT_TYPE
, D.DESCRIPTION_TYPE DESCRIPTION_TYPE
, D.DESCRIPTION_VALUE DESCRIPTION_VALUE
, D.LANGUAGE LANGUAGE FROM OLAP_DESCRIPTIONS$ D
, USER$ U
, OBJ$ O
, OBJ$ O2 WHERE D.DESCRIPTION_CLASS IS NULL
AND D.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND D.OWNING_OBJECT_ID = O2.OBJ#
AND D.OWNING_OBJECT_TYPE = 10 -- MEASURE FOLDER
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 (-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 -- USER HAS ACCESS TO CUBES IN MEASURE FOLDER ( EXISTS (SELECT NULL
FROM OLAP_MEAS_FOLDER_CONTENTS$ MFC
, OLAP_MEASURES$ M
WHERE MFC.MEASURE_FOLDER_OBJ# = O2.OBJ#
AND M.MEASURE_ID = MFC.OBJECT_ID
AND ( M.CUBE_OBJ# IN ( SELECT OBJ# -- DIRECTLY GRANTED AUTHORIZATION
FROM SYS.OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) ) ) ) ) UNION ALL SELECT U.NAME OWNER
, O2.NAME OBJECT_NAME
, 'BUILD PROCESS' OBJECT_TYPE
, D.DESCRIPTION_TYPE DESCRIPTION_TYPE
, D.DESCRIPTION_VALUE DESCRIPTION_VALUE
, D.LANGUAGE LANGUAGE FROM OLAP_DESCRIPTIONS$ D
, USER$ U
, OBJ$ O
, OBJ$ O2 WHERE D.DESCRIPTION_CLASS IS NULL
AND D.OBJ# = O.OBJ#
AND O.OWNER# = U.USER#
AND D.OWNING_OBJECT_ID = O2.OBJ#
AND D.OWNING_OBJECT_TYPE = 8 --BUILD_PROCESS
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 (-321
, -- DROP ANY BUILD PROCESS -322
, -- UPDATE ANY BUILD PROCESS -395
, -- SELECT ANY BUILD PROCESS -396) -- ALTER ANY BUILD PROCESS ) ) )