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.
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>();