Multi-Tenancy

Multi-Tenancy in Pentaho

Pentaho has three categories of multi-tenancy:

• Data multi-tenancy allows developers and integrators to apply custom security and business rules to control access to data.
• Content multi-tenancy separates content, such as reports and folders, among tenants.
• UI multi-tenancy presents different styles of the user interface for each tenant.

There are two required components to make multi-tenancy work.  Users need to be associated with tenants via roles, tenant IDs, or other identifiers which indicate what content and data users see.  Similarly, there must be something in the data that can be used to restrict access.  The combination of user information and data make the multi-tenancy approaches described here possible.  Since these approaches are data model and data-driven, they are very flexible.

Sharding Data Multi-Tenancy

Sharding involves separating data by database or by database connection properties. The Pentaho Business Analytics platform manages access to database connections through a central mechanism called a data source. The platform delegates to the IDBDatasource object whenever a connection is requested to a given data source. Through Java code and Pentaho configuration files, developers can plug in their own object to apply rules at run time as shown in the following example.

IDBDatasourceService Implementation Example

An IDBDatasourceService implementation uses two methods to modify the data source name: getDataSource() and clearDataSource(). The following code sample adds a tenant ID to the data source name. Adding the tenant ID allows the system to publish report definitions with a common data source name. At run time, the platform will use the dynamically created data source name.
 

public class MyTenantedDatasourceService extends PooledOrJndiDatasourceService {
    @Override
    public DataSource getDataSource(String dsName) throws DBDatasourceServiceException {
        return super.getDataSource(modifyDsNameForTenancy(dsName));
    }
    @Override
    public void clearDataSource(String dsName) {
        super.clearDataSource(modifyDsNameForTenancy(dsName));
    }
    private String modifyDsNameForTenancy(String dsName){
        logger.debug("Original DSName is "+dsName);
        IPentahoSession session = PentahoSessionHolder.getSession();
        if (session == null) {
            logger.warn("Session is null; no modifications made to the JNDI dsName.");
            return dsName;
        }
        String tenantId = (String)session.getAttribute("tenantId");
        if (StringUtils.isEmpty(tenantId)){
            logger.warn("ID not found; no modifications made to the JNDI dsName.");
            return dsName;
        }
        String dsname = tenantId.concat("_").concat(dsName);
        logger.debug("New DSName is "+dsname);
        return dsname;
    }
}

Configuring IDBDatasourceService

You must configure the class you want to use.

1. Place your compiled class or jar file in the webapps/pentaho/WEB-INF/lib folder.
2. Modify pentahoObjects.spring.xml to point to your class name. For example:

   <bean id="IDBDatasourceService" class="org.myorganization.MyTenantedDatasourceService" scope="singleton"/>

3. Restart the server for the configuration change to take effect.

Using the Data Source

The following steps describe how the data source in this example is used at run time.

1. A report is published to Pentaho which refers to a JNDI data source called 'MyDataSource'.
2. A user with the tenant ID of 'CompanyA' runs a report.
3. The IDBDatasourceService appends the tenant ID to the data source name, resulting in 'MyDataSource_CompanyA', and returns the connection.
4. The report is run using data from the 'MyDataSource_CompanyA' connection. 

The IDBDatasourceService interface can be used any time a connection uses either JNDI or Pentaho database-pooled connections. This approach works for reports generated with Report Designer, Analyzer, dashboards, data model-based reports, and action sequences using named data sources. Note that the IDBDatasource is implemented on the Pentaho Server and is not directly available to BA design tools, such as Report Designer and Schema Workbench.

If you are using only the IDBDatasourceService for multi-tenancy and also using Pentaho Analysis, it is recommended that you have a DSP (see below) which will modify the Analysis schema in a benign way, such as modifying the description. This is because the schema is used to determine caching.  If only the IDBDatasourceService is used, it is possible for data from one tenant to be exposed to another tenant.

Striping and Data Model Multi-Tenancy

Striping involves filtering tenant data within a single database connection. To allow a user to interactively query or navigate this data, access rules must exist between the user actions and the data. Data models provide a place to embed these access rules. Pentaho Business Analytics natively supports two types of data models: Pentaho Metadata and Analysis schemas.

• Pentaho Metadata allows you to define business abstractions of relational database models. The Pentaho Metadata Editor (PME) and the Data Source Wizard are used to build and set up data models. Pentaho Metadata is used by Pentaho Interactive Reporting report files (.prpti) and can be used by Pentaho Report Designer report files (.prpt), dashboards, Pentaho Data Integration, and CTools dashboards.
• Analysis schemas define multi-dimensional data models. These schemas are created by Pentaho Schema Workbench, Data Source Wizard, and other parts of Pentaho software. Analysis schemas can be used by Analyzer, Pentaho Report Designer reports (.prpt) and CTools dashboards. 

