Per-row permissions based security RRS feed

  • Question

  • Is it possible to access the entity data being 'evaluated' in the QueryInterceptor?  I'm leaning towards no, as I'm understanding the QueryInterceptor as only being in the query-creation pipeline rather than any data pipeline.  I require a way to read the data before it is decided whether it should be returned or not.

    We intend to implement a permission system that dictates which users can access what data at a very fine level of granularity.  Part of this system includes a simulated filesystem with parent/child permissions and permissions inheritance.  These permissions would exist in a table in our database.  We would like to enforce these permissions in our Data Service.  Our goal is to provide regulated access to our data for third party integration with our platform, while not relying on the third party to properly enforce permissions in their applications.

    To do this, we need to be able to know what data is being requested so that we can look up the entity in our permissions table.

    We're peachy with things in the ChangeInterceptor, as we have access to the entity through the parmeter provided in the declaration.  So we could implement the create/write/delete permissions just fine - but read permissions are the issue.

    Is there *any* way to access the entity data that the QueryInterceptor is acting on in the QueryInterceptor?
    Saturday, November 8, 2008 5:33 PM


  • Hi Nick. After looking it more, I have not been able to find a way to call a SP from the Expression in the interceptor.


    A option could be to use a service operation instead for your results with filtered permissions.  Not ideal, but at least you can do most anything in the service operation.  If anyone thinks of anything else, please post.

    Monday, November 10, 2008 2:54 PM

