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

• Jun 18, 2024
• 5 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.

UserService.cs

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.

SelectQueryFilterService.cs

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:

Program.cs

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;

var builder = WebApplication.CreateBuilder(args);

builder.Services
    .AddAuthentication(CookieAuthenticationDefaults.AuthenticationScheme)
    .AddCookie();
builder.Services.AddScoped<IUserService, UserService>();
builder.Services.AddScoped<ISelectQueryFilterService, SelectQueryFilterService>();

var app = builder.Build();

app.UseAuthentication();

app.UseAuthorization();

app.Run();

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

Global.asax.cs

DefaultQueryBuilderContainer.Register<ISelectQueryFilterService, SelectQueryFilterService>();

More Examples

Implement Row-Level Security

This example assumes that several users share the same database. The application sets the current user ID in SESSION_CONTEXT. Once the database connection opens, security policies filter rows that shouldn’t be visible to the current user.

Refer to the following example for the complete source code that implements the scenario listed above:

View Example: Reporting for ASP.NET Core - Implement Row-Level Security

The following image displays the registration form where you can select a user to see the report that displays filtered data based on your selection:

The following code snippet shows how to implement IDBConnectionInterceptor that filters data when the application connects to the database:

1. Implement the IDBConnectionInterceptor interface (RLSConnectionInterceptor.cs in this example). When the database connection opens, store the current user ID in SESSION_CONTEXT. Modify queries to the Order table - filter data by user ID. This way you implement database behavior equivalent to connection filtering.

   RLSConnectionInterceptor.cs

   using DevExpress.DataAccess.Sql;
   using System.Data;
   
   namespace WebReport.Services {
       public class RLSConnectionInterceptor : IDBConnectionInterceptor {
           readonly int employeeId;
           public RLSConnectionInterceptor(IUserService userService) {
               employeeId = userService.GetCurrentUserId();
           }
   
           public void ConnectionOpened(string sqlDataConnectionName, IDbConnection connection) {
               using(var command = connection.CreateCommand()) {
                   command.CommandText = $"EXEC sp_set_session_context @key = N'EmployeeId', @value = {employeeId}";
                   command.ExecuteNonQuery();
               }
           }
   
           public void ConnectionOpening(string sqlDataConnectionName, IDbConnection connection) { }
       }
   }
   

2. Register RLSConnectionInterceptor as an extension in IServiceCollection.

   Program.cs

   var builder = WebApplication.CreateBuilder(args);
   
   builder.Services.AddScoped<IDBConnectionInterceptor, RLSConnectionInterceptor>();
   
   var app = builder.Build();