SQL Generator

The SQL Generator is a special class that is called when Pentaho Metadata queries the database. There are two methods that can be overwritten: preprocessQueryModel() and processGeneratedSql().

• The preprocessQueryModel() method is the recommended method to overwrite. It is called before the SQL is generated. To overwrite, add an "AND" condition to the query to restrict data.
• The second method, processGeneratedSql(), is called after the SQL is generated. While it is possible to modify this query, it would involve parsing and modifying the string query.

The following example shows the preprocessQueryModel() for a class that extends the SqlGenerator class. The first loop gathers all of the columns to see which ones need to be constrained to avoid duplicate checks. The second loop adds a “WHERE” clause to the query. Only “AND” conditions can be used. Because you cannot add parentheses, using "AND" conditions can cause problems if the model already has an “OR” condition in the “WHERE” clause.

@Override
protected void preprocessQueryModel(SQLQueryModel query, List<Selection> selections, Map<LogicalTable, String> tableAliases, DatabaseMeta databaseMeta) {
    Set<LogicalTable> selectedLogicalTables = new HashSet<LogicalTable>();
    // Get the users productline from the session
    IPentahoSession session = PentahoSessionHolder.getSession();
    String productline = (String)session.getAttribute("productline");
    //Object territory = "NA" ;
    // Figure out and gather up the selected logical tables. We need to
    // know if the CUSTOMER_W_TER table is included in the
    // query...
    if(selections!=null && !selections.isEmpty()) {
        for(Selection selection:selections) {
            LogicalColumn column = selection.getLogicalColumn();
            LogicalTable table = column.getLogicalTable();
            selectedLogicalTables.add(table);
        }
    }
    // We now find the column to constrain, and add our where
    // clause, using a dialect specific where clause...
    for(LogicalTable table:selectedLogicalTables) {
        List<LogicalColumn> logicalColumns=table.getLogicalColumns();
        for(LogicalColumn logicalColumn:logicalColumns) {
            if(logicalColumn.getId().equalsIgnoreCase("BC_PRODUCTS_PRODUCTLINE")) {
                String tableAlias = tableAliases.get(table);
                String columnName = (String) logicalColumn.getPhysicalColumn().
                getProperty("target_column");
                query.addWhereFormula(tableAlias + "." +
                columnName + " = '" + productline + "', AND");
            }
        }
    }
}

Once the code has been compiled and placed in a .jar file, it should be deployed to webapps/pentaho/WEB-INF/lib. Then two files need to be modified:

• pentaho-solutions/system/pentahoObjects.spring.xml should be modified to change the class defined for the sqlGenerator bean. The following shows an example of the bean mapping:

  <bean id="sqlGenerator" class="org.myorganization.MySqlGenerator" scope="prototype"/>

• webapps/pentaho/WEB-INF/classes/classic-engine.properties should be modified by defining a new parameter:

  org.pentaho.reporting.engine.classic.extensions.datasources.pmd.SqlGeneratorClass=org.myorganization.MySqlGenerator
  

One way to debug SQLGenerator functionality is to view the generated SQL statements. A common method is to use logging statements within processGeneratedSql and increase the log level of SQLGenerator in log4j.xml file under webapps/pentaho/WEB-INF/classes.

  <category name="org.pentaho.metadata.query.impl.sql.SqlGenerator">
    <priority value="DEBUG"/>
  </category>
  <category name="org.myorganization.MySqlGenerator">
    <priority value="DEBUG"/>
  </category>

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. For example:

   . . .
   <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 Analysis 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:

public class TenantedDynamicSchemaProcessor extends LocalizingDynamicSchemaProcessor
implements mondrian.spi.DynamicSchemaProcessor { 

  public String filter(String schemaUrl, Util.PropertyList connectInfo, InputStream stream) throws Exception {
    String schema = super.filter(schemaUrl, connectInfo, stream);
    IPentahoSession session = PentahoSessionHolder.getSession();
    schema = schema.replaceAll("%my_id%", session.getName());
    return schema;
  }

}

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.

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;
  }
}

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

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);
  }
}

Roles and Access Control Lists (ACLs)

Pentaho uses roles and user IDs to enforce authorization to content in the solution repository. For multi-tenancy, roles are typically used instead of user IDs. A role for a user should be thought of as a piece of metadata about the user and is simply a group which the user belongs to. User roles are set as part of the authentication and identification process when a user logs in. For example, a user might be in the 'West and Sales Managers group'.

