How To design configurable field level permissions with Entity Framework

Question

Say we have a table of information pertaining certain models of cars, such as the following:

How would I best implement field level access permissions for reading and write operations if I also need the rules to be user-configurable? I am using MSSQL Server 2016 and EF 6.

Based on that table we might have the following use-cases, that describe the fields visible to a certain role or group:

1) Default permission group for public data

2) Entity-based permission group

3) Custom field based permission group

Requirements are, that the hidden data must be distinctive from NULL-values and rules/permissions must be user-configurable. I also need to paginate lists, which requires correct sorting on visible data. For this, I need a way to handle data types. For example, the construction year is a non-nullable DateTime, yet when the field is not visible it needs to be set to a default value like DateTime.MinValue. This becomes much more challenging when dealing with bit (boolean) values :-)

I am currently considering an approach with either table-valued functions, which seems to be more difficult to implement dynamically for my scenario, or a separate caching layer that holds the entirety of the data, which I would need to keep in sync with the database.

Answer 1

One simple way to achieve your goal can be to create a settings table, where to specify the visibility of each field by group.

First you will need make a group(for brand) table like this:

 public class Group
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

then you will need a table for visibility settings:

  public class TableVisibilitySettings
    {
        public int Id { get; set; }
        public int GroupId { get; set; }
        public virtual Group Group { get; set; }
        public bool ContructionYear { get; set; }
        public bool Power { get; set; }
        public bool IsConvertible { get; set; }
    }

Then you will need your table and the view model:

public class Table
    {
        public int Id { get; set; }
        public int GroupId { get; set; }
        public virtual Group Grup { get; set; }

        public string Color { get; set; }
        public int? ConstructionYear { get; set; }
        public string Power { get; set; }
        public bool? IsConvertible { get; set; }


        public IEnumerable<TableVm> GetTableByGroupType(int groupId, ApplicationDbContext context)
        {
            var table = context.Tables.ToList();
            var visibility = context.TableVisibilitySettings.FirstOrDefault(x => x.GroupId == groupId);

            return table.Select(x => new TableVm
            {
                Id = x.Id,
                Brand= x.Grup.Name,
                Color = x.Color,
                ConstructionYear = visibility.ContructionYear == true ? x.ConstructionYear : null,
                Power = visibility.Power == true ? x.Power : null,
                IsConvertible = visibility.IsConvertible == true ? x.IsConvertible : null
            }).ToList();
        }
    }

Using the method GetTableByGroupType you can retrieve the data base on the visibility settings for each group.

If you want you can use the Roles instead of Group.

Edit:

One way to apply pagination can be like this:

 public IEnumerable<TableVm> GetTableByGroupWithPag(int groupId, ApplicationDbContext context,int pageNumber, int rowsPerPage)
        {

            var table = context.Tables.Skip((pageNumber-1)*rowsPerPage).Take(rowsPerPage).ToList();

            var visibility = context.TableVisibilitySettings.FirstOrDefault(x => x.GroupId == groupId);

            return table.Select(x => new TableVm
            {
                Id = x.Id,
                Group = x.Grup.Name,
                Color = x.Color,
                ConstructionYear = visibility.ContructionYear == true ? x.ConstructionYear : null,
                Power = visibility.Power == true ? x.Power : null,
                IsConvertible = visibility.IsConvertible == true ? x.IsConvertible : null
            }).ToList();
        }

First you need to take the rows to display from your table, than you only need to apply the visibility settings.

Edit:

There are several ways to link a group to the user, depending of your application design and your skills. The most simple way is to set a one to one, or many to many relations between ApplicationUser and Group, like this:

public class ApplicationUser
{
 ...
 public int GroupId {get;set;}
 public virtual Group Group
}

and in the Group class you need to add:

 public virtual ICollection<ApplicationUser> Users {get;set;}

Another way is to create roles for each brand and to give each user one or more roles based on the brands which you want him to read/write.

Another way is to use Claims, and all you need to do is to add to each user a claim representing the groupId or the groupName or the brand.

Hope that this will help you chose a way to link the user to the group.

Answer 2

Since you need to configure permissions like this (see my comment) the issue has nothing to do with EF - this is related to your app's business logic.

