Filtering Data using Entity Set Filters (Michael Simons)

Filtering Data using Entity Set Filters (Michael Simons)

Rate This
  • Comments 14

It is common for line of business applications to have scenarios which require row level security (RLS). For example a LightSwitch developer creates an application that stores Employee information such as name and address along with a relationship to EmployeeBenefit information such as salary and number of vacation days. All users of the application should have access to read all of the Employee information but a typical user should only be able to view their own EmployeeBenefit information. In this article, I will show how the entity set filter functionality that was added to LightSwitch in Visual Studio 11 can be used to solve this problem.

Visual Studio LightSwitch (LightSwitch V1)

LightSwitch V1 provided no built in means to solve this problem. The entity set CanRead functionality applies to the entity type as a whole and cannot be used to prevent read access to a specific entity or subset of entities. It has sometimes been assumed that the EntitySet_All preprocess query interception methods can be used prevent read access to a specific set of entities. Although this works when the entity isn’t related to any other entities, it will not work if the entity has any relationships because the query expression defined within the EntitySet_All preprocess query methods only gets applied when the entity set is the target of the query. It is not applied when the entity is referenced or accessed via a navigation property within a query that targets another entity set. Suppose a LightSwitch developer writes the following EmployeeBenefits EntitySet_All query interception method.

C#

partial void EmployeeBenefitsSet_All_PreprocessQuery(ref IQueryable<EmployeeBenefits> query)
{
    query = query.Where(eb => eb.Employee.UserId == this.Application.User.Name);
}

VB

Private Sub EmployeeBenefitsSet_All_PreprocessQuery(ByRef query As IQueryable(Of EmployeeBenefits))
    query = query.Where(Function(eb) eb.Employee.UserId Is Me.Application.User.Name)
End Sub

This logic would only prevent an end user from seeing other user’s EmployeeBenefit information when executing queries directly on the EmployeeBenefit entity set. For example, it would prevent the following query from returning all of EmployeeBenefit information that does not belong to the user who is executing the query.

EmployeeBenefitsSet.Where(eb => eb.Salary > 100000)

The preprocess query interception logic shown above would not get applied however when the EmployeeBenefit data is indirectly accessed, as shown in the following examples, because the queries are not targeting the EmployeeBenefit entity set.

  • Employees.Include("EmployeeBenefits")
  • Employees.Where(e => e.EmployeeBenefits.Salary > 100000)
  • Employees.OrderBy(e => e.EmployeeBenefits.Salary)

This means the end user would still be able to access or infer information about the EmployeeBenefit data that they should not have access to.

Filter Interception Method

In order to provide a means for LightSwitch developers to implement RLS, a new query interception method has been added to LightSwitch in Visual Studio 11 which allows developers to specify a filter predicate that is applied whenever an entity set is referenced.

The new interception method is available in the Write Code menu button on the table designer.  The following is an example of a filter query interception method that can now be written.

C#

partial void EmployeeBenefitsSet_Filter(ref Expression<Func<EmployeeBenefits, bool>> filter)
{
    filter = eb => eb.Employee.UserId == this.Application.User.Name;
}

VB

Private Sub EmployeeBenefitsSet_Filter(ByRef filter As Expression(Of Func(Of EmployeeBenefits, Boolean)))
    filter = Function(eb) eb.Employee.UserId Is Me.Application.User.Name
End Sub

If you are not familiar with the lambda expression syntax used to define the filter, you can read about it on MSDN – C# or VB

Example

Let’s take a look at a concrete example.  Within LightSwitch, I have defined an Employee table and EmployeeBenefits table with a 1 to 0..1 relationship between them.  The following two screen shots show the two tables.

image

image

Since these entities have a 1 to 0..1 relationship, I will also add logic within the Employee_Created interception method to create a new EmployeeBenefits whenever a new Employee is created.

C#

public partial class Employee
{
    partial void Employee_Created()
    {
        this.EmployeeBenefits = new EmployeeBenefits();
    }
}

VB

Public Class Employee
    Private Sub Employee_Created()
        Me.EmployeeBenefits = New EmployeeBenefits()
    End Sub
End Class

Now I will define an editable grid screen that displays the Employee table.image