Access Control Lists (ACLs) are used to define which users or roles have access to content in the solution repository. As with roles, ACLs should be thought of as metadata about the repository content. An ACL might be that Sales Managers can read and edit content in the Sales Managers folder, but not delete it.

When a user attempts to perform a task with content, an ACL voter will determine if the user can perform the action. The voter compares the ACL against the user’s ID and roles to determine the user's access permissions. For example, if Sam is assigned the role of 'Sales Manager' and the Sales Manager role can read files in the Sales Managers folder, then Sam can see the content in that folder. However, Sam is not assigned the HR role, and since the HR folder is only viewable by users assigned the HR role, Sam cannot see the content in the HR folder.

For more information on roles and ACLs, see Use Pentaho Security.

This feature can be used for multi-tenancy as well. Each user is assigned a role which associates that user with a tenant-specific folder which only that user can see. There also may be folders which are viewable by all tenants. When a user logs on to the system, that user can only see shared folders and the folders for their tenant.

The approach described works for many cases, but does not accommodate complex access rules. For this reason, a better approach is often to use custom access voters. The next sections will describe how to create custom voters and how to manage ACLs without using the User Console.

Multi-Tenancy at the Content Level

You can use the ACL system to manage access to content by tenant. If content needs to be restricted by more complex rules such as by tenant and then by role within the tenant, you can create a custom access voter by implementing IRepositoryAccessVoter. The only method that needs to be implemented is the hasAccess() method.

Custom Voters

Pentaho, through the Spring-based bean injection, allows you to apply a different AccessVoter, which can apply custom logic to determine access for users. For example, you may have common reports that all users can access based on roles, as well as solution folders for specific tenants or business units. The ACL can allow a user to see all the common reports, but only the tenant folders for which that user has access. Additional fine-grained access control rules can be added as needed. Therefore, it is possible to have more than one AccessVoter and to have them chained. All instances of AccessVoters are managed by AccessVoterManager. When implementing a new AccessVoter, consider the following:

• All users with the role of 'Administrator' will bypass the AccessVoter logic.
• AccessVoters are chained, and their order is specified in the repository.spring.xml for the repositoryAccessVoterManager bean:

  <bean class="org.pentaho.platform.repository2.unified.RepositoryAccessVoterManager" id="repositoryAccessVoterManager">
    <constructor-arg ref="tenantedAccessVoter/>
    <constructor-arg ref="authorizationPolicy"/>
    <constructor-arg ref="repositoryAdminUsername"/>
  </bean>

• A user will have access to a resource if all AccessVoters in the chain allow access. The first AccessVoter in the chain that rejects access will break the chain, and the user will not have access to the resource.

To implement a new AccessVoter, you need to write a java class which implements IRepositoryAccessVoter. This interface is for all resources stored in Pentaho’s repository. The only method that needs to be implemented is the hasAccess() method, which is called for each object the user tries to access. In the following listing, the tenantid is retrieved from the session and then compared to the folder name. All tenants will have an individualized directory under /public/tenants/${tenantid}. If the folder name matches the tenantid, the user is granted access; otherwise, access is denied.

  public boolean hasAccess(RepositoryFile file, RepositoryFilePermission operation,
                              RepositoryFileAcl acl, IPentahoSession session) {
     String tenantid = (String)session.getAttribute("tenantid");
     String [] topLevelDirs = getTopLevelDirNames(file);
     // a dir pattern name of /public/tenants/tenantid
     if ((topLevelDirs != null ) && (topLevelDirs.length > 3)) {  
        if ( "public".equals(topLevelDirs[1])) {
           if ("tenants".equals(topLevelDirs[2])) {
              return tenantid.equals(topLevelDirs[3]);
           }
        }
     }
     return true; // leave it up to the server level ACL
  }

  protected String [] getTopLevelDirNames(RepositoryFile f){
      String fullpath = f.getPath();
      if (fullpath != null){
          String [] dirs = fullpath.split("/");
          return dirs;
      } else {
          return null;
      }
  }

Managing ACLs via a Web Service

As a part of multi-tenant solution, there is often a need create and manage files and folders in an unattended fashion. Pentaho has SolutionRepositoryService with a RESTful interface that allows third party scripts and applications to create new folders as well as set ACLs. Detailed information on this service can be found in the Pentaho SDK package, which can be downloaded from the Pentaho support site.

Theming

When you integrate content for users, you may want to make it look like the application into which it is being integrated. Furthermore, in multi-tenanted environments, you may want a different theme for each tenant. How to create additional themes is covered in the Custom User Console Themes article. This section focuses on setting the theme for different tenants.