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
-drillthrough
-- 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.
-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.
-drillthrough
-- Authorize/deny the reporting data-source.
[+|-] reportingDataSource data-source-name
-- Deny all reporting data-source for this schema.
-reportingDataSourceS
-- 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.
-perspectiveS
-- 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
Preprocessing
Schema scope definitions can contain one or more preprocessing directives.
#IF/#ELSE/#ENDIF
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
#ELSE
-- applied statements when mdx-expression is false
#ENDIF
#FOREACH HIERARCHY/#ENDFOREACH
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
#ENDFOREACH
Examples
Example 1
An empty profile is granting full access to all the schemas.
Example 2
The following profile is denying access to all schemas.
-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]
#ELSE
+measureGroup [Sales].[France]
#ENDIF
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
#ENDFOREACH