Access control and roles

Mondrian’s access-control model lets you carve up your schema into roles. A role is a named set of grants and denials that covers the entire schema — from which cubes are visible down to which individual members a user is allowed to see. You define roles directly in your schema file, and Saiku Cloud applies them when it establishes a connection on behalf of a user.

Defining a role

<Role> elements are direct children of <Schema>, placed after the last <Cube>. Here is a complete example:

YAML

- name: "California manager"
  schema_grant:
    access: "none"
    cubes:
    - cube: "Sales"
      access: "all"
      dimensions:
      - dimension: "[Measures]"
        access: "all"
      hierarchies:
      - hierarchy: "[Store]"
        access: "custom"
        top_level: "[Store].[Store Country]"
        members:
        - member: "[Store].[USA].[CA]"
          access: "all"
        - member: "[Store].[USA].[CA].[Los Angeles]"
          access: "none"
      - hierarchy: "[Customers]"
        access: "custom"
        top_level: "[Customers].[State Province]"
        bottom_level: "[Customers].[City]"
        members:
        - member: "[Customers].[USA].[CA]"
          access: "all"
        - member: "[Customers].[USA].[CA].[Los Angeles]"
          access: "none"
      - hierarchy: "[Gender]"
        access: "none"

XML

<Role name="California manager">
  <SchemaGrant access="none">
    <CubeGrant cube="Sales" access="all">
      <DimensionGrant dimension="[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>

The sections below explain each element in detail.

---

<SchemaGrant>

<SchemaGrant> sets the default access to the entire schema. Its access attribute can be:

Value	Meaning
all	The role can see every cube and dimension in the schema.
all_dimensions	The role can see all dimensions but still needs explicit <CubeGrant> entries to access any cube’s data.
none	The role can see nothing unless explicitly granted.

In the example above, access="none" means the user can browse only the “Sales” cube, because it is the only one explicitly granted.

---

<CubeGrant>

<CubeGrant> controls access to a specific cube. Its access attribute can be:

Value	Meaning
all	Full access to the cube and all its dimensions and hierarchies.
custom	Access is defined by child <DimensionGrant> and <HierarchyGrant> elements.
none	The cube is hidden from the role.

---

<DimensionGrant>

<DimensionGrant> controls access to a whole dimension within a cube. Its access attribute can be:

Value	Meaning
all	All child hierarchies of this dimension are accessible.
custom	No inherent access to child hierarchies — each hierarchy must be granted separately using <HierarchyGrant>.
none	The dimension is hidden.

The dimension attribute takes the dimension’s MDX unique name (for example "[Measures]" or "[Store]").

---

<HierarchyGrant>

<HierarchyGrant> controls access to a hierarchy, optionally constraining the visible levels and members.

Attribute	Required	Description
hierarchy	yes	MDX unique name of the hierarchy, e.g. "[Store]".
access	yes	"all", "custom", or "none".
topLevel	no	MDX unique name of the highest visible level. Members above this level are hidden.
bottomLevel	no	MDX unique name of the lowest visible level. Members below this level are hidden.
rollupPolicy	no	How to compute totals when some children are hidden. See Rollup policy.

access values

Value	Meaning
all	Every member is visible.
none	The hierarchy is hidden from the role.
custom	Visibility is controlled by topLevel, bottomLevel, and/or <MemberGrant> children.

---

<MemberGrant>

<MemberGrant> grants or denies access to a specific member and all of its children. You can only use <MemberGrant> when the enclosing <HierarchyGrant> has access="custom".

Attribute	Required	Description
member	yes	MDX unique name of the member, e.g. "[Store].[USA].[CA]".
access	yes	"all" or "none".

Rules for member grants

Member grants interact in a specific way when multiple grants apply to the same hierarchy:

1. Members inherit access from their parents. Denying access to California automatically denies access to San Francisco.
2. Grants are order-dependent. If you grant access to USA, then deny Oregon, Oregon is hidden. If you deny Oregon first and then grant USA, everything is visible. Order matters.
3. A member is visible if any of its children are visible. If you deny USA but grant California, you will see USA and California — but no other states. Note: totals for USA under the full rollup policy still reflect all states (see Rollup policy). If a topLevel is set, only parents at or below that level are shown.
4. Member grants do not override topLevel and bottomLevel. If topLevel="[Store].[Store State]" is set and you grant California, you still cannot see the USA level.

Example walkthrough

With the “California manager” role from the opening example, the user can:

• See California and all cities in California except Los Angeles.
• See USA (because California, a visible child, makes the parent visible), but no other countries.
• Not see “All Stores” because it lies above the topLevel of Store Country.
• Not see customers outside of the state-to-city band (State Province through City), and not Los Angeles.
• Not see the Gender hierarchy at all.

---

Predicate-based row security (<PredicateGrant>)