Now if I publish this application, run it, and add some sample data I will see the following.

image

The original problem I wanted to solve was that all users should only be able to see their own EmployeeBenefit information.  I also want HR administrator users to be able to see everyone's benefit information.  In order to accomplish this, I will add a ViewAllEmployeeBenefitInformation permission.  Also note that I am using Forms authentication in this example.

image

Now I can write my custom logic in the Filter interception method for the EmployeeBenefitsSet.

image

C#

public partial class ApplicationDataService
{
    partial void EmployeeBenefitsSet_Filter(ref Expression<Func<EmployeeBenefits, bool>> filter)
    {
        if (!this.Application.User.HasPermission(Permissions.ViewAllEmployeeBenefitInformation))
        {
            filter = eb => eb.Employee.UserId == this.Application.User.Name;
        }
    }
}

VB

Public Class ApplicationDataService
    Private Sub EmployeeBenefitsSet_Filter(ByRef filter As Expression(Of Func(Of EmployeeBenefits, Boolean)))
        If Not Me.Application.User.HasPermission(Permissions.ViewAllEmployeeBenefitInformation) Then
            filter = Function(eb) eb.Employee.UserId Is Me.Application.User.Name
        End If
    End Sub
End Class

Now when I run the application and log in as a particular user, I will only see that users EmployeeBenefit information.

image

You can also see that if you sort on the Salary, the user cannot infer any information about who has the highest salary.  That is because any data that is filtered out is treated as null data when the query is processed.

image

To demonstrate how the ViewAllEmployeeBenefitInformation permission works, I will add a new HRAdministrator Role within the Roles Administration screen.

image

And I will give a user the ViewAllEmployeeBenefitInformation permission by adding them to the HRAdministrator Role.

image

Now if I log in as the John Deere, I will see all of the Employee Benefit information.

image

Additional Details

Entity set filters are only available for database data sources including the intrinsic/built-in data source and attached databases.  They are not available for attached SharePoint, OData, or Custom RIA data sources.  Although if you are using a Custom RIA data source, you can implement your own row level filtering within your RIA data source implementation.

The predicates defined within the Filter interception method may reference other entity sets. For example the filter for the EmployeeBenefits entity set I defined earlier references the Employee entity set.

C#

public partial class ApplicationDataService
{
    partial void EmployeeBenefitsSet_Filter(ref Expression<Func<EmployeeBenefits, bool>> filter)
    {
        if (!this.Application.User.HasPermission(Permissions.ViewAllEmployeeBenefitInformation))
        {
            filter = eb => eb.Employee.UserId == this.Application.User.Name;
        }
    }
}

VB

Public Class ApplicationDataService
    Private Sub EmployeeBenefitsSet_Filter(ByRef filter As Expression(Of Func(Of EmployeeBenefits, Boolean)))
        If Not Me.Application.User.HasPermission(Permissions.ViewAllEmployeeBenefitInformation) Then
            filter = Function(eb) eb.Employee.UserId Is Me.Application.User.Name
        End If
    End Sub
End Class

If I were to additionally define a Filter for the Employee entity set, it would not get applied to the Employee reference within the filter for the EmployeeBenefits entity set.  The primary reason for this behavior is because sometimes the filter needs to access data the end user should not have access to in order to determine if the end user should have access to the data.

Conclusion

With LightSwitch in Visual Studio 11 you can define filters on your data and those will now be applied across any query that accesses that data, even via an association. This allows you to easily implement multi-tenant database applications or other applications where only certain rows should be displayed to the user.

-Michael Simons, Visual Studio Team

