Row level security.

Introduction

The previous post covered table level security. Let’s focus now on row level security: which rows can a user see depending on her permission set.

Let’s take in mind the app for managing purchase requests. I have an PurchaseOrder which is initially requested by a company employee (we call them requestors). Before being sent eventually to the vendor, the purchase order first needs to be approved by several other parties:  a procurement officers and and Adviser.

Let’s imagine that following row level security rules needs to be implemented:

  • requestors can see only their own requests
  • procurement officers can only see requests to which they are assigned as procurement officer
  • advisers can see only those requests to which they are assigned as adviser.
An important requirement is that it is perfectly possible that users have different roles at the same time: e.g. an adviser can hand in as well a purchase request. This means that she can see requests handed in by her and requests in which she particapates as adviser.

Preprocess query method and permissions

It is clear that the row level security that we want to implement is handled in the ProProcessQuery method associated with the PurchaseRequest entity. Furthermore, the LightSwitch permission mechanism is used as “entry point” to the security implementation.

Let’s keep the data model as simple as possible:

 

We define 3 permissions in the Access Control Tab of the application properties:

  • RLS_ApplyFilteringOnAssociatedRequestor
  • RLS_ApplyFilteringOnAssociatedProcurementOfficer
  • RLS_ApplyFilteringOnAssociatedAdviser

The RLS stands for Row Level Security.  By using this prefix the permissions can be easily identified in the permission list. That’s practical, because row level security permissions have a slightly different semantic than other permissions. A “normal” permission is a kind of additional right in the application. So, more permissions, more rights. That’s kind of different with the row level security permissions. I you have a certain RLS permission, additional filtering on data is applied, so in a way, you have less rights.

What is wrong with the following approach

What is the problem with following code?

partial void PurchaseRequests_All_PreprocessQuery(ref IQueryable<LightSwitchApplication.PurchaseRequest> query)
        {
            bool applyRequestorFiltering= this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedRequestor);
            bool applyProcurementOfficerFiltering = this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedProcurementOfficer);
            bool applyAdviserFiltering = this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedAdviser);

            if (applyRequestorFiltering)
            {
                query = query.Where(r => r.RequestorUserName == this.Application.User.Name);
            }

            if (applyProcurementOfficerFiltering)
            {
                query = query.Where(r => r.ProcurementOfficerUserName == this.Application.User.Name);
            }

            if (applyAdviserFiltering)
            {
                query = query.Where(r => r.AdviserUserName == this.Application.User.Name);
            }
        }

What is happening here? We first take the 3 permissions in a simple boolean value (e.g. applyRequestorFiltering). Depending on the active filters, a specific predicate will be applied. This all makes sense and this works also perfectly under the assumption that the user has either:

  • one of the 3 possible permissions: a single where clause is applied.
  • no permission at all: in this case the query will just be returned untouched.

What happens when the user has e.g. both the requestor and the adviser permission? Well, .. the 2 filters are applied but only the Intersection is returned and that’s not what we want. We want the records where the current user is requestor OR where the current user is Adviser.

It’s very tempting to try to combine the 3 predicates in one where clause, but you’ll see you end up with other trouble.

 

Introducing the ConditionalOr Operator

Try to see what’s happening here:

 partial void PurchaseRequests_All_PreprocessQuery(ref IQueryable<LightSwitchApplication.PurchaseRequest> query)
        {
            bool applyRequestorFiltering = this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedRequestor);
            bool applyProcurementOfficerFiltering = this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedProcurementOfficer);
            bool applyAdviserFiltering = this.Application.User.HasPermission(Permissions.RLS_ApplyFilteringOnAssociatedAdviser);

            if (!applyRequestorFiltering && !applyProcurementOfficerFiltering && !applyAdviserFiltering)
            {
                return;
            }

            Expression<Func<PurchaseRequest, bool>> StartWith = r => false;
            Expression<Func<PurchaseRequest, bool>> requestorPredicate = r => r.RequestorUserName == this.Application.User.Name;
            Expression<Func<PurchaseRequest, bool>> procurementOfficerPredicate = r => r.ProcurementOfficerUserName == this.Application.User.Name;
            Expression<Func<PurchaseRequest, bool>> adviserPredicate = r => r.AdviserUserName == this.Application.User.Name;

            Expression<Func<PurchaseRequest, bool>> predicate =
                StartWith
                .ConditionalOr(requestorPredicate, applyRequestorFiltering)
                .ConditionalOr(procurementOfficerPredicate, applyProcurementOfficerFiltering)
                .ConditionalOr(adviserPredicate, applyAdviserFiltering);
            query = query.Where(predicate);
        }

So, we split the 3 permissions into 3 Expressions and we start with a query that’s returning nothing by applying the StartWith expression. Afterwards each filter is applied in a conditional way and Or-ed with the previous expression.

This will give the result we expect: in case a user has multiple permissions, the correct result set is returned.

Of course, we still need the code for the ConditionalOr operator (I found this approach here: http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx):

public static class Utility
    {
        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {
            // build parameter map (from parameters of second to parameters of first)
            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);

            // replace parameters in the second lambda expression with parameters from the first
            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);

            // apply composition of lambda expression bodies to parameters from the first expression
            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
        }

        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.And);
        }

        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {
            return first.Compose(second, Expression.Or);
        }

        public static Expression<Func<T, bool>> ConditionalOr<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second, bool? doCompose)
        {
            if (doCompose.Value == true)
            {
                return first.Compose(second, Expression.Or);
            }

            else
            {
                return first;
            }
        }
    }
    public class ParameterRebinder : ExpressionVisitor
    {
        private readonly Dictionary<ParameterExpression, ParameterExpression> map;

        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }

        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }

        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;
            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }
            return base.VisitParameter(p);
        }
    }

 

Note that you can get the same result by using the excellent LinqKit Predicatebuilder (http://www.albahari.com/nutshell/predicatebuilder.aspx).

 

 Conclusion

Row level security is key in every decent enterprise application. LightSwitch allows to implement row level security in an excellent way. Extending Linq with the conditionalOr operator allows to really tune the row level security at any level.