WCF OData service row level security

Question

The Problem

I have a WCF OData service, backed by Entity Framework and SQL, for which I am trying to implement row level access control.

Consider the following data model, where a user has orders, and those orders have items:

┌───────┐    ┌────────┐    ┌────────┐
│User   │    │Order   │    │Item    │
├───────┤    ├────────┤    ├────────┤
│UserID │    │OrderID │    │ItemID  │
└───────┘    │UserID  │    │OrderID │
             └────────┘    └────────┘

Users should be restricted to viewing only their own orders, and the order items for those orders.

To implement this, I am using WCF query interceptors, a basic implementation being:

// currentUser is request-scoped User entity of the logged in user

[QueryInterceptor("Orders")]
public Expression<Func<Order, bool>> OrderInterceptor()
{
    return order => order.UserID == currentUser.UserID;
}

[QueryInterceptor("Items")]
public Expression<Func<Item, bool>> ItemInterceptor()
{
    return item => item.Order.UserID == currentUser.UserID;
}

However, I would like to call common code inside the interceptors, as there are many entities, and more to the access control rules than just matching the user ID.

Possible Solutions

My previous question dealt with calling a common method from the interceptors, to return an expression for multiple types. The provided answer solved that problem, but it turns out that was just the tip of the iceberg.

Use an Interface

public interface ICommonInterface
{
    int GetUserID();
}

public partial class Item : ICommonInterface
{
    public int GetUserID()
    {
        return this.Order.UserID;
    }
}

[QueryInterceptor("Items")]
public Expression<Func<Item, bool>> ItemInterceptor()
{
    return CommonFilter<Item>();
}

private Expression<Func<T, bool>> CommonFilter<T>() where T : class, ICommonInterface
{
    return entity => entity.GetUserID() == currentUser.UserID
}

Except LINQ to Entities only supports initializers, members, and navigation properties. This means any properties or methods I add to get the user ID won't work, so those are out.

Put the Expression in the Entity Class

Instead of having each entity return its associated user ID, have it implement the filter itself. Since the filter operates on a type, not an instance, it will have to be static.

public partial class Item : ICommonInterface
{
    public static Expression<Func<Item, bool>> CurrentUserFilter(int userID)
    {
        return item => item.Order.UserID == userID;
    }
}

Except interfaces don't allow static methods, so we'll have to replace it with an abstract base class. Except abstract classes also don't allow static methods.

The whole point is to apply the same filtering logic to multiple entity types, so if the filter expression method can't be called from CommonFilter, there's not much point putting it in the entity class.

Add UserID Column to All Tables

This severely denormalizes the database, and is undesirable.

Forget About Tables and Use Views

Instead of using the Items table, create an Items view that includes the user ID in each row. I haven't tried this yet, as it's a pretty big change.

So the question is, how do I implement record level security in my service?

score 1 · Answer 1 · answered Mar 24 '17 at 22:29

Use your database's native data filtering features or use a SQL proxy.

SQL proxies are components that sit between your application and your database. They intercept the SQL statement and modify it so that the relevant content is selected from the database.

For instance, your app might send the following on behalf of Alice:

SELECT * FROM records WHERE recordDate='2017-01-01'

And the proxy might modify it as follows

SELECT * FROM records WHERE recordDate='2017-01-01' AND owner='Alice'

This is called dynamic data filtering and dynamic data masking.

Here are a couple of options for you:

Microsoft SQL Server Row-Level Security
MySQL FGAC
Oracle VPD
Axiomatics Data Access Filter MD

score 0 · Accepted Answer · answered May 26 '17 at 18:01

In the end, I ended up putting the expression in the entity class and using an interface.

Interface

public interface ICommonInterface<T>
{
    Expression<Func<T, bool>> CurrentUserFilter(int userID);
}

Entity Partial Class

Static methods are not allowed in interfaces, so the expression has to be an instance method.

public partial class Item : ICommonInterface
{
    public Expression<Func<Item, bool>> CurrentUserFilter(int userID)
    {
        return item => item.Order.UserID == userID;
    }
}

Generic Filter

Since the filter is an instance method, we need to create a dummy instance to call it on (not the prettiest).

private Expression<Func<T, bool>> DefaultFilter<T>()
    where T : class, ICommonFilter<T>, new()
{
    Expression<Func<T, bool>> userFilter = new T().UserFilter(currentUser.UserID);
    // Common filtering code...

    return userFilter;
}

Query Interceptor

[QueryInterceptor("Items")]
public Expression<Func<Item, bool>> InterceptItemRead()
{
    return DefaultFilter<Item>();
}

WCF OData service row level security

The Problem

Possible Solutions

2 Answers2