Leave a Comment
  • Please add 4 and 8 and type the answer here:
  • Post
  • Epic! Thanks for this great wrap-up on the new filters-feature! :-)

  • Great article. Thanks so much for assisting us to be better programmer.

  • Great article. I've started a multi-tenant app using this technique. What is best practice to restrict ownership to fine grained instances in a tree of relationships where the owner is currently at the root?

    partial void Transaction_Filter( ref Expression<Func<Transaction, bool>> filter )

    {  // Walk up the parent entities to the owner

      filter = e => e.Position.Account.Portfolio.Customer.UserName == Application.User.Name;

    }

    This should work but I'm not sure if it is the proper way. Or should I redundantly define Transaction.Customer, etc..., for direct access at a finer level?

    Suggestions and tradeoffs appreciated

  • Hi tex tai,

    There is nothing wrong with your approach in general.  However as you already hinted at, there can be performance issues that may arise with this approach depending on the data size, how these relationships are defined, the complexity of the Transaction queries your application issues, etc.  Certainly defining the "shortcut" relationship from Transaction to Customer would help performance but may not be necessary considering how your application is built/used.  Defining this additional relationship does add more complexity to your application as you would need to keep it in sync with the primary relationship during inserts, updates and deletes.

    Hope that helps.

    -Michael

  • Thanks Michael for the sanity check. I will likely hang a Customer reference off of the Position too. Syncing it should be easy. I assume that accessing all this data from OData exposure doesn't change the issues as OData is layered on top?

  • Hi tex tai,

    Your OData assumption is correct.

    -Michael

  • I there a way to dynmically build the filter? I tried predicatebuilder, but it doesn't work.

  • Bruce,

    You can use the System.Linq.Expressions namespace to build up an Expression that represents your filter.  And then you can pass that Expression into "Expression.Lambda<Func<T,bool>>(expression)".

    So for example (in C#) you can say:

    partial void Employees_Filter(ref Expression<Func<Employee, bool>> filter)

    {

           string currentUserName = this.Application.User.Name;

           ParameterExpression param = Expression.Parameter(typeof(Employee), "e");

           Expression filterExpression = Expression.Equal(

               Expression.Property(param, "UserId"),

               Expression.Constant(currentUserName));

           filter = Expression.Lambda<Func<Employee, bool>>(filterExpression, param);

    }

    You can use the Expressions namespace to build up any predicate using "Or" and "AndAlso", etc.  See msdn.microsoft.com/.../system.linq.expressions.aspx for more information.

    Eric

  • Great article, thank you Michael.

    After settings the new filter interception method, would you also keep the former _All_PreprocessQuery query filter  as belt and braces ; or is that just redundant and would you suggest removing the legacy filter? Do both of them cover exactly the same surface? Is _All_PreprocessQuery just for backward compatibility, or does it keep its own use cases?

    Sorry to post the same question in 3 different flavors and thanks to share any usefull comment on this.

  • BravoSierra,

    The EntitySet filter is a superset of the EntitySet _All_PreprocessQuery functionality.  So if you copied your preprocess logic to the filter method there is no need to leave the preprocess logic.  In fact leaving the preprocess logic will add overhead to the system.  The EntitySet _All_PreprocessQuery functionality was left in place purely for backwords compatibility.

    -Michael

  • Great article !!!  If I create a filter, does it get applied each time a new query for the table is written too?  I'm fairly new to Lightswitch and I think I created unnecessary queries to "simulate" some of this.  I'm trying to figure what if anything I could back out and re-do the "right way".

  • @JDStClair - Yes the filter is applied anytime the data is retrieved from the data source. So when you create a query, it's applied in addition to the filter.

    Cheers,

    -Beth

  • I am new to LS and late to this conversation, however i have to filter data on my tables. In your example, you use username however in my project i have to filter data based on a list of locations the person is permitted to view. How would you advice me in getting this done. I can write my linq query and get the list of locations, but i do not know where and how to implement it. I created the list in Application_LoggedIn() method, now i need to past that list to my server project to do the filter, how do i get this done?

  • Dinesh,

    How is the location information stored/retrieved.  Is it possible to do a join from within the filter method?  Alternatively if you have the list of locations you can utilize the linq operators as shown in the following example.

    In this example Customer is an entity with a string Location field.

    partial void Customers_Filter(ref Expression<Func<Customer, bool>> filter)

    {

       IEnumerable<string> permittedLocations = this.GetPermittedLocations();

       filter = customer => permittedLocations.Any(location => customer.Location == location);

    }

    private IEnumerable<string> GetPermittedLocations()

    {

       // TODO:  Write logic to retrieve the Locations for the current user.

       yield return "SouthWest";

       yield return "Central";

    }

Page 1 of 1 (14 items)