Schema Permissions

Schema permissions allows for granting access to data from the schema level down to a (cube) cell level.

Authorization Language

Schemas permissions are defined using some sort of authorization language. Note that this authorization language is the same language as the one used for the on-the-fly authorization (i.e., when the role is defined using data outside icCube when the user logs in).

Permissions are defined using "lines"; each line is either granting access (+) or denying access (-) to the defined MDX entity(ies). Within a scope, the first '+' means all entities are DENIED first whereas the first '-' means all entities are AUTHORIZED first. See examples below.

Since icCube v8.4.0, permissions for a given schema can define functions and sets.

MDX entity names are following the MDX standard (e.g., [Sales], [Time].[Calendar]).

Global Section

Define global authorizations that apply to all schemas and define the list of authorized schemas.

-- Defines the default Read/Write mode for all schemas (default: W)
-- Can be re-defined for any given schema later.

+schemaAccess [R|W]

-- Deny drillthrough for all the schemas


-- Authorize/deny a schema; use a single line for each schema
-- (if not specified all schemas are authorized).

[+|-] schema schema-name

-- Deny access to all the schemas.



Schema Section

For each specified schema, grant or deny access to the schema entities.

-- The following line introduces a schema scope: following authorization lines
-- apply to this schema:

:schema schema-name

-- Create helper sets and functions for complex MDX expressions in following definitions.
-- Note that each set/function declaration MUST be followed by an empty line.

CREATE SET [set-name] AS {

CREATE FUNCTION fun-name(params...) AS ...

-- (Re)Define the schema access mode for this schema.

+schemaAccess [R|W]

-- Disable drillthrough for this schema.


-- Authorize/deny the reporting data-source.

[+|-] reportingDataSource data-source-name

-- Deny all reporting data-source for this schema.


-- Authorize/deny the dimension.

[+|-] dimension dimension-name

-- Authorize/deny the hierarchy

[+|-] hierarchy hierarchy-name

-- Deny the level removing all descendant levels.

-level level-name

-- Allow for redefining the hierarchy default members ( e.g., [Geography].[GEO].[France] )

+defaultMember default-member-name

-- Authorize/deny the cube as well as defining its read/write access mode.

[+|-] cube [R|W] cube-name

-- Authorize/deny the measure-group (aka. facts). Prefix the name with its cube.

[+|-] measureGroup cube-name.measure-group-name

-- Authorize/deny the measure. Prefix the name with its cube.
-- When a measure-group access has been specified, those measure are added/removed
-- from the list of measures authorized by the measure-group rules.

[+|-] measure measure-name

-- Authorize/deny the perspective.

[+|-] perspective perspective-name

-- Deny all perspective for this schema.


-- Allows for authorizing members/tuples.
--   D       : applies only to the dimension definition (e.g. denying a specific member).
--   C       : cells/facts (measures will not access rows defined by the members (e.g.
--             removing the access to all data defined by a specific member)).
--   DA      : same as the D options (where the set-expression is a set of members only) + for the base hierarchy
--             members includes the matching members for all attributes hierarchies (using this option with the [-]
--             might give unexpected results as the same attribute hierarchies members might be shared by the different
--             base hierarchy members).
--   NO_DESC : does not apply to descendants.
--   Default : DC and DESC
--   measureGroups=measure-group-names-expression
--             measure-group-name             := [cube-name].[measure-group-names]
--             measure-group-names-expression := [-](measure-group-name)(,measure-group-name)+
--             An optional list of measure groups (applies and allowed when the C option applies).
--             When not specified the tuples statement applies to all the measure groups.
--   The order of the sequence is relevant:
--       -FRANCE followed by +FRANCE == FRANCE authorized
--       +FRANCE followed by -FRANCE == FRANCE denied
--   Calculated measures/members are not supported (note that perspectives can hide calculated measures/members).

[+|-] tuples [D|C|DC|DA|DAC] [NO_DESC] [measureGroups=measure-group-names-expression] set-expression



Schema scope definitions can contain one or more preprocessing directives.


Allows for including/excluding authorization statements based on an MDX expression value. This expression is considered as true if not empty, not null, not zero or not false. Nested directives are not allowed.

#IF mdx-expression

-- applied statements when mdx-expression is true


-- applied statements when mdx-expression is false



Allows for repeating some statements for each iterated hierarchy accessible via the $current string. Nested directives are not allowed. Cannot contain any #IF directive.

#FOREACH HIERARCHY [dimension] [mdx-hierarchy-filter-expression]

-- repeated statements for each hierarchy accessible via the $current string



Example 1

An empty profile is granting full access to all the schemas.


Example 2

The following profile is denying access to all schemas.


Example 3

The following profile is granting read access to all schemas but [Sales].

+schemaAccess R
-schema [Sales]

Example 4

The following profile is granting access to the schema [Sales] only and is removing all data from Switzerland. Note that the member [Switzerland] is still visible.

+schema [Sales]

:schema [Sales]
-tuples C [Geography].[Geo].[Switzerland]

Example 5

The following profile is granting access to the schema [Sales] only and is denying access to the member (and its data) Switzerland.

+schema [Sales]

:schema [Sales]
-tuples DC [Geography].[Geo].[Switzerland]

Example 6

The following profile is showing how to explicitly refer to a measure group in +tuples statements. Note the usage of the cube name [Sales] prefixing the measure group name [Sales] itself.

+schema [Sales]

:schema [Sales]
-tuples DC measureGroups=[Sales].[Sales] [Geography].[Geo].[Switzerland]

Example 7

The following profile is demonstrating the usage of a pre-processing #IF/#ENDIF to define the authorization based on a MDX expression value.

+schema [Sales]

:schema [Sales]

#IF ( [Geography].[Country].[Switzerland], [Measures].[Sales] )

+measureGroup [Sales].[Switzerland]


+measureGroup [Sales].[France]


Example 8 (Attribute)

If 'Continent' and 'Currency' are defined as attributes, [Geography].[Continent].[Europe] and [Geography].[Currency].[CHF] will be the only ones authorized as well.

+schema [Sales]

:schema [Sales]
+tuples DAC [Geography].[Geo].[Switzerland]

# As [Geo] is the base hierarchy, this is equivalent to :
# +tuples DC [Geography].[Geo].[Switzerland]
# +tuples D  [Geography].[Continent].[Europe]
# +tuples D  [Geography].[Currency].[CHF]
# Note how changing the [+] by the [-] might give unexpected results

Example 9 (Properties)

Properties that are Attributes are impacted by the authorization. For example, if 'Continent' is defined as an attribute, the property 'Continent' is not available for base european hierarchy members, i.e. [Geography].[Country].[Switzerland]

+schema [Sales]

:schema [Sales]
-tuples D [Geography].[Continent].[Europe]

Example 10

The following profile is granting access to the schema [Sales] only and is removing all data from Switzerland and France. Note that the members [Switzerland] and [France] are still visible.

+schema [Sales]

:schema [Sales]

create set [countries] as { [Geography].[Geo].[Switzerland], [Geography].[Geo].[France] }
-tuples C [countries]

Example 11 (#FOREACH)

The following is denying all the derived (i.e., non based hierarchy) hierarchies of the [Product] dimension.

+schema [Sales]

:schema [Sales]

#FOREACH HIERARCHY [Product] $current.uniqueName <> [Product].[Product].uniqueName

-hierarchy $current