Building an Enterprise Data Access Layer: Composable Row-Level Security

2025-11-24

7 minutes

Welcome to the sixth post in our series on building a feature-rich, automated enterprise Data Access Layer (DAL) using C# and Linq2Db. In our previous articles, we established a powerful architectural pattern: a composable global query filter system. This system allows us to define cross-cutting concerns as simple interfaces, automatically combining and applying them to every relevant query.

So far, we have successfully implemented:

Automated Auditing (ICreatable, IModifiable) to set CreatedAt and ModifiedAt timestamps.
Transparent Soft-Deletes (IRemovable) to filter out records where RemovedAt is not NULL.
Composable Multi-Tenancy (ITenanted) to automatically isolate data based on a TenantId, even through projected relationships.

In this post, we will tackle the final and most complex pillar of our automated DAL: entity-based row-level security (RLS). Our goal is to automatically filter any query to return only the records that the current user is authorized to access. We will achieve this by extending the exact same composable filter architecture we've already built, demonstrating its power and flexibility.

1. Defining the Security Contracts

To build an RLS system, we need to define three core components:

A contract for the current user.
A contract for a permission record.
A contract for an entity that requires protection.

Updating the Database Context

First, we must make our DAL aware of the "current user." We previously extended our IDbCtx with DbCtxAttributes to hold the current TenantId. We will now update that same record to also hold a UserId.

// In DAL.Base/IDbCtx.cs
public interface IDbCtx : IDataContext
{
	DbCtxAttributes Attributes { get; set; }

	ITable<IPermissionEntity> GetPermissions(); // New!

	public record DbCtxAttributes(
		Ulid TenantId = default,
		Ulid? UserId = null // New!
	);
}

We've added two things:

Ulid? UserId: This will be set by the application (e.g., from an HTTP context) at the beginning of a request. It's nullable, which is a critical design choice. If UserId is null, our RLS system will treat the user as unauthenticated and (by default) deny all access to protected entities.
ITable<IPermissionEntity> GetPermissions(): This new method is an abstraction. Our filter logic needs to query "permissions," but it shouldn't be hard-coded to a specific Permission table. This interface-based approach allows our DbCtx implementation to provide the correct table, promoting loose coupling.

The Permission Contract (`IPermissionEntity`)

Next, we need a way to represent a permission. In our database, we've added a new permission table with a simple schema: subject_id (who has the permission) and object_id (what entity the permission is for).

To map this in code, we create a new interface:

// In DAL.Base/EntityBehavior/IPermissionEntity.cs
public interface IPermissionEntity
{
	Ulid SubjectId { get; } // The "Who" (e.g., a UserId)
	Ulid ObjectId { get; }  // The "What" (e.g., a PostId)
}

After running our scaffolder to generate the Permission entity from the new table, we simply create a partial class to apply this interface:

// In DAL.Context/Entity/Permission.cs
public partial class Permission : IPermissionEntity
{ }

Finally, we implement the new GetPermissions() method in our DbCtx.cs file. It's a simple one-line pass-through:

// In DAL.Context/DbCtx.cs
public ITable<IPermissionEntity> GetPermissions() => GetTable<Permission>();

2. The `IProtected` Interface: The RLS Filter

With our contracts in place, we can now create the "behavior" interface that will automatically apply our RLS filter. We'll call it IProtected. This interface is the heart of our new system and leverages the same [EntityFilter] attribute we used for soft-deletes and tenancy.

// In DAL.Base/EntityBehavior/IProtected.cs
[EntityFilter<IProtected>(nameof(Filter))]
public interface IProtected
{
	Ulid GetPermissionObjectId();

	private static IQueryable<T> Filter<T>(IQueryable<T> q, IDbCtx dbCtx)
		where T : IProtected
		=> dbCtx.Attributes.UserId == null
			// 1. If no user is in context, return nothing.
			? q.Where(_ => false)
			// 2. Otherwise, join with permissions and filter.
			: q.InnerJoin(
				dbCtx.GetPermissions(),
				(entity, perm) =>
					perm.ObjectId == entity.GetPermissionObjectId() &&
					perm.SubjectId == dbCtx.Attributes.UserId,
				(entity, perm) => entity
			);
}

Let's break this down, as it's the most important piece:

[EntityFilter<...>]: This attribute, as before, tells our DAL's bootstrapper to find the Filter method and apply it to any entity that implements IProtected.
Ulid GetPermissionObjectId(): This is the contract. Any entity implementing IProtected must tell the system which ObjectId to use for its permission check. This is a method, not a property, which allows for incredible flexibility, as we'll see next.
private static IQueryable<T> Filter<...>: This is the filter logic.
- The Security Check: First, it checks dbCtx.Attributes.UserId == null. If true, it returns q.Where(_ => false). This is a "fail-closed" security principle. If we don't know who the user is, they get to see nothing.
- The RLS Join: If a user is present, it performs an InnerJoin between the entity query (q) and the permissions table (dbCtx.GetPermissions()).
- The Join Condition: This is the core logic: perm.ObjectId == entity.GetPermissionObjectId() && perm.SubjectId == dbCtx.Attributes.UserId. It translates to: "Find a permission record where the ObjectId matches the ID the entity provides, AND the SubjectId matches the current user in the context."
- The Result: The final lambda (entity, perm) => entity simply returns the original entity. The InnerJoin acts as a pure filter, discarding any entity that doesn't have a matching permission record for the current user.

