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 authorizing the EF Core query itself: you page it with ToPageAsync, and SQL Server checks its rows against the caller's grants. A page never contains a row the caller may not see.

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 is not paged with ToPageAsync 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 evaluate it in the SQL that reads your rows. The database handles authorization, filtering, sorting, and pagination. 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 EF Core. The recommended SqlOSDbContext<TContext> base class registers it for relational contexts and keeps a copy of every index of a protected table under every node of the resource tree, current on save:

public sealed class AppDbContext(DbContextOptions<AppDbContext> options)
    : SqlOSDbContext<AppDbContext>(options)
{
    public DbSet<Document> Documents => Set<Document>();
 
    protected override void OnApplicationModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Document>(entity =>
        {
            entity.HasIndex(d => d.ResourceId).IsUnique();
            entity.HasIndex(d => new { d.CreatedAt, d.Id });   // the order lists page by
        });
    }
}

Then you write the query as usual and page it with ToPageAsync, naming the caller and the permission:

var page = await db.Documents
    .Where(d => d.ProjectId == projectId)   // your business filter
    .OrderBy(d => d.CreatedAt)
    .ToPageAsync(                            // ← authorization and pagination
        subjectId: currentUserId,
        permissionKey: "documents.read",
        pageSize: 20,
        cursor: cursor,
        cancellationToken: ct);

ToPageAsync reads your query in its order and checks each row with the TVF in SQL, for as many rows as the page needs. When few of those rows are visible, it reads its copy of the order's index under each resource the caller is granted instead. The page's rows are then loaded through your query and checked once more. Unauthorized rows never reach the application, and page.NextCursor continues the list.

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.

That holds when authorized rows are common in the order. For a caller who sees a few rows of a large table, reading forward until k are found can read most of the table. That is why the order is indexed: SqlOS keeps a copy of every index under every node of the resource tree, ToPageAsync reads it from the caller's granted resources, and the page still costs about a page.

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

ToPageAsync and the TVF handle list 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 check runs in SQL as the page is read instead of after materialization. Page every protected list path with ToPageAsync; it does not turn an unpaged EF query or raw SQL into an automatically protected query. There is no authorized count or join: those cost every row the caller can see, so lists are read a page at a time.