Setting up a Multi-Tenant System

In this tutorial we discuss how to set up a multi-tenant system in ReportServer.

ReportServer is built upon a very flexible permission scheme that allows to easily handle even the most complext requirements. For this tutorial we are going to consider the following scenario: A software vendor SV provides a set of services and wants to use ReportServer to allow its clients to access their data. We will demonstrate how to set everything up, so that the four clients A, B, C, and D get access. The data is structured such that every table in the datawarehouse is extended by an attribute CustomerID which specifies for which client the data record is.

The design goals of our setup are twofold:

  1. Each client should get access to the system and to their data (and only their data).
  2. We would like to reduce maintenance and administration overhead on the IT team of our software vendor SV as much as possible. In particular, reports should be generated only once and then be used for all clients. That is, the reports should not be duplicated for each client, but ReportServer should ensure that when client A is running a report that only the data for client A is included.

In order to set up the system we are going to use ReportServer's hierarchical model of users and ReportServer's user variable concept. In the following we discuss how to structure users in order to simulate separate sub systems for each client. We then discuss how to use user variables within reports to allow the reuse of reports across clients. Finally, we will discuss some more advanced use cases.

The Basic Setup

We start with a fresh and empty system. (For instructions on how to install ReportServer see the ReportServer Configuration Guide as well as the best practice tutorials for installing ReportServer on Windows and Linux. Or simply use one of the prepackaged installers.)

With a fresh setup, the user management module should contain two folders (or Organizational Units) called Administration and DEFAULT_ROLES, a single user and two groups (see the screenshot on the right). We assume a basic working knowledge of ReportServer's permission and user management system. For an introduction to ReportServer's permission management see the tutorial Getting Started with Permissions as well as the Administration Guide.

The idea with a multi-tenant system is to create an organizational unit per tenant. That is, we would add an organizational unit called Clients to the root folder and then beneath that one unit for each of the clients. Thus, the basic structure could be as follows

root
+-Administration
+-Clients
  +-A
    +-Alice
    +-Andreas
  +-B
    +-Bob
    +-Bill
  +-C
    +-Charly
    +-Chris
  +-D
    +-Dora
    +-Dean
+-DEFAULT_ROLES

Each of the users should be granted the standard user permissions, that is, the user should be allowed to log in to the system, see the Dashboard and TeamSpace module, etc. For this, we are going to use the default role Users which grants these permissions. As the default user role provides a bit too many permissions, we will later need to adapt it, but for getting started, we will add the Clients OU (short for organizational unit) as a member to the Users group (see screenshot on the right).

Adding an OU to a group is equivalent to adding each and every user that is in the OU to the group. Thus, by adding the Clients OU to the Users group we have added all the users, Alice, Andrease, ..., Dora, and Dean, to the group. Also, if at any later point new users (or clients) are added, they directly get the correct permissions.

To test the current configuration we can login as one of the users. For this, we can use the switch user feature. By pressing CTRL+ALT+L (and if the current user has the necessary permissions) we can login as a different user and confirm that, say, Alice can login and see the modules Dashboard, TeamSpace and Scheduler. Once we log out, we will again be logged in with our actual user.

Let us quickly recap, what we have done so far. We have created an organizational unit for each of the clients, we have added the users for each client and we have granted permissions for all clients to access the system. In the next step we are going to set up a basic report and provide access to the report to all clients.

Adding Baisc Reports

In the following we will create a report and provide access to the report to all clients. The report will be a Dynamic List on top of a database table called ClientData which has the following structure:

CREATE TABLE `ClientData` (
  `id` mediumint(8) unsigned NOT NULL auto_increment,
  `City` varchar(255),
  `Name` varchar(255) default NULL,
  `Company` varchar(255),
  `SomeDate` varchar(255),
  `SomeNumber` varchar(13) default NULL,
  `ClientID` varchar(255) default NULL,
  PRIMARY KEY (`id`)
) AUTO_INCREMENT=1;

The above SQL is for MySQL. The demodata was generated via http://www.generatedata.com/. The database script to load the demodata used in this tutorial can be downloaded from

Before we can create a report, we need to create a datasource pointing to the demodata. In our case, we create a new relational database datasource which we name Client Data Datasource. For more information about how to set up datasources in ReportServer see the Administration Guide.

