Skip to main content
All docs
V22.1

Multi-Tenant Support (Row Filtering in Shared SQL Database)

  • 4 minutes to read

This topic describes how to use Web Reporting in a multi-tenant environment. The idea is to restrict access to the source data based on the user who is logged into the system.

Security Filters

Security filters are part of the multi-tenancy application architecture. They are row-level filters applied to the underlying data from the database.

In detail, a security filter is a conditional clause applied to each logical query that retrieves data from tables with the tenant ID column. The filter is used to constrain data based on the rows where the tenant ID column value matches the user ID variable obtained from the application’s user context.

DevExpress Web Reporting allows you to create and register a service that implements the DevExpress.DataAccess.Web.ISelectQueryFilterService interface. The ISelectQueryFilterService.CustomizeFilterExpression method applies a conditional clause to the query passed to the method as a parameter. The Document Viewer, Report Designer’s Preview, and Query Builder call the ISelectQueryFilterService service before the SqlDataSource executes a SELECT query.

Multi-Tenancy Implementation

This section explains how to implement security filters in an ASP.NET Core Reporting application.

View Example: Row-Level Filtering in ASP.NET Core Reporting Application with SqlDataSource (Multi-Tenant Support)

Implement Authentication

For ease of demonstration, this example uses a simulated user login (without actual verification) that allows your code to use this user’s identity.

Obtain the User ID

Create and register the UserService service that processes HttpContext and retrieves the user ID.

using System;
using System.Globalization;
using System.Linq;
using System.Security.Claims;
using Microsoft.AspNetCore.Http;

namespace QueryFilterServiceApp.Services {
    public interface IUserService {
        int GetCurrentUserId();
        string GetCurrentUserName();
    }

    public class UserService : IUserService {
        readonly IHttpContextAccessor contextAccessor;

        public UserService(IHttpContextAccessor contextAccessor) {
            this.contextAccessor = contextAccessor 
                ?? throw new ArgumentNullException(nameof(contextAccessor));
        }

        public int GetCurrentUserId() {
            var sidStr = contextAccessor.HttpContext.User.Claims
                .FirstOrDefault(x => x.Type == ClaimTypes.Sid);
            return Convert.ToInt32(sidStr.Value, CultureInfo.InvariantCulture);
        }

        public string GetCurrentUserName() {
            return contextAccessor.HttpContext.User.Claims
                .Single(x => x.Type == ClaimTypes.Name).Value;
        }
    }
}

Add Security Filter

Create and register the SelectQueryFilterService service that implements the DevExpress.DataAccess.Web.ISelectQueryFilterService interface. The service calls the UserService service to get the ID of the user who is logged into the application.

The following code determines whether the query contains the specified tables, and adds conditional clauses that retrieve data rows where the StudentID column value matches the current User ID.

using System.Collections.Generic;
using System.Linq;
using DevExpress.Data.Filtering;
using DevExpress.DataAccess.Sql;
using DevExpress.DataAccess.Web;

namespace QueryFilterServiceApp.Services {
    public class SelectQueryFilterService : ISelectQueryFilterService {
        readonly int studentId;

        public SelectQueryFilterService(IUserService userService) {
            studentId = userService.GetCurrentUserId();
        }

        public CriteriaOperator CustomizeFilterExpression(
            SelectQuery query, CriteriaOperator filterExpression) 
        {
            List<CriteriaOperator> filters = new List<CriteriaOperator>();

            if(query.Tables.Any(x => x.ActualName == "Students")) {
                filters.Add(new BinaryOperator
                    (new OperandProperty("Students.ID"), 
                    new OperandValue(studentId), 
                    BinaryOperatorType.Equal));
            }

            if(query.Tables.Any(x => x.ActualName == "Enrollments")) {
                filters.Add(new BinaryOperator
                    (new OperandProperty("Enrollments.StudentID"), 
                    new OperandValue(studentId), 
                    BinaryOperatorType.Equal));
            }

            if(query.Tables.Any(x => x.ActualName == "Reports")) {
                filters.Add(new BinaryOperator
                    (new OperandProperty("Reports.StudentID"), 
                    new OperandValue(studentId), 
                    BinaryOperatorType.Equal));
            }

            if(!object.ReferenceEquals(filterExpression, null)) {
                filters.Add(filterExpression);
            }

            var result = filters.Any() ? new GroupOperator(GroupOperatorType.And, filters) : null;
            return result;
        }
    }
}

Note that the ISelectQueryFilterService does not allow you to modify the query passed to the CustomizeFilterExpression method. The method returns the CriteriaOperator that forms the WHERE clause for the original SELECT query.

Register Services

Register services at application startup.

In the ASP.NET Core application you can use the following code:

using DevExpress.AspNetCore;
using DevExpress.AspNetCore.Reporting;
using DevExpress.DataAccess.Web;
using DevExpress.XtraReports.Web.Extensions;
using Microsoft.AspNetCore.Authentication.Cookies;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;


public class Startup {
    // ...
    public void ConfigureServices(IServiceCollection services) {
        services
            .AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
            .AddCookie();
        // ...

        services.AddScoped<IUserService, UserService>();
        services.AddScoped<ISelectQueryFilterService, SelectQueryFilterService>();
    }

    public void Configure(IApplicationBuilder app, IWebHostEnvironment env, ILoggerFactory loggerFactory) {
        // ...
        app.UseAuthentication();
        // ...
        app.UseAuthorization();
        // ...
    }
}

In the ASP.NET WebForms or MVC applications you should register the DevExpress.DataAccess.Web.ISelectQueryFilterService service as follows:

DefaultQueryBuilderContainer.Register<ISelectQueryFilterService, SelectQueryFilterService>();