Skip to main content
Pentaho Documentation

Define Schema Security

Now you have all this great data, but what if you do not want everyone to be able to read all of it? To solve this, you can define a security profile, called a Role, as part of the schema, and set this role when establishing a connection. This restricts the role so that when you do publish the schema, only the specific parts that you designate will be available to members of that particular role.

Define a Role

Roles are defined by <Role> elements, which occur as direct children of the <Schema> element, after the last <Cube>. Here is an example of a role.

<Role name="California manager">
<SchemaGrant access="none">
<CubeGrant cube="Sales" access="all">
<DimensionGrant hierarchy="[Measures]" access="all"/>
<HierarchyGrant hierarchy="[Store]" access="custom" topLevel="[Store].[Store Country]">
<MemberGrant member="[Store].[USA].[CA]" access="all"/>
<MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Customers]" access="custom" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]">
<MemberGrant member="[Customers].[USA].[CA]" access="all"/>
<MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Gender]" access="none"/>
</CubeGrant>
</SchemaGrant>
</Role>

A <SchemaGrant> defines the default access for objects in a schema. The access attribute can be all or none; this access can be overridden for specific objects. In this case, because access="none", a user would only be able to browse the Sales cube, because it is explicitly granted.

A <CubeGrant> defines the access to a particular cube. As for <SchemaGrant>, the access attribute can be all, custom, or none, and can be overridden for specific subobjects in the cube.

A <DimensionGrant> defines access to a dimension. The access attribute can be all, custom, or none. An access level of all means that all the child hierarchies of the dimension will get inherited access. An access level of custom means that the role does not get an inherent access to the child hierarchies, unless the role is explicitly granted using a <HierarchyGrant> element.

A <HierarchyGrant> defines access to a hierarchy. The access attribute can be all, meaning all members are visible; none, meaning the hierarchy's very existence is hidden from the user; and custom. With custom access, you can use the topLevel attribute to define the top level which is visible; this prevents users from seeing too much of the 'big picture', such as viewing revenues rolled up to the Store Country level. Or you can use the bottomLevel attribute to define the bottom level which is visible; this keeps users from looking at individual customers' details. Control which sets of members the user can see, by defining nested <MemberGrant> elements.

You can only define a <MemberGrant> element if its enclosing <HierarchyGrant> has access="custom". Member grants give or remove access to a given member, and all of its children. Here are the rules.

  1. Members inherit access from their parents. If you deny access to California, you will not be able to see San Francisco.
  2. Grants are order-dependent. If you grant access to USA, then deny access to Oregon, then you will not be able to see Oregon, or Portland. But if you were to deny access to Oregon, then grant access to USA, you can effectively see everything.
  3. A member is visible if any of its children are visible. Suppose you deny access to USA, then grant access to California. You will be able to see USA, and California, but none of the other states. The totals against USA will still reflect all states, however. If the parent HierarchyGrant specifies a top level, only the parents equal or below this level will be visible. Similarly, if a bottom level is specified, only the children above or equal to the level are visible.
  4. Member grants do not override the hierarchy grant's top- and bottom-levels. If you set topLevel="[Store].[Store State]", and grant access to California, you will not be able to see USA. Member grants do not override the topLevel and bottomLevel attributes. You can grant or deny access to a member of any level, but the top and bottom constraints have precedence on the explicit member grants.

In the example, the user will have access to California, and all of the cities in California except Los Angeles. They will be able to see USA—because its child, California, is visible—but no other nations, and not All Stores, because it is above the top level, Store Country.

Rollup Policy

A rollup policy determines how Mondrian computes a member's total if the current role cannot see all of that member's children. Under the default rollup policy, called full, the total for that member includes contributions from the children that are not visible. For example, suppose that Fred belongs to a role that can see [USA].[CA] and [USA].[OR] but not [USA].[WA]. If Fred runs the query:

SELECT {[Measures].[Unit Sales]} ON COLUMNS,
    {[[Store].[USA], Store].[USA].Children} ON ROWS
FROM [Sales]

the query returns this:

[Customer][Measures].[Unit Sales]
[USA]	             266,773
[USA].[CA]	        74,748
[USA].[OR]	        67,659

Note that [USA].[WA] is not returned, per the access control policy, but the total includes the total from Washington (124,366) that Fred cannot see. For some applications, this is not appropriate. In particular, if the dimension has a small number of members, the end-user may be able to deduce the values of the members which they do not have access to.

To remedy this, a role can apply a different rollup policy to a hierarchy. The policy describes how a total is calculated for a particular member if the current role can only see some of that member's children.

  • Full. The total for that member includes all children. This is the default policy, if you do not specify the rollupPolicy attribute.
  • Partial. The total for that member includes only accessible children.
  • Hidden. If any of the children are inaccessible, the total is hidden.

Under the partial policy, the [USA] total is the sum of the accessible children [CA] and [OR].

[Customer][Measures].[Unit Sales]
[USA]	             142,407  
[USA].[CA]	        74,748
[USA].[OR]	        67,659

Under hidden policy, the [USA] total is hidden because one of its children is not accessible.

[Customer][Measures].[Unit Sales]
[USA]	             -  
[USA].[CA]	        74,748
[USA].[OR]	        67,659

The policy is specified per role and hierarchy. In the following example, the role sees partial totals for the [Store] hierarchy but full totals for [Product].

<SchemaGrant access="none">
<CubeGrant cube="Sales" access="all">
<HierarchyGrant hierarchy="[Store]" access="custom" rollupPolicy="partial" topLevel="[Store].[Store Country]">
<MemberGrant member="[Store].[USA].[CA]" access="all"/>
<MemberGrant member="[Store].[USA].[CA].[Los Angeles]" access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Customers]" access="custom" rollupPolicy="full" topLevel="[Customers].[State Province]" bottomLevel="[Customers].[City]">
<MemberGrant member="[Customers].[USA].[CA]" access="all"/>
<MemberGrant member="[Customers].[USA].[CA].[Los Angeles]" access="none"/>
</HierarchyGrant>
<HierarchyGrant hierarchy="[Gender]" access="none"/>
</CubeGrant>
</SchemaGrant>
</Role>

This example also shows existing features, such as how hierarchy grants can be restricted using topLevel and/or bottomLevel attributes, and how a role can be prevented from seeing a hierarchy using access="none".

Union Roles

A union role combines several roles, and has the sum of their privileges. A union role can see a particular schema object if one or more of its constituent roles can see it. Similarly, the rollup policy of a union role with respect to a particular hierarchy is the least restrictive of all of the roles' rollup policies.

Here is an example showing the syntax of a union role.

<Role name="Coastal manager">
<Union>
<RoleUsage roleName="California manager"/>
<RoleUsage roleName="Eastern sales manager"/>
</Union>
</Role>

The constituent roles California manager and Eastern sales manager may be regular roles, user-defined roles, or union roles, but they must be declared earlier in the schema file. The Coastal manager role will be able to see any member that or a California manager and Eastern sales manager. It will be able to see all the cells at the intersection of these members, plus it will be able to see cells that neither role can see. For example, if only California manager can see [USA].[CA].[Fresno], and only Eastern sales manager can see the [Sales Target] measure, then Coastal manager will be able to see the sales target for Fresno, which neither of the constituent roles will have access to.