Mondrian Security
RBAC in Mondrian ..
Workshop - Mondrian Security
Publishing OLAP cubes for organizational use requires more than just creating sophisticated dimensional models—you must ensure that users only access data appropriate to their roles and responsibilities. While relational database security can restrict table-level access, OLAP security must operate at multiple granularities: entire schemas, specific cubes, individual dimensions and hierarchies, and even particular members within those hierarchies. Mondrian's role-based access control (RBAC) framework provides comprehensive security mechanisms that enable you to implement precise, multi-layered data protection policies directly within your schema definitions.
In this comprehensive hands-on workshop, you'll implement enterprise-grade security for the Miniature Models schema by creating four distinct roles with progressively restrictive access patterns. You'll configure role mappers to bridge Pentaho platform roles with Mondrian schema roles, then systematically apply SchemaGrant, CubeGrant, HierarchyGrant, and MemberGrant security constraints that control exactly what data each role can access. Additionally, you'll explore rollup policies—a sophisticated feature that determines how aggregated totals behave when users have restricted access to underlying detail members—ensuring that security doesn't inadvertently expose restricted information through aggregate values.
What You'll Accomplish:
Create platform users (exec_user, sales_mgr, region_mgr, analyst) and assign Pentaho permissions
Configure the Lookup Map role mapper in pentahoObjects.spring.xml to connect platform roles to Mondrian roles
Implement SchemaGrant with "all" access for Executive Role providing unrestricted schema access
Implement SchemaGrant with "none" access plus CubeGrant for Sales Manager Role restricting to specific cubes
Add levels (State, City) to the CUSTOMERS dimension hierarchy for granular security testing
Implement HierarchyGrant with topLevel and bottomLevel restrictions for Analyst Role limiting drill-down depth
Create MemberGrant security for Regional Manager Role restricting access to specific states (NJ, NY, PA)
Understand security inheritance and how granting child access implicitly grants parent access
Configure rollup policies (full, partial, hidden) to control aggregate behavior with restricted access
Test and validate each security configuration by logging in as different users
Refresh Mondrian Schema Cache to apply security changes
By the end of this workshop, you'll understand how Mondrian's hierarchical security model enables precise, maintainable data protection that mirrors organizational structure and responsibilities. Rather than creating separate views or cubes for different user groups—an approach that duplicates data and creates maintenance nightmares—you'll implement role-based access control that maintains a single source of truth while presenting appropriately filtered data based on user credentials. You'll see how executives view complete enterprise data, sales managers access departmental cubes, regional managers see only their territories, and analysts work with summarized data without customer-level details—all from the same underlying schema secured through declarative XML configuration rather than complex application-level security code.
Prerequisites: Completion of Miniature Models workshop; Schema Workbench and Pentaho Server installed with administrative access; Understanding of dimension hierarchies and member navigation; Familiarity with XML editing and Pentaho user management
Estimated Time: 120 minutes
Overview Security Concepts
Before implementing security, it's important to understand the hierarchy of security grants in Mondrian:
Important: Granting access to a child element implicitly grants access to its parent. For example, granting access to a cube implicitly grants access to the schema.
SchemaGrant
Controls access to the entire schema
CubeGrant
Controls access to individual cubes within a schema
DimensionGrant
Controls access to entire dimensions
HierarchyGrant
Controls access to hierarchies and can restrict by level (topLevel, bottomLevel)
MemberGrant
Controls access to specific members within a hierarchy
Start Schema Workbench:
Ensure Pentaho Server is running:
Ensure that the Pentaho Server is up and running (automatically started in Pentaho Lab):
Setup
Let's drill down even further ..!
The Regional Manager should only see data for the Western USA region, states that include California, Oregon, and Washington.
exec_user
Executive
Full access to all data
sales_mgr
Sales Manager
Access to sales cube only
region_mgr
Regional Manager
Limited to specific regions only
analyst
Analyst
Summary data only, no detailed records
Log into Pentaho User Console as Administrator.
Username: admin
Password: password
Navigate to Administration Perspective from the drop-down menu.
Select Users & Roles > Manage Users.
Click on the + sign to create each user from the table above with password: password
Navigate to Users & Roles > Manage Roles
Assign the following Roles / Permissions to the User:
Executive
All the permissions
exec_user
Sales Manager
Schedule Content
Read Content
Publish Content
Create Content
Execute
sales_mgr
Regional Manager
Schedule Content
Read Content
Publish Content
Create Content
Execute
region_mgr
Analyst
Publish Content
Create Content
analyst
Role Mapper
The role mapper connects Pentaho user roles to Mondrian schema roles. We will use the Lookup Map role mapper to demonstrate flexible role mapping.
Open the file - pentahoObjects.spring.xml:
Locate the Mondrian-UserRoleMapper section (approximately line 295)
Comment out the default One-to-One mapper if it's active:
Uncomment and configure the Lookup Map role mapper:
Save the file.
Restart the Pentaho server for changes to take effect.
The key represents the Pentaho role name, and the value represents the Mondrian role name that will be defined in the schema.
SchemaGrant
SchemaGrant controls access to the Miniature Models schema. We'll implement two scenarios:
full access:
no access:
Refresh the Mondrian Schema Cache
Please remember to refresh the Mondrian Schema Cache every time you edit and save the Miniature Models - security.xml schema ..
Log in as Administrator.
Password: password
Follow the steps below to understand the difference bewteen Full & No access:
Full Access - Executive Role
The Executive role should have unrestricted access to all cubes and dimensions in the schema. The easiest method to apply the SchemaGrant is by editing
Open the Miniature Models - original.xml schema.
Save as: Miniature Models - security.
Highlight Schema and Click on the 'User shadow+'.
Enter: Executive Role & tab to set the value.
Right-mouse on Executive Role & Select: Add Schema Grant:
Select: all & tab to set the value:
Right-mouse click on the Schema Grant and Add Cube Grant:
Set access: all
From the cube drop-down, select: Sales_FY2003_2005:
Click Save & Publish.
Currently only Executive Role users have access to the Miniature Models - security schema / Sales_FY2003_2005 cube as a data source ..
To test login as: exec_user
To test log in as: analyst
Sales Manager
So far only the Executive Role users have access to the Miniature Models - security schema as a data source. Next step is to set access to the Sales_FY2003_2005 cube only for the Sales Managers Role users.
Open the Miniature Models - security.xml schema.
Highlight Schema and Click on the 'User shadow+'.
Enter: Sales Managers Role & tab to set the value.
Right-mouse on Sales Managers Role & Select: Add Schema Grant.
Select: none & tab to set the value:
Right-mouse click on the Schema Grant and Add Cube Grant.
Set the access: all & tab to set the value.
From the cube drop-down, select cube: Sales_FY2003_2005 & tab.
Click Save & Publish.
To test login in as: sales_mgr
Setting access='none' at the SchemaGrant level blocks all cubes by default. The CubeGrant then selectively grants access to the Sales_FY2003_2005 cube, implicitly granting access to the parent schema as well.
Remember to refresh the Mondrian Schema Cache ..
HierarchyGrant
HierarchyGrant allows fine-grained control over which levels of a hierarchy users can access. We'll use topLevel and bottomLevel attributes to restrict the Analyst role's view.
The Analyst role should see summarized data only, without access to the most granular details. We'll restrict their view to show data from Country level down to City level, but not individual PostalCodes.
So were going to have to add some Levels to our Schema:
All > Territory > Country > State > City > Customer Name
Refresh the Mondrian Schema Cache
Please remember to refresh the Mondrian Schema Cache every time you edit and save the Miniature Models - security.xml schema ..
Log in as Administrator.
Password: password
To add another level, in the left pane, right-click the Customers (hierarchy) under CUSTOMERS and select Add Level.
To define the State level, type or choose:
name
State
column
STATE
type
String
levelType
Regular
hideMemberIf
Never
To define the City level, type or choose:
name
City
column
CITY
type
String
levelType
Regular
hideMemberIf
Never
Open the Miniature Models - security.xml schema.
Highlight Schema and Click on the 'User shadow+'.
Enter: Analyst Role & tab to set the value.
Right-mouse on Analyst Role & Select: Add Schema Grant.
Select: none & tab to set the value:
Right-mouse click on the Schema Grant and Add Cube Grant.
Set the access: all & tab to set the value.
From the cube drop-down, select cube: Sales_FY2003_2005 & tab.
Right-mouse click on the Cube Grant and Add Hierarchy Grant.
From the drop-down options select the following:
access
custom
hierarchy
[CUSTOMERS.Customers]
topLevel
[CUSTOMERS.Customers].[Country]
bottomLevel
[CUSTOMERS.Customers].[City]
Top & Bottom Level
topLevel: The highest (most aggregated) level the user can see. Setting it to Country means the user cannot see the All level.
bottomLevel: The lowest (most detailed) level the user can see. Setting it to City means the user cannot see individual Store data.
Result: The Analyst can see data aggregated from Country > Region > City, but not Customer Name -level details.
Right-mouse click on the Cube Grant and Add Hierarchy Grant.
From the drop-down options select the following:
access
all
hierarchy
[PRODUCTS.Products]
Click Save & Publish.
To test login in as: analyst
Notice: The scope of the Customers Hierarchy has been restricted - no Territory & PostalCode
Member Grant
Let's dive a bit deeper..!
The Regional Manager Role should only see data for the Eastern USA region, which include: New York, New Jersey, and Pennsylvania.
Refresh the Mondrian Schema Cache
Please remember to refresh the Mondrian Schema Cache every time you edit and save the Miniature Models - security.xml schema ..
Log in as Administrator.
Password: password
Open the Miniature Models - security.xml schema.
Highlight Schema and Click on the 'User shadow+'.
Enter: Regional Manager Role & tab to set the value.
Right-mouse on Regional Manager Role & Select: Add Schema Grant.
Select: none & tab to set the value:
Right-mouse click on the Schema Grant and Add Cube Grant.
Set the access: all & tab to set the value.
From the cube drop-down, select cube: Sales_FY2003_2005 & tab.
Right-mouse click on the Cube Grant and Add Hierarchy Grant.
From the drop-down options select the following:
access
custom
hierarchy
[CUSTOMERS.Customers]
topLevel
[CUSTOMERS.Customers].[State]
Right-mouse click on Hierarchy Grant to Add a Member Grant.
Add the Member constraints for each State: [NJ] [NY] [PA]
Repeat the workflow to add the following Member Grants:
access
all
member
[CUSTOMERS.Customers].[NA].[USA].[NJ]
[CUSTOMERS.Customers].[NA].[USA].[NY]
[CUSTOMERS.Customers].[NA].[USA].[PA]
Finally add the PRODUCTS.Products hierarchy:
access
all
hierarchy
{PRODUCTS.Products]
Finally Save & Publish.
With access="custom", Mondrian denies everything by default and only allows what you explicitly grant. By only granting access to NJ, NY, and PA, those are the only states that will be visible.
To test login in as: region_mgr
Notice topLevel: STATE. The default behaviour is to restrict the total / SUM as a Partial rollup.
Rollup Policies
Rollup policies control how measures are aggregated when role-based security restricts access to certain members in a dimension hierarchy. This becomes critical when you have hierarchies with restricted access - topLevel & bottomLevel will restrict the aggregation.
When a role restricts access to certain dimension members, what should happen to the totals at higher levels? Should they be included the restricted data or not?
full
Shows the complete total including hidden members. User sees accurate company-wide totals but cannot infer hidden values if few members are visible.
partial
Shows total of only visible members. User sees accurate subtotals for their authorized data. Useful when users should only see their scope.
hidden
Hides the total completely if any children are not accessible. Most restrictive option, prevents any inference of hidden data.
Executive / VP
full
Need complete picture, restricted drill-down
Regional Manager
partial
Should only see their region's totals
Contributor
partial or hidden
Limited scope, no need for aggregates
Analyst (restricted data)
full
Need context of full data while working with subset
Remember the aggregation level is determined by the access restrictions. So .. If Regional Manager needs to see "complete totals but limited drill-down":
Use top-down grant with denials:
Was this helpful?