Authorized EF Core Queries with SQL Server Table-Valued Functions

You need certain users to see certain rows. That's the whole problem.

The typical solution is application-level filtering — .Where(x => x.TenantId == currentTenant) — scattered across every query in your codebase. It works until someone forgets the filter, adds a new endpoint without it, or builds a report that bypasses the service layer entirely. At that point, unauthorized data leaks.

SqlOS addresses this by composing an authorization predicate into the EF Core query itself. The database does not return unauthorized rows for queries that apply that predicate.

This guide covers the SQL Server table-valued function (TVF) approach used by SqlOS FGA. It is query-level authorization, not a SQL Server security policy: raw SQL or an EF query that omits the authorization filter is not protected automatically.

The Problem with Application-Level Filtering

Consider a standard multi-tenant query:

var documents = await db.Documents
    .Where(d => d.TenantId == currentTenantId)
    .ToListAsync();

This has four problems:

1. Easy to forget. Every query needs the filter. Miss one and you have a data leak.
2. Easy to bypass. Raw SQL, background jobs, reporting queries — anything outside your service layer skips the check.
3. Flat. TenantId works for single-level multi-tenancy. But what about org → team → project → document? You'd need nested joins and subqueries for every query.
4. Scattered. Authorization logic lives in dozens of places instead of one.

SQL Server has a built-in RLS feature using security policies and predicate functions. It works, but it's tightly coupled to database users/roles and doesn't integrate cleanly with application-level identity (JWTs, claims, etc.).

TVFs as Authorization Predicates

A Table-Valued Function (TVF) is a SQL function that returns a table. When it's an inline TVF, SQL Server's optimizer doesn't treat it as a black box — it folds the function body directly into the calling query's execution plan.

This means you can write an authorization check as a TVF and use it as a WHERE EXISTS predicate. The database handles authorization, filtering, sorting, and pagination in a single query. No post-load filtering, no N+1 permission checks, no external service calls.

Here's the core idea:

CREATE FUNCTION [dbo].fn_IsResourceAccessible(
    @ResourceId NVARCHAR(128),
    @SubjectIds NVARCHAR(MAX),
    @PermissionId NVARCHAR(128)
)
RETURNS TABLE
AS
RETURN
(
    WITH ancestors AS (
        -- Walk up the resource tree from the target to the root
        SELECT Id, ParentId, 0 AS Depth
        FROM [dbo].[SqlOSFgaResources] WHERE Id = @ResourceId
 
        UNION ALL
 
        SELECT r.Id, r.ParentId, a.Depth + 1
        FROM [dbo].[SqlOSFgaResources] r
        INNER JOIN ancestors a ON r.Id = a.ParentId
        WHERE a.Depth < 10
    )
    SELECT TOP 1 a.Id
    FROM ancestors a
    INNER JOIN [dbo].[SqlOSFgaGrants] g ON a.Id = g.ResourceId
    INNER JOIN [dbo].[SqlOSFgaRolePermissions] rp ON g.RoleId = rp.RoleId
    WHERE g.SubjectId IN (
        SELECT LTRIM(RTRIM(value)) FROM STRING_SPLIT(@SubjectIds, ',')
    )
    AND rp.PermissionId = @PermissionId
    AND (g.EffectiveFrom IS NULL OR g.EffectiveFrom <= GETUTCDATE())
    AND (g.EffectiveTo IS NULL OR g.EffectiveTo >= GETUTCDATE())
)

What this does for each row:

1. Walks up the resource tree from the target resource to the root (recursive CTE, bounded by MaxResourceHierarchyDepth, which defaults to 10)
2. Joins against grants at each ancestor node
3. Checks if any grant matches the caller's principals and the required permission
4. Returns a row if access exists (TOP 1 — one match is enough)

Because it is an inline TVF, SQL Server can compose the function into the calling query plan instead of making an application-side permission request per row. Whether a specific plan uses seeks or scans still depends on indexes, predicates, statistics, and data distribution.

Mapping It to EF Core

The TVF needs to be callable from LINQ. The recommended SqlOSDbContext<TContext> base class registers it for relational contexts:

public sealed class AppDbContext(DbContextOptions<AppDbContext> options)
    : SqlOSDbContext<AppDbContext>(options);

Then you build an authorization filter as an Expression<Func<T, bool>> that calls the TVF:

var filter = await fga.BuildFilterAsync<Document>(
    subjectId: currentUserId,
    permissionKey: "documents.read");
 
var results = await db.Documents
    .Where(d => d.ProjectId == projectId)   // your business filter
    .Where(filter)                           // ← authorization
    .OrderBy(d => d.CreatedAt)
    .Take(20)
    .ToListAsync();

EF Core translates this to a single SQL statement. The TVF call becomes a WHERE EXISTS (...) subquery that the optimizer inlines. One database round-trip. No post-load filtering.

