Multi-tenant reporting in ASP.NET Core: row-level security your users can't bypass

By Razi Syed. Sample code for this article: github.com/dotnetreport/dotnetreport-multitenant-rls

If you run a multi-tenant SaaS product on .NET, you have already solved tenant isolation for the parts of the app you wrote. Every query your code issues carries a WHERE TenantId = @tenant, or a global query filter in EF Core does it for you, and you sleep fine.

Then a customer asks for reporting. Not a fixed set of reports — self-service reporting, where their analysts pick tables and columns, group and filter however they like, and build dashboards without a ticket to your team.

And now you have a problem, because the thing that made isolation easy — you write the query — is gone. The user writes the query. If isolation is something your code adds at the call site, there is no call site anymore.

This article walks through the pattern I use to make row-level security a property of the reporting engine rather than of any particular query, so that it applies to every report a user builds, from context the user cannot change. The examples use Dotnet Report, an embedded report builder for .NET that I work on (disclosure up front), but the shape of the solution — a single server-side context method plus engine-applied filters — is what matters, and it transfers to any reporting layer that gives you the same hooks.

Why "just add the WHERE clause" stops working

Three things go wrong once users author their own queries:

1. There is no fixed query to modify. A user can join Orders to Customers to Regions in an order you never anticipated. A filter that only knows about Orders misses rows that leak through the join.
2. Client-side filtering is not security. Anything applied in the browser — hidden columns, default filters, "don't show tenant X" — is one DevTools session away from being removed.
3. Scheduled and exported reports run with nobody logged in. If isolation depends on the current HTTP request, a nightly emailed PDF has no request and therefore no isolation.

So the requirements are: the filter must be applied server-side, on every table that carries a tenant column, for every query path (interactive, export, scheduled), from trusted context the user cannot forge.

One method, every request

Dotnet Report routes every call from the report builder through a single server-side method, GetSettings(), on the API controller the NuGet package installs. Whatever that method returns is sent to the reporting engine with the request. The three properties that matter for multi-tenancy are:

• ClientId — the tenant. Scopes reports, folders and dashboards so tenants don't see each other's saved work.
• UserId and CurrentUserRole — the user and their roles, for ownership, sharing and role-based access.
• DataFilters — row-level security. A filter the engine appends to every generated SQL statement.

That last one is the key. DataFilters is an anonymous object whose property names are column names and whose values are the allowed values:

settings.DataFilters = new { TenantId = "42" };

The engine turns that into, in effect,

... AND [Table].[TenantId] IN (42)

for every table in the query that has a TenantId column — which is exactly what solves problem #1 above. Join Orders to Customers to Regions; if all three carry TenantId, all three get filtered.

If you need to be explicit about a table, use the Table__Column form (double underscore), and you can combine several filters:

settings.DataFilters = new
{
    Orders__TenantId    = "42",
    Customers__TenantId = "42",
    RegionId            = "3,7"   // comma-separated -> IN (3,7)
};

Drive it from claims, never from input

The values above are hard-coded, which is fine for a demo and unacceptable for production. The whole point is that the tenant comes from context the user cannot change — and in ASP.NET Core that means claims on the authenticated principal, issued by your sign-in code after you verified who they are.

Here is the middle of GetSettings() after the change. (The token/config lines at the top of the installed method stay as they are; the full method is in the sample repo.)

var user = User as ClaimsPrincipal;

// The tenant this user belongs to. Issued at login by YOUR code.
var tenantId = user?.FindFirst("tenant_id")?.Value ?? string.Empty;

// Scopes saved reports, folders and dashboards to the tenant.
settings.ClientId = tenantId;

// Scopes ownership and sharing to the user.
settings.UserId   = user?.FindFirst(ClaimTypes.NameIdentifier)?.Value ?? string.Empty;
settings.UserName = user?.Identity?.Name ?? string.Empty;

// Roles drive role-based access to reports and folders.
settings.CurrentUserRole = user?.Claims
    .Where(c => c.Type == ClaimTypes.Role)
    .Select(c => c.Value)
    .ToList() ?? new List<string>();

// Row-level security: appended to EVERY generated query, server-side.
settings.DataFilters = new { TenantId = tenantId };

And the sign-in side, which is the only place the tenant is ever decided. This is a plain cookie-auth example; with ASP.NET Core Identity or OpenID Connect the mechanism is the same — you add the claim when you build the principal:

var claims = new List<Claim>
{
    new(ClaimTypes.NameIdentifier, userId),
    new(ClaimTypes.Name, userName),
    new("tenant_id", tenantId),      // looked up from YOUR user store, not from the request
};
claims.AddRange(roles.Select(r => new Claim(ClaimTypes.Role, r)));

var identity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
await http.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, new ClaimsPrincipal(identity));

Notice what the user cannot do now. They can build any report they like, join any tables, add any filters — and every query still ends with a tenant clause they never see and cannot remove, because it is added on the server from a claim that only your login code can issue.

What about exports and scheduled reports?

This is where the "single method" design pays off a second time. Exports go through the same context. Scheduled reports are more interesting: when a user schedules a report to be emailed nightly, the schedule saves the user's DataFilters alongside it, and the background job renders the report with those saved filters — so a PDF that lands in an inbox at 6 a.m. is filtered exactly as it would have been in the browser, with nobody logged in. Isolation doesn't depend on an HTTP request existing.

Cases you'll hit in practice

A user who spans tenants. Support staff or a parent-company admin may legitimately need several tenants. Issue a claim listing them and pass the comma-separated value — "1,2,3" becomes IN (1,2,3). Isolation is still enforced; it's just enforced to a set.

Restrictions beyond tenant. A regional manager should see only their regions within their tenant. Add a second filter from a second claim: new { TenantId = tenantId, RegionId = regionIds }. Filters compose.

Tables without a tenant column. Lookup/reference tables (countries, currencies) don't need filtering, and a filter keyed on a column they don't have simply doesn't apply to them. The rule is: any table that does carry tenant data must have the tenant column, or it can leak. That's a schema discipline you need regardless of reporting tool.

Admin capabilities. Who can enter the builder's admin mode or the schema setup page is also claim-driven (AllowAdminMode, AllowSetupPageAccess), so you gate those the same way you gate everything else.

Testing it

Do the boring test and do it every release: two users in two tenants, each builds the widest report they can — every table, every join — and each confirms zero rows from the other tenant and zero visibility of the other's saved reports. Then schedule one of those reports, receive the email, and check the PDF. If all three pass, your isolation is a property of the engine, not of your vigilance.

Takeaways

• Self-service reporting removes the call site where you used to add WHERE TenantId = …. Isolation has to move into the engine.
• Put tenant, user and roles into a single server-side context method, sourced from claims, and have the engine append the filter to every query — including joins, exports and scheduled runs.
• Never hard-code tenant values; never trust anything from the client for this.

The complete GetSettings() and a Program.cs that issues the claims are in the sample repo: dotnetreport/dotnetreport-multitenant-rls. If you're starting from zero, my earlier post, Add self-service ad hoc reporting to an ASP.NET Core app, and the quickstart repo get the report builder running first.

---

Razi Syed builds Dotnet Report, an embedded self-service reporting platform for .NET whose report-builder front-end is source-available on GitHub. He writes about adding reporting and analytics to SaaS products without rebuilding them from scratch.