Assuming the database is correctly set up, we can now create our first report. For this, we go to the report administration module, create a folder with the name Common Reports (which will contain reports that can be accessed by all the clients) and within that we create a new Dynamic List called DemoReport. As datasource we select the Client Data Datasource and as query we enter

SELECT * FROM ClientData

In order to confirm, that the report is working properly, we run the report by double clicking it, selecting all available attributes and checking the preview, which should give the following picture (note the ClientID column which contains the information about to which client each record belongs).

Now that we have a report, let's provide access to the report to all of our clients. For this, we are going to create a TeamSpace for each of the clients. Of course, we could also create multiple teamspaces for each client but for this tutorial we go with one TeamSpace per client. To do this, we switch to the TeamSpace module and create the four TeamSpaces called

  • Client A
  • Client B
  • Client C
  • Client D

In each of the TeamSpaces we add the corresponding users to become members and import the report that we've created in the previous step.

Note that there are various ways to structure TeamSpaces. For example, if the clients should only get read access, one could add them to their TeamSpace with the guest role. If, on the other hand they should be able to make changes then the role of user would be more appropriate. (For an introduction to TeamSpace roles see our User Guide.) Both settings, however, require that when a new user is added to the system an administrator needs to place the user into the correct TeamSpace. By giving one ore more users in a TeamSpace the role of Manager you can pass this administrative task to the client. Finally, if you are running ReportServer Enterprise then the whole set up and maintenance of TeamSpaces can be automated via scripts. We will get to this during the discussion of advanced use-cases.

To once again test that everything is set up correctly, we can switch to one of the users, say Alice, and confirm that she can access the TeamSpace of Client A and that she can there access the Demo Report.

What we have done until now, is that we have created TeamSpaces for each client and added the single report to each of the TeamSpaces. The latter step is of course problematic since now every client can see the data of everyone. We could mitigate this problem by creating four reports instead of one each restricted to the data of one client and then giving each client only the permission to access their report. This, howveer, creates lots of additional maintenance work which we would like to avoid and in the following section we will explain how we can restrict the view within a single report such that it only shows the data the current user is allowed to see.

Reporting with User Variables

Our Demo Report was set up with the simple SQL query

SELECT * FROM ClientData

Using parameters, we could, for example, add a dropdown box to the report which allows to select a client and then only show the data for that particular client. Assuming that we name the parameter P_CLIENT the adapted query could be:

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT}

Of course, adding a selection box to choose which client's data one wants to see does not solve the problem that in the current setup client A can see also the data of client B. The idea that we will use to restrict the access of each client will, however, use a similar approach. We will use a parameter to restrict the data records returned by the query, but we will not allow the user to select the parameter. Instead the parameter's value is automatically assigned depending on which user is currently logged in. The concept that makes this work are ReportServer's User Variables.

A user variable can be considered a property that is put on each user and that can be used by report designers when creating reports. In our case, we are going to use a very simple user variable which for each user will assign the client the user belongs to. That is, for Alice and Andreas the variable will be set to A, for Bob and Bill it will be B and so forth.

User variables are defined in the user management view at the root node. Once you select User root you should see a tab User variable management. This allows us to define what user variables are available. Currently ReportServer supports two types of user variables:

Text variables
Have a single value for each user
List
Allows to define a list of values for each user

In our example, we want to store for each user which client he or she belongs to. We thus create a text user variable with the name client. What we need to do next is to actually set the value for each user.

Naturally, explicitly setting the value for each and every user in the system would be a tedious endeavour. Thus, to make things easier, ReportServer allows you to use the hierarchical structure of the user tree to define variables. That is, you can not only specify the variable's value directly per user but also on an organizational unit. In this case ReportServer scans through all the user's parent OUs to find the value to use. The first one it finds, will be the one taken. This allows you to, for example, specify a default value on the root node and then overwrite this value for certain users or OUs deeper in the user tree.

To assign the users of each of the clients the correct value we set the user variable at each of the client OUs. That is, we select the OU for client A and then the tab User variables. There we select the previously created user variable client and set its value to A. We do the same for each of the other clients.

