Filtering Data using Entity Set Filters (Michael Simons)

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