Reporting — Row-Level Security in Multi-User Applications with the DevExpress Web Report Designer (v21.1)

Reporting Team Blog
05 July 2021
Considering SaaS (Software-as-a-Service) for your app delivery model? If so, this post summarizes multi-tenant related enhancements introduced in DevExpress Reports v21.1.
A Brief Primer
In an SaaS model, a user is actually a tenant because a customer pays rent for application services. As you know, SaaS solutions are based on multi-tenant architecture wherein a single software instance serves multiple customers (tenants) and shares resources between tenants. The application, therefore, must enforce the isolation of tenant data.
Database Model for Multi-Tenant Architecture
Multi-Tenancy Scheme
A multi-tenant system is usually implemented at the database level. The three basic tenancy models for a multi-tenant system include:
  • Separate database for each tenant (single-tenancy): You can use a custom connection string provider to implement this model with DevExpress Reports.
  • Separate schema for each tenant in a shared database: You can use a custom database schema to implement this model with DevExpress Reports.
  • Shared schema in a shared database: This option does not require database and schema modifications, but it needs a tenant ID column in each data row to distinguish the data for a tenant in a table.
In our most recent release, we focused on the third option (tenant ID). Your feedback will help us shape future direction in this regard.
Row-Level Security within DevExpress Reports
DevExpress Reports allow users to create individual custom reports via our End-User Report Designer. The DevExpress End-User Report Designer ships with an internal Query Builder module that can create complex queries with various data source types. An end-user can construct an arbitrary SELECT query in the Query Builder and retrieve data to build a report as requirements dictate.
In a multi-user environment, it is important to isolate data between users so that each user can only obtain appropriate data. If a multi-tenancy solution uses a shared database and tenant IDs in its data tables, we can map the report user to the tenant ID to address row-level security. Each tenant retrieves its own data rows marked with a specific identifier. The data remains invisible to other tenants.
Implementation Details
With our latest release cycle (v21.1), you can implement a security filter service and modify each SELECT query executed within our Document Viewer, Report Designer’s Preview, and Query Builder component. The security filter adds a conditional clause to SELECT expressions and retrieves only the data that belongs to a specified tenant ID (mapped to the current report user).
As a result, data is filtered so that users can see only data for which they are authorized.
To apply this model, you must create a security filter for the data source to restrict access to data. Create a custom service that implements the ISelectQueryFilterService interface and register it in your application. The Web Document Viewer, Report Designer’s Preview, and Query Builder components call the service before the data source executes a SELECT query. The interface includes a CustomizeFilterExpression method that adds a conditional clause to the query:
public class SelectQueryFilterService : ISelectQueryFilterService {
	readonly int tenantId;
	public SelectQueryFilterService(IUserService userService) {
		tenantId = userService.GetCurrentUserId();
	}
	public CriteriaOperator CustomizeFilterExpression(
		SelectQuery query, CriteriaOperator filterExpression)
	{
		List<CriteriaOperator> filters = new();
		if(query.Tables.Any(x => x.ActualName == "Reports")) {
			filters.Add(new BinaryOperator
				(new OperandProperty("Reports.TenantID"),
				 new OperandValue(tenantId),
				 BinaryOperatorType.Equal));
		}
		if(!object.ReferenceEquals(filterExpression, null)) {
			filters.Add(filterExpression);
		}
		var result = filters.Any() ? 
			new GroupOperator(GroupOperatorType.And, filters) : null;
	return result;
	}
}
Review the following help topic (with code examples and a full ASP.NET Core project for a multi-tenant Web-based Reporting system) for additional information/guidance: Multi-Tenant Support (Row Filtering in Shared SQL Database).
Your Feedback Matters
As always, we welcome your feedback. Should you have technical questions or if you’d like to discuss specific SaaS requirements (multi-tenancy requirements), feel free to submit a support ticket via the DevExpress Support Center.

FREE DevExpress Xamarin UI Controls

Deliver high-impact native mobile apps with our feature-rich Data Grid, Scheduler, Chart, TabView, Editors, and utility controls. Reserve your free copy today!
Jonatas Hudler
Jonatas Hudler

Nice feature!

We need something just like in the Dashboard suite. We already managed to do so digging into the Data Services classes creating/replacing with own-services wrappers, intercepting and modifing the query model. But since this is not a officially supported approach, every new release this workaround breaks and have to be adapted.

5 July 2021
Alex (DevExpress Support)
Alex (DevExpress)
Hi Jonatas,

Do I understand it correctly that you have already integrated a custom security solution in your Web application? If possible, share additional information about your implementation in the Support Center. We will discuss how to address this usage scenario and minimize the amount of custom code.
14 July 2021

Please login or register to post comments.