Now that we have the user variable defined, we need to adapt our report to use it. For this, we go to the Demo Report and open the parameters section. Add a parameter of type User Variable, name it Client ID with key P_CLIENT_ID and then in the specific properties of the parameter choose the previously created user variable client. You might also want to set the parameter to be hidden, as otherwise the user will see a parameter page with the value of the user variable. As this is the only parameter for the report and it is static (i.e., the user cannot change it) it does not make much sense to show it to the user. In other scenarios it might of course be useful to show the users what restriction is in place.

Now we are almost done, all that is left to do is to adapt the report's query to

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID}

Let's test it. If we now log in with Alice and execute the report we expect to only see records for Client A.

If instead we log in with Bob's account, we only see records for Client B.

This concludes the basic set up of a multi-tenant system, so let us once more recapitulate the steps until now. For each client we created an organizational unit which holds all the users of that particular client. Additionally, we added a user variable and set it up so for each user it reflects to which client the user belongs. We have then employed this user variable in the definition of a report shared between all users so that each user can only see the data for the client they belong to.

In the following we will discuss a few more advanced topics. In particular, if you followed the above tutorial, then you might have noticed that the super user that you used to set up the entire system can no longer run the report. The reason being that it does not have the user variable set and thus ReportServer cannot execute the query

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID}

Before we get to this, let us first present a few warnings on the permission setup and TeamSpaces.

Careful with Permissions

In a multi-tenanted (and also in a single-tenant) setup one might be tempted to use TeamSpaces for granting or revoking access to reports. This is, however, not a secure approach. Permissions, that is, the right to access and execute a report is granted in the report management module. If a user is given the permission to access a report there, that user can access the report even in case he or she does not have access to any TeamSpace. All they would need to know is the report's id.

The default user role grants access to all reports in the system and is thus usually not well suited for a multi-tenant system where you often still have some reports which are specific to certain clients. It is thus adviseable to use a more granular permission scheme and, for example, have a report folder for each of the clients to store client specific reports. Permissions for these folders should then be granted only to users that belong to the corresponding client.

Similarly, the default user role grants read access to all users in the system, meaning, for example, that a user could select any other user in the system as a recipient of a scheduler job, or if a user is a TeamSpace manager he or she could add any other user to the TeamSpace.

Let us quickly show, how we could adapt the permissions in our example. The first step would be to remove the permissions that the default user role grants on users and reports. For this we go to the report root folder and there to the permissions tab and remove the Access Control Entry for Users.

Let us stress that the default user role is nothing static which is fixed in ReportServer but simply a pregenerated group that is meant to simplify setting up permissions. You can (and should) adapt it to you own requirements.

Since we want all clients to be able to access the Common Reports folder we select it, and switch to the permissions tab. We add a new Access Control Entry and select the Organization Unit Clients as folk. Note that, as folk (the recipient of the permission) we can select either individual users, groups, or organizational units. As permissions we grant the read and execute permission.

Next we swith to the user management module and also remove the permissions that are granted to the default user role. Again we go to the root node (in this case User Root) and there to the permission tab to remove the Access Control Entry for Users. We then go to each client, that is, client A, B, C and D and there add an Access Control Entry granting users of the current client to read all other users of the same client.

Advanced Use Cases

After this short introduction there are two more advanced topics we would like to discuss. First we want to show the necessary steps to setup a user that sees the data for more than one client. For example, this could be an employee of our software vendor who is assigned to two clients and should also be able to see their data. The second point we want to briefly touch is how to use ReportServer scripts to perform some automated maintenance.

Access to Multiple Clients

As we've mentioned earlier with our set up only users that have the CLIENT_ID variable set will be able to run the Demo Report. In particular the root user would no longer be able to run the report. We could of course, provide a simple solution to this. We assign a default value, say DEFAULT to the user variable (that is we set a value at the Root node). Then we adapt the report's query to look somewhat like

SELECT * FROM ClientData WHERE CLientID = ${P_CLIENT_ID} OR  ${P_CLIENT_ID} = 'DEFAULT'