I suggest to design an API within your business layer which reads the data - i.e. the cars - and applies the security permissions, which might (or might not) be read in advance.

IMO, the permissions configuration table schema, should look like this:

CREATE TABLE [dbo].[PermissionsConfig] (
    [Id]         INT NOT NULL,
    [CarId]      INT NOT NULL,
    [UserId]     INT NOT NULL,
    [Permission] INT NOT NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC), 
    CONSTRAINT [FK_PermissionsConfig_Car] FOREIGN KEY ([CarId]) REFERENCES [Car]([Id]), 
    CONSTRAINT [FK_PermissionsConfig_User] FOREIGN KEY ([UserId]) REFERENCES [User]([Id])
);

Next create a flagged enum to specify the permissions:

[Flags]
public enum CarFieldPermission
{
    Unknown = 0,
    ViewConstructionYear = 2,
    ViewPower = 4,
    ViewIsConvertible = 8
}

To configure the permissions, loop through the necessary users/roles/groups and all the cars and do a bitwise OR on the flags to calculate permissions. E.g.

var permissionConfigEntry.Permission = CarFieldPermission.ViewConstructionYear 
    | CarFieldPermission.ViewPower
;

Later, in your business layer API, read a page from the cars table (use LINQ Skip() and Take() methods). Then loop through the records and check the permissions configuration against the current user and car; hide the data as necessary:

public IEnumerable<Car> LoadCars(User user, int pageIndex, int pageSize)
{
    var result = db.Cars
        .Skip((pageIndex - 1) * pageSize)
        .Take(pageSize)
        .ToArray()
    ;

    var carsInInterest = result.Select(c => c.Id).ToArray();

    var allThePermissions = db.PermissionConfiguration
        .Where(pc => pc.User.Equals(user))
        .Where(pc => carsInInterest.Contains(pc.CarId))
        .ToArray()
    ;

    foreach (var carX in result)
    {
        var current = allThePermissions.FirstOrDefault(pc => pc.User.Equals(user) && pc.Car.Equals(carX));

        if (current != null)
        {
            if (!current.Permissions.HasFlag(CarFieldPermission.ViewConstructionYear))
            {
                carX.ConstructionYear = null;
            }
        }
    }

    return result;
}

Answer 3

Another option would be to create a proxy with Castle.DynamicProxy (https://github.com/castleproject/Core/blob/master/docs/dynamicproxy.md):

class Program
{
    static void Main(string[] args)
    {
        ProxyGenerator generator = new ProxyGenerator();
        var person = new Person { Id = 1, Name = "Bob", Age = 40 };
        var proxy = generator.CreateClassProxyWithTarget<Person>(person, new EFInterceptor(new SecurityInfo()));
        Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", person.Id, person.Name, person.Age);
        Console.WriteLine("Id: {0}, Name: {1}, Age: {2}", proxy.Id, proxy.Name, proxy.Age);
    }
}

public class Person
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual int Age { get; set; }
}

public interface ISecurityInfo
{
    bool IsAllowed(string propName);
}

public class SecurityInfo : ISecurityInfo
{
    public bool IsAllowed(string propName)
    {
        return propName != nameof(Person.Age);
    }
}

class EFInterceptor : Castle.DynamicProxy.IInterceptor
{
    private readonly ISecurityInfo securityInfo;

    public EFInterceptor(ISecurityInfo info)
    {
        this.securityInfo = info;
    }

    public void Intercept(IInvocation invocation)
    {
        if (invocation.Method.Name.StartsWith("get_"))
        {
            var propName = invocation.Method.Name.Replace("get_", "");
            HandleAccess(invocation, propName);
        }
        if (invocation.Method.Name.StartsWith("set_"))
        {
            var propName = invocation.Method.Name.Replace("set_", "");
            HandleAccess(invocation, propName);
        }
    }

    private void HandleAccess(IInvocation invocation, string propName)
    {
        if (!securityInfo.IsAllowed(propName))
        {
            invocation.ReturnValue = GetDefault(invocation.Method.ReturnType);
        } else
        {
            invocation.Proceed();
        }
    }

    public static object GetDefault(Type type)
    {
        if (type.IsValueType)
        {
            return Activator.CreateInstance(type);
        }
        return null;
    }
}