Try Pentaho Data Integration and Analytics

Analyzer data multi-tenancy

The two primary ways that multi-tenancy is applied for analysis reports include Dynamic Schema Processor and Delegate Roles. This section will describe when and how to use each.

Dynamic schema processor

A Dynamic Schema Processor (DSP) is a special class that can intercept the schema when it is loaded. Since the class has access to the user session information, it can modify the schema based on details about the user, such as user ID, roles, or other session attributes set when the user logged in, such as tenant ID.

A common implementation approach with Mondrian 3.x is as follows:

  1. Modify the Mondrian schema to include SQL statements that will limit the data returned from the dimension and fact tables.

    SQL statements on tables will be added to the WHERE clause when the SQL query is generated by Mondrian.

    . . .
<Table name="my_facts">
  <SQL dialect="generic">my_column = %my_tenant_id%</SQL>
</Table>
. . .

  2. Populate the user’s session to include the variable value, such as my_tenant_id = 12.

    This step is usually completed at session startup by creating a session startup action or startup rule. It can also be set via SSO by passing additional parameters which are set in the SSO filter.

  3. Create a DynamicSchemaProcessor class to modify the schema to replace the variable with the value from the user’s session.

    This class should implement the mondrian.spi.DynamicSchemaProcessor interface. It is common to extend the FilterDynamicSchemaProcessor or the LocalizingDynamicSchemaProcessor.

  4. Publish the Mondrian schema on the Pentaho Server.

  5. Configure each desired Pentaho Analyzer data source to use the DSP by adding parameters to the data source properties:

    • UseContentChecksum=true

    • DynamicSchemaProcessor=org.myorganization.MyDynamicSchemaProcessor Please note that DSPs typically involve a separate cache to be maintained for each tenant, which can cause performance concerns. Therefore, DSPs are generally recommended where multiple users share the generated schema. For cases where individuals each have his or her own rules within the schema, the delegate role technique is recommended.

    To maintain internationalization functionality, the LocalizingDynamicSchema processor is typically extended and the filter method overridden. The following listing shows a simple DynamicSchemaProcessor which replaces all of the tenant-ID variables in the schema with the tenant-ID of the user:

See the following references for further details:

Delegate roles

Delegate roles allow the data to be restricted using roles at run time. You can implement individual roles for each user in the schema, but this can be unmanageable for thousands of users, each with a unique role. In the scenario where many users will have fine-grained control, delegate roles solve the problem with a few classes.

The standard approach is as follows.

  1. Populate the user session with information which can be used to restrict the data.

  2. Add a role with data restrictions to some members of the hierarchy to be controlled.

  3. Configure a role mapper in pentahoObjects.spring.xml. A one-to-one mapping is usually recommended. Without this configuration, roles are not enforced.

  4. Create a custom role object.

  5. Create a custom connection object which will set the new delegate role.

  6. Change the configuration to use your custom connection and role objects. Open pentahoObjects.spring.xml in an editor, and modify the connection-MDX bean.

Populate the user session

The typical approach is to populate the session with the specific data members which a user will be able to see. For example, if a sales manager is restricted to the northwestern United States, then that manager might have “ID,” “WA,” and “OR” in a session variable. This data will be used by the delegate role described to restrict access.

Add a role to the schema

To restrict on a role, Mondrian will need to have a role defined. A common approach is as follows.

  1. Create the Authenticated role or some other role that all users of the schema will have. However, you can use any role which the user might have.

  2. Within the role, a MemberGrant should be defined.

    The following listing shows a role for Sales Managers. In this case the Sales Manager will only have access to facts associated with the state of WA (Washington).

    <Role name="Sales Manager">
  <SchemaGrant access="none">
    <CubeGrant cube="Product Sales" access="all">
      <HierarchyGrant hierarchy="[Location]" access="custom">
        <MemberGrant member="[Location].[Country].[USA]" access="none"/>
        <MemberGrant member="[Location].[State].[WA] access="all"/>
      </HierarchyGrant>
  </CubeGrant>
  </SchemaGrant>
</Role>

There are a couple of important considerations.

  • A member grant must exist. If there is no member grant on a hierarchy, Mondrian will not check to see if the user has access.

  • The member must exist in the data. For example, the member could not be [Location].[State].[NoWhere] unless NoWhere is a legitimate member. It is an option to have fake members in the dimension table with no facts and use that as the default.

  • If a dimension needs to be restricted, you must restrict it separately. Restricting a hierarchy does not restrict other hierarchies, so if there is not a measure or restricted member, Analyzer will show you all members of the dimension.