Member grants restrict which dimension members a role can see. Sometimes you instead need to restrict the fact rows themselves by a column value that tracks the requesting user — classic multi-tenant row-level security, or Looker’s access_filter. For that, Saiku adds a <PredicateGrant> (a Saiku extension to Mondrian 4, issue #106): it binds a role to a filter on a real fact column, valued from the connection’s query parameter.

<Role name="Regional">
  <SchemaGrant access="all"/>
  <PredicateGrant measureGroup="Sales" column="region"
                  operator="eq" parameter="region"/>
</Role>

The Calcite backend injects the predicate as a WHERE filter on every segment load of the named measure group, pre-aggregation, so totals — not just leaf rows — are correctly restricted, and the per-role result is isolated in the segment cache. An unbound parameter fails closed (zero rows), never unrestricted. operator is eq (equality) or in (membership).

Note

This is the row-security counterpart to member grants, and is documented in full — declaration, supplying the value at connect time, the security model, and the LookML access_filter mapping — on the Query parameters & row security page. Predicate grants require the Calcite query backend (mondrian.backend=calcite, the Saiku default).

---

Rollup policy

A rollup policy determines what value Mondrian shows for a parent member when the current role cannot see all of its children.

Policy	Meaning
full	The parent total includes all children, visible or not. This is the default.
partial	The parent total includes only the accessible children.
hidden	If any child is inaccessible, the parent total is suppressed.

Example: effect of each policy

Suppose a role can see [USA].[CA] and [USA].[OR] but not [USA].[WA], and runs:

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

Full (default) — [USA] total includes Washington’s 124,366 units:

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

Partial — [USA] total reflects only CA and OR:

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

Hidden — [USA] total is suppressed because at least one child is inaccessible:

Member	Unit Sales
[USA]	—
[USA].[CA]	74,748
[USA].[OR]	67,659

Specifying the rollup policy

The rollupPolicy attribute sits on <HierarchyGrant>. You can apply different policies to different hierarchies in the same role:

YAML

- name: "South Pacific manager"
  schema_grant:
    access: "none"
    cubes:
    - cube: "Sales"
      access: "all"
      hierarchies:
      - hierarchy: "[Store]"
        access: "custom"
        top_level: "[Store].[Store Country]"
        rollup_policy: "partial"
        members:
        - member: "[Store].[USA].[CA]"
          access: "all"
        - member: "[Store].[USA].[CA].[Los Angeles]"
          access: "none"
      - hierarchy: "[Customers]"
        access: "custom"
        top_level: "[Customers].[State Province]"
        bottom_level: "[Customers].[City]"
        rollup_policy: "full"
        members:
        - member: "[Customers].[USA].[CA]"
          access: "all"
        - member: "[Customers].[USA].[CA].[Los Angeles]"
          access: "none"
      - hierarchy: "[Gender]"
        access: "none"

XML

<Role name="South Pacific manager">
  <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>

---

Union roles

A union role combines the privileges of two or more existing roles. The result has the least restrictive access across all its constituent roles: the union role can see any schema object that at least one constituent role can see, and its rollup policy for a given hierarchy is the least restrictive policy among all constituents.

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

The constituent roles must be declared earlier in the schema file. They can be regular roles, other union roles, or custom class-backed roles. The “Coastal manager” union role can see any member accessible to either constituent, and can also see cells at the intersection of members only one role can see.

Caution

Union roles (<Union> / <RoleUsage>) have no YAML equivalent yet — the YAML schema converter does not round-trip them, so this construct is shown in XML only. Author union roles directly in XML, or compose the equivalent grants into a single role in YAML.

---

Setting a connection’s role

A role takes effect when it is associated with a connection. By default, connections have an unrestricted role that can see every cube in the schema. There are two ways to activate a specific role:

In the connect string

Include the Role keyword in the Mondrian connection string. Saiku Cloud sets this on behalf of your users based on your tenant configuration:

Provider=mondrian; Role="California manager";
Jdbc=jdbc:postgresql://host/db; JdbcUser=user; JdbcPassword=pw;
Catalog=/WEB-INF/MySchema.mondrian.xml

To activate multiple roles at once (creating an ad-hoc union), separate the names with commas. If a role name itself contains a comma, escape it with a double comma.

Programmatically

If you are embedding Mondrian directly, call Connection.setRole(Role) after opening a connection, or look up a named role with Schema.lookupRole(String).

---

Default role

You can mark one role as the default in your schema using the defaultRole attribute on <Schema>. Any connection that does not explicitly specify a role will use this default:

<Schema name="FoodMart" defaultRole="California manager">
  ...
</Schema>

---

Roles in YAML schemas

If you author your schema in YAML format, roles are declared under the top-level roles key. Each entry maps to a <Role> element. See the YAML schema reference for the full roles syntax, including schema_grant, cubes, hierarchies, members, rollup_policy, top_level, and bottom_level keys.

A quick example:

roles:
  - name: "California manager"
    schema_grant:
      access: "none"
      cubes:
        - cube: "Sales"
          access: "all"
          hierarchies:
            - hierarchy: "[Store].[Stores]"
              access: "custom"
              top_level: "[Store].[Stores].[Store Country]"
              members:
                - member: "[Store].[Stores].[USA].[CA]"
                  access: "all"
                - member: "[Store].[Stores].[USA].[CA].[Los Angeles]"
                  access: "none"

  - name: "No HR Cube"
    schema_grant:
      access: "all"
      cubes:
        - cube: "HR"
          access: "none"

Tip

Roles are emitted at the end of the generated XML so that all cube references are guaranteed to resolve correctly. If you hand-edit the XML, place <Role> elements after the last <Cube>.

---

Adapted from the Mondrian project schema guide (EPL v1.0).