All replies

  • In so much as you can reduce your test to a "where" predicate, you should be able to do what you need.  In effect, you are inspecting each entry with your "where".  AFAICT, you build and return a where predicate that gets appended to the user's original query.


    Code Snippet


    public Expression<Func<Entries, bool>> EntriesInterceptor()


              // Some general logic if required.

    if (!HttpContext.Current.User.IsInRole("Admins")) return (e=>false);


    // Use any valid *Store expression.

    return (e => e.Users1 !=null && e.Users1.SecurityPermissions.Any());




    If users query comes over logically as:


    Code Snippet

    from e in db.Entries

    where e.Users.Name == "joe"

    select e;



    Then is becomes below after the Interceptor above:


    Code Snippet

    from e in db.Entries

    where e.Users.Name == "joe"

    where e.User1 != null && e.Users1.SecurityPermissions.Any(//some logic)

    select e;



    For debugging, one thing that would be nice is to be able to inspect the final "combined" query and the results at the server side before return.




    Saturday, November 8, 2008 7:38 PM
  • I am brand new to the Entity Data Model, so I'm not sure I understand your reply entirely.  I understand you are suggesting that I could apply the logic (or only relationships?) in the EDM, and test for the permissions using the e.Username.SecurityPermissions.Read or similar?  I've tried searching to find the significance of the "Any" but being such a widely used word, I'm not making any headway in finding meaningful information.

    To elaborate further, the inheritance functionality requires extensive logic be executed to determine proper permissions.  The permissions table is not a 1:1 for the Files table - that is, not all Files will have a permissions entry in the permissions table ( that's the entire point).  Instead, if no permissions entry is found for the File in the permissions table, that means the file is inheriting its permissions from its parent.  If this is the case, we must find its parent and check its permissions.
    Saturday, November 8, 2008 8:05 PM
  • From your description, it still sounds like it could be possible using (maybe complex) linq statement.  Do you have a linq query that shows the logic already?  If possible please post.  Or post a simplier example that would get you started.  The limitation is the query has to "translate" into a valid store/sql statement that gets shipped and run at the server, so you can't use arbitratry functions or code, but you may be able to also use SP, but have not tried that.


    btw - the Any operator is just one of the standard operators you can use to create your edm queries (not astoria queries as all operators not supported).


    Saturday, November 8, 2008 8:24 PM
  • I'm still in the "can it even do this?" stage, picking the most complex problems that I expect to encounter and ensuring all architectures I plan to use can solve them.  I do not have any linq queries written, and can't think of anything simpler that would still ensure Data Services w/ Entity Framework are capable of solving this.

    If we can use stored procedures, like you've suggested, then I'm almost certain the goal can be achieved.  I hadn't even considered stored procedures.  Thank you for the link, I was getting quite frustrated trying to find information on that Any operator.

    Does anyone else have any ideas?  If I can get all of the security enforced through the data service I'll be ecstatic.
    Saturday, November 8, 2008 9:00 PM
  • Not knowing your specifics, can't say for sure.  However, I have a similar model in my current project where entries can have security permissions by User and by Group.  I am able to do the following with this model.  So your checks can get fairly complex as needed.


    Code Snippet


    public Expression<Func<Entries, bool>> EntriesInterceptor()


    string tag = WhoAmI();

    // Return Entry if:

    // 1) Entry is not private (i.e. public).

    // 2) Entry is owned by user (i.e. tag==currentUser).

    // 3) Entry was sent to the user.

    // 4) User has Read permission to the entry.

    // 5) User has Read permission as member of a security group.

    // Note: Permissions are applied to Folders and folder items. Groups and/or users can be given permissions.

    return (

    e => e.IsPrivate == false

    || e.Users.Tag == tag

    || e.Users1.Tag == tag

    || e.Users.SecurityPermissions.Any(sp=> sp.Read==true && sp.Folders.Entries.EntryID==e.EntryID)

    || e.Users.SecurityGroups.Any(sg=>sg.SecurityPermissions.Any(sp=>sp.Read && sp.Folders.Entries.EntryID == e.EntryID)));




    Sunday, November 9, 2008 6:48 AM
  • After some more studying, I do see how you can reduce your situation down to the where predicate.  If I did not need inheritance, I could achieve my goal in the exact same manner that you have.  My first thought is that the Stored Procedure will need to know which Entry it would be dealing with.  From the looks of the FunctionImport Element, it looks like the parameter (e.g. the EntryID) could be generated based on what particular Entry entity I am dealing with.  Am I correct in this assumption?

    I'm not entirely clear where the Stored Procedure would fit in the predicate, however.  The Stored Procedure would return all permissions for the given EntryID (or its parent, if that was the case) and UserID, so would my Stored Procedure assume the place of "SecurityPermissions" in your predicate?

    Also, I am confused by your Users1 entity.  What is that?
    Sunday, November 9, 2008 1:29 PM
  • Hi Nick. After looking it more, I have not been able to find a way to call a SP from the Expression in the interceptor.


    A option could be to use a service operation instead for your results with filtered permissions.  Not ideal, but at least you can do most anything in the service operation.  If anyone thinks of anything else, please post.

    Monday, November 10, 2008 2:54 PM
  • In the context of Northwind... I have a simple sproc called GetOrdersByCustID and have created a FunctionImport specifying that it returns Orders:

    Code Snippet

    ALTER PROCEDURE dbo.GetOrdersByCustID(@CustomerID as char(5))
    SELECT * FROM Orders AS o WHERE o.CustomerID = @CustomerID

    It looks like it returns the sproc results as an ObjectResult Of NorthwindModel.Orders, but I can not tack on a .Any and see if a particular Order is in the resulting collection, or return anything at all if the sproc is in the return.  e.g. I try:

    Code Snippet

    <QueryInterceptor("Customers")> _
    Public Function InterceptCustomers() As Expression(Of Func(Of Customers, Boolean))
    Return Function(Customer) CurrentDataSource.GetOrdersByCustID(Customer.CustomerID).Any(Function(Order) Order.OrderID = 10248)
    End Function

    This should return any users who have an order with the OrderID of 10248, which should return a single user with CustomerID VINET.  btw, the Function(n) n.... is VB's Lamba syntax.

    And it gives me an exception:

    Code Snippet

    LINQ to Entities does not recognize the method
    GetOrdersByCustID(System.String)' method, and this method cannot be
    translated into a store expression.

    I gather that anything in the Return must be translated into an expression, so apparently stored procedures in the QueryInterceptor aren't a possibility. Upon further thought, even if it didn't have to convert them, it would mean executing the sproc for every record returned. So yea, sprocs that filter the results from a QueryInterceptor are just not possible.

    I will work more on my model and see if I can't enforce the permissions without the need for conditional logic.

    Monday, November 10, 2008 10:46 PM

    We can use service operations to do most any kind of processing and calling sprocs or tsql like:


    public IEnumerable<Users> GetUsersSet(int userID)


    // Call Uri:http://localhost:7777/MyService.svc/GetUsersSet?userID=2

    // Call a sproc using EFExtentions library. http://code.msdn.microsoft.com/EFExtensions

    // Blog post http://blogs.msdn.com/meek/archive/2008/03/26/ado-entity-framework-stored-procedure-customization.aspx

    var results = this.CurrentDataSource

    .CreateStoreCommand("GetUsers", CommandType.StoredProcedure, new SqlParameter("userID", userID))


    return results;



    This gets us closer as we can limit all records to just those the user has access to. It also allows us to return only the columns we need (i.e. column filtering) and still create an object.  On the other hand, it does not behave like a property.


    "EntitySet" in the EFExtensions looks interesting as you can create your own alternate endpoints for collections (and use sprocs in them) in your ObjectContext, but I can't get astoria to recognize the new collection property. 

    Tuesday, November 11, 2008 5:45 AM
  • William, thank you very much for all the help you've provided.  I have bookmarked the EFX stuff and will continue to read up on ServiceOperations.  As it stands now, I believe I am going to go the route of maintaining an association with the "File" and its permissions parent so that I can have my cake and eat it to.  I've learned a lot about why things don't work and what is going on under the hood over the past few days.  Without you prompting me to look into sprocs and the subsequent discovery of why it doesn't work, I would still be much in the dark about what is going on.  Thank you!
    Tuesday, November 11, 2008 7:53 AM