Configure the role mapper

A role mapper tells Mondrian how to map from a Pentaho role to a role defined in the Mondrian schema. By default, Pentaho does not have the role mapper defined. If there is no role mapper, then Mondrian will not apply any security access and all users will have access to all data.

To configure the role mapper, modify pentahoObjects.spring.xml in the pentaho-solutions/system folder. There are several options with descriptions already defined in the configuration file. The most common is to use the MondrianOneToOneUserRoleListMapper. This mapper will map directly from a Pentaho role to a Mondrian role.

Create a custom connection

You will need a custom connection to set the delegate role mapper. This is because only the connection can be configured in Pentaho. The custom class will typically extend the default MDX connection and extend the init() method. The following example shows how to set the delegating role for Authenticated:

public class CustomMDXOlap4jConnection extends MDXOlap4jConnection {

  public boolean connect( Properties props ) {
    boolean result = super.connect( props );
    if ( result && this.connection != null ) {
      try {
        RolapConnection rolapConnection = this.connection.unwrap( RolapConnection.class );
        Role delegateRole = rolapConnection.getSchema().lookupRole( "Authenticated" );

        if ( delegateRole != null ) {
          CustomSessionAssistedRole customRole = new CustomSessionAssistedRole( delegateRole );
          rolapConnection.setRole( customRole );
        }
      } catch ( Exception e ) {
        throw new RuntimeException( e );
      }
    }

    return result;
  }
}

To configure the custom connection, modify pentahoObjects.spring.xml and set the value of the connection-MDX bean.

<bean id="connection-MDXOlap4j" class="org.myorganization.CustomMDXOlap4jConnection" scope="prototype">
  <property name="useExtendedColumnNames" value="true" />
</bean>

Reference

MDXOlap4jConnection JavaDoc: https://javadoc.pentaho.com/bi-platform102/pentaho-platform-extensions-10.2.0.0-218-javadoc/org/pentaho/platform/plugin/services/connections/mondrian/MDXOlap4jConnection.html

Create a delegating role class

The custom delegating role will be called whenever access needs to be determined, such as if a user is attempting to see data about a particular state. You must extend the DelegatingRole class and override the getAccess() methods, depending on which one is to be checked (for example, Hierarchy or Member). Only the logic for determining access to a member is shown below. Note that a complete class will have a number of other methods:

public Access getAccess(Member member) {
  // state previously defined by reading from PentahoSession
  if (null == state) {
    return Access.NONE;  // prevent access to everything if no session variable defined
  }
  // Only check the Location dimension.
  if (member.getHierarchy().getName().contains("Location")) {
    // Check ancestors as well for restriction.
    List<Member> members = member.getAncestorMembers();
    for (Member mem : members) {
      if (state.equalsIgnoreCase(mem.getName())) {
        return Access.ALL;
      }
    }
    // Check the member itself; is this the state that's allowed?
    if (state.equalsIgnoreCase(member.getName())) {
      return Access.ALL;
    }
    return Access.NONE;
  }
  return role.getAccess(member);
}

@Override
public Role.HierarchyAccess getAccessDetails(Hierarchy hierarchy) {
  Role.HierarchyAccess ha = super.getAccessDetails(hierarchy);
  return (ha == null ? null : new CustomHierarchyAccess(ha));
}

@Override
public boolean canAccess(OlapElement olapElement) {
  Util.assertPrecondition(olapElement != null, "olapElement != null");
  if (olapElement instanceof Member) {
    return getAccess((Member) olapElement) != Access.NONE;
  } else {
    return super.canAccess(olapElement);
  }
}

protected class CustomHierarchyAccess extends RoleImpl.DelegatingHierarchyAccess {
  public CustomHierarchyAccess(Role.HierarchyAccess ha) {
    super(ha);
  }
  public Access getAccess( Member member) {
    return CustomDelegateRole.this.getAccess(member);
  }
}

Note: If you are using a custom role class, you must override these methods: DelegatingRole.getAccess(), DelegatingRole.getAccessDetails(), and DelegatingRole.canAccess().

Reference

DelegatingRole JavaDoc: https://javadoc.pentaho.com/mondrian102/mondrian/olap/Role.html

PreviousPentaho metadata securityNextSolution multi-tenancy

Last updated

Was this helpful?