Why a Resource Tree, Not a Flat Tenant ID

The flat TenantId approach breaks down as your product grows. Real applications have nested structure:

Organization
  └─ Team
      └─ Project
          └─ Document

With a flat model, a team lead who should see all documents in their team's projects needs explicit grants on every project, or you need complex join logic in every query.

With a resource tree, you grant a role at the team level and it inherits downward:

// The subject, resource, and role must already exist.
await db.GrantRoleAsync(
    subjectId: userId,
    resourceId: "team::engineering",
    roleKeyOrId: "viewer",
    cancellationToken: ct);
await db.SaveChangesAsync(ct);
 
// This grant now covers:
// - team::engineering
// - project::alpha (child)
// - project::beta (child)
// - doc::1, doc::2, ... (grandchildren)

The TVF handles the inheritance automatically — it walks up from each document's resource to the root, and the team-level grant matches at the ancestor node.

Performance Characteristics

The TVF approach has a bounded hierarchy walk. A useful conceptual model is:

Per-row cost: O(D) where D is the tree depth. Each row triggers a recursive CTE that walks at most D ancestor hops. With proper indexes, each hop is an index seek.

Per-page cost: O(k · D) where k is the page size. Under cursor pagination, the database evaluates rows sequentially until it finds k authorized ones.

The benchmark below found stable page-level behavior at the tested scales because cursor pagination examines rows forward from the cursor and the TVF walks a bounded ancestor chain. Treat those measurements as benchmark results, not a universal latency guarantee: indexes, selectivity, grant density, plan quality, and application predicates still matter.

Benchmarked results from the SHRBAC paper:

A notable finding: narrower grants are faster. A store-level grant (2.28ms) matches at the first ancestor hop, while a chain-level grant (3.12ms) requires 3–4 hops. Least-privilege is not just more secure — it's measurably faster.

Grants, Not Roles, Carry the Scope

A common mistake in RBAC systems is encoding scope into role names: TeamA_Editor, ProjectAlpha_Viewer, Document42_Commenter. This leads to role explosion.

With the TVF approach, roles stay generic and reusable:

// Define roles once and attach reusable permission keys
seed.Role("viewer", "Viewer").Can("documents.read");
seed.Role("editor", "Editor").Can("documents.read", "documents.edit");
 
// Scope is determined by WHERE the grant is placed
await db.GrantRoleAsync(userId, "org::acme", "viewer");   // sees everything
await db.GrantRoleAsync(userId, "team::eng", "editor");   // edits eng subtree
await db.GrantRoleAsync(userId, "doc::42", "viewer");     // sees one document
await db.SaveChangesAsync(ct);

Same three roles. Different scope depending on where in the tree the grant lives. No role explosion.

Mutations Use the Same Model

The TVF handles reads. For writes, you use point checks against the same resource tree:

var access = await fga.CheckAccessAsync(
    subjectId: userId,
    permissionKey: "documents.edit",
    resourceId: document.ResourceId);
 
if (!access.Allowed)
    return Results.Forbid();

Reads and writes use the same grants, the same roles, the same resource tree. One authorization model for your entire application.

When to Use This Approach

Use TVF-based RLS when:

• You need row-level visibility filtering in EF Core
• Your authorization model has hierarchical structure (orgs, teams, projects, etc.)
• You want authorization composed into the SQL query rather than evaluated after materialization
• You can index the resource/grant paths and benchmark the expected hierarchy and grant density
• You want the same model for human users, service accounts, and autonomous agents

Consider alternatives when:

• Your access model is truly flat (single tenant ID per row, no hierarchy)
• You need arbitrary attribute-based policies (IP ranges, time-of-day, device type)
• Your resources form a DAG rather than a tree (multiple parents per node)

Getting Started

SqlOS implements this pattern as a library. Install it, register the services, and the TVF is created automatically:

dotnet add package SqlOS

builder.AddSqlOS<AppDbContext>(
    db => db.UseSqlServer(connectionString),
    options =>
    {
        options.Fga.Seed(seed =>
        {
            seed.ResourceType("document", "Document");
            seed.Permission("documents.read", "Read documents", "document");
            seed.Permission("documents.edit", "Edit documents", "document");
            seed.Role("viewer", "Viewer").Can("documents.read");
            seed.Role("editor", "Editor").Can("documents.read", "documents.edit");
        });
    });
 
var app = builder.Build();

The getting started guide walks through the full setup. The SHRBAC paper covers the formal model and benchmark methodology.

Authorized row filtering is a property of how the authorization model composes with a data query. With SqlOS, the filter becomes part of the SQL query instead of running after materialization. Apply it directly on every protected list path; it does not turn unfiltered EF or raw SQL into an automatically protected query.