3. Implementation: Projected Permissions

Now, let's apply our new IProtected interface to an entity. A common scenario is that permissions aren't granted to granular entities like Comment, but rather to their parent, like Post. A user who can access a Post can access all of its Comments.

Our IProtected interface's GetPermissionObjectId() method is designed for precisely this. We can implement it on our Comment entity to return its parent Post.Id. This is the exact same "projected" logic we used for multi-tenancy.

// In DAL.Context/Entity/Comment.cs
public partial class Comment : IProtected
{
	// This is the implementation of IProtected.GetPermissionObjectId()
	[ExpressionMethod(nameof(GetPermissionObjectIdExpression))]
	public Ulid GetPermissionObjectId() => Post.Id;

	private static Expression<Func<Comment, Ulid>> GetPermissionObjectIdExpression()
		=> x => x.Post.Id;
}

By using Linq2Db's [ExpressionMethod], we are telling the query engine that when it sees entity.GetPermissionObjectId() inside our Filter's InnerJoin, it should substitute the expression x => x.Post.Id.

The RLS filter logic will now transparently translate to: perm.ObjectId == comment.Post.Id. The DAL will automatically join the Comment table to the Post table to get the Id for the check, all without the developer ever thinking about it.

4. The Payoff: The Final Composed Query

We have now layered four distinct, automated behaviors onto our entities:

ICreatable & IModifiable (Auditing)
IRemovable (Soft-Delete)
ITenanted (Multi-Tenancy)
IProtected (Row-Level Security)

Let's see what happens when we execute a simple business operation, like modifying a comment. We add this code to our Program.cs:

// Try to update a protected comment entity
await ctx.GetTable<Comment>()
	.Set(x => x.Content, "asd123")
	.ModifyAsync();

Remember, our custom ModifyAsync extension also automatically handles setting the ModifiedAt timestamp. The developer wrote a simple, two-line update. Our DAL, however, composes all the rules and generates the following SQL:

UPDATE
	[comment]
SET
	[content] = 'asd123',
	-- 1. "ModifyAsync" Auditing
	[modified_at] = '2025-11-04 12:34:11.899'
FROM
	[post] [a_Post],
	[user] [a_User],
	-- 4. RLS Join
	[permission] [b]
WHERE
	-- 2. Soft-Delete Filters (on all joined tables)
	[a_User].[removed_at] IS NULL AND
	[a_Post].[removed_at] IS NULL AND
	[comment].[removed_at] IS NULL AND
	-- 3. Projected Multi-Tenancy Filter
	[a_User].[tenant_id] = X'019A4EDC4AD87A53C22679FA88F15885' AND
	-- Joins for projections
	[comment].[post_id] = [a_Post].[id] AND
	[a_Post].[user_id] = [a_User].[id] AND
	-- 4. Projected Row-Level Security Filter
	[b].[object_id] = [a_Post].[id] AND
	[b].[subject_id] = X'019A4EDC4ADE42CA89BA742103A7A723'

This query is a perfect demonstration of our architecture's power:

Auditing: ModifyAsync correctly injected the modified_at SET clause.
Soft-Delete: The [removed_at] IS NULL filter was applied to comment, post, AND user.
Multi-Tenancy: The projected tenancy filter ([a_User].[tenant_id] = ...) is present, ensuring we don't even see comments from other tenants.
Row-Level Security: The new IProtected filter joined the permission table ([b]) and correctly filtered on the Post ID ([b].[object_id] = [a_Post].[id]) and the current User ID ([b].[subject_id] = ...).

The developer is completely abstracted from this complexity. They just write business logic, and the DAL guarantees that auditing, soft-deletes, tenancy, and row-level security are all correctly and automatically enforced.

Conclusion

With the addition of composable row-level security, our enterprise DAL framework is now feature-complete on its core data integrity and security goals. We have proven that a thoughtful architecture built on interfaces, expression trees, and composable filters can eliminate entire classes of common bugs and security vulnerabilities.

By separating the definition of a behavior (the interface) from its implementation (the filter logic), we've created a system that is robust, testable, and incredibly easy to extend.

As always, the complete, working implementation for this post is available in our GitHub repository: https://github.com/ByteAether/EnterpriseDal/tree/part6

In our next post, we will address the final piece of our auditing system: automatically populating the CreatedBy and ModifiedBy fields, leveraging the new UserId we just added to our DbCtxAttributes.