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

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;
    }
}

Answer 3

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 4

If you're at all concerned about securing this solution, I don't think you can cache, except for the user in question. That said, I would approach this by returning data consisting of a bit visible field, and would null those fields which are not visible. If you pull your data into a temp table, figure out what they're supposed to be able to see (updating the visible field as appropriate), and then null out the values, you'd then be able to clearly indicate to your UI that something is null because it's stored that way (visible is 1 but has null in the field) or because the user shouldn't be able to see it (visible is 0 and data is null).

Yes, this is a bit tedious. It also means that you're not running the risk of returning data to the user that they shouldn't see - your UI developer / maintainer will simply not be able to display the data if it's not supposed to be shown. You're safer that way, if it's you doing the UI work, because you won't forget what you were intending somewhere down the road and expose the data.