While this solves the problem for administrators that should be able to see the report in its entirety, it is not clear how to let a particular user see the data for two clients, say A and B. One approach to solve this is to use a user variable of type list instead of a simple text variable. We can then set the variable to 'A' for users within client A, but for our special user we could set it to 'A|B'. (Here note that to enter multiple values in the list variable the values are separated by athe | (pipe) symbol.) When working with a list in the report's query we would then use the $X expression (see the Using Parameters from the administration guide)

SELECT * FROM ClientData WHERE $X{IN,ClientID,P_CLIENT_LIST}

Using lists and the $X expression has an interesting side effect. If the list is empty, then by convention the expression returns true. Thus, one could define the default value for the user variable to be the empty list in which case users which are not restricted would see all the data.

Note that $X can only be used in Dynamic Lists and Jasper reports.

Automating ReportServer

In the final part of this tutorial we want to present a glimpse into what is possible using ReportServer scripts. What we would like to do is to automate the generation of TeamSpaces. That is, we want to create a script, that generates a TeamSpace for each client (unless the TeamSpace already exists) and synchronizes the users of that particular TeamSpace with all the users of the client. Of course, this could be extended by also checking reports which are in the TeamSpace etc. The possibilities are endless.

Following is the script. The explanation follows

import net.datenwerke.security.service.usermanager.UserManagerService
import net.datenwerke.rs.teamspace.service.teamspace.TeamSpaceService

import net.datenwerke.security.service.usermanager.entities.User
import net.datenwerke.rs.teamspace.service.teamspace.entities.TeamSpaceMember
import net.datenwerke.rs.teamspace.service.teamspace.entities.TeamSpaceRole

/* the client folder */
def clientOuID = 3484l; // note the trailing l to indicate that the number is a long

/* load services */
def teamSpaceService = GLOBALS.getInstance(TeamSpaceService.class);
def userService = GLOBALS.getInstance(UserManagerService.class);

/* load root user -> this user will be the owner of all teamspaces */
def owner = userService.getUserByName("root");

/* get client OU */
def clientOu = userService.getNodeById(clientOuID);

/* for each of the children, i.e., each client */
clientOu.getChildren().each { client -> 
  def name = client.getName();
  
  /* try to load TeamSpace */
  def ts = teamSpaceService.getTeamSpaceByName("Client " + name);
  
  /* create teamspace if it does not exist */
  if(null == ts){
    /* create teamspace with owner as owner */
    ts = teamSpaceService.createTeamSpace(owner);
    
    /* set name and description */
    ts.setName("Client " + name);
    ts.setDescription("Users of Client " + name);    
  }
  
  /* synchronize users -> remove all users from teamspace then add all users of client */
  ts.setMembers(null);
  
  /* add users */
  client.getDescendants().each{
    if(it instanceof User){
      // add user as teamspace member
      def member = new TeamSpaceMember(it);
      member.setRole(TeamSpaceRole.USER);
      ts.addMember(member);
    }
  }
}

return "done synchronizing";

In order to work with users, we will use the UserManagerService and for TeamSpaces we use the TeamSpaceService. Additionally, we need to import a few entity objects that we need to create (User, TeamSpaceMember and TeamSpaceRole). First we define the base OU, that is, the Clients folder in the user manager tree. We could load the OU by name but since that is not necessarily unique we opted to store its id. Then in lines 12 and 13 we load the two services that we are going to use in the script. To complete the script's setup we load the root user in line 16 and the actual OU object in line 19. Since each TeamSpace has an owner we need to assign this to some user and in the above script we will use the root user for this purpose.

The meat of the script starts in 22 when we loop over all children of the Clients folder. As each child represents a client we will create a TeamSpace for each of the children. The first step, in line 26 is to check whether the TeamSpace already exists. If this is not the case, we need to create it (lines 30 to 37). In the next step we remove all the members from the current team space (line 40). Finally, we loop over all the user objects beneath the current client folder and make them a member in the TeamSpace.

Admittedly, this was a rather quick explanation but we hope that this gives you some idea of what can be done with scripting. To get a better understanding of how scripting works, have a look at the scripting chapter in the administration guide as well as the ReportServer Scripting Guide.

This script could now be used by an administrator to update the current TeamSpace configuration, if users or clients changed, or it could even be scheduled and run on a regular basis, say once every morning.

This concludes this tutorial on multi tenant scenarios with ReportServer. As always we are happy about feedback.

Happy Reporting