Implementing Row-Level Security – (SPs vs LINQ to Objects)

Question

Hi all,

I believe this is more a question about best practices and design than anything else. I tried searching for similar queries regarding this but couldn’t find any. I actually found the Row Level Security with Entity Framework but I believe the context here is a bit different.

I will try to explain my scenario first:

I have a .net 3.5 WebSite that uses a common Business Library to access my SQL 2008 Databases via NHibernate. All code is C#, with NHibernate 2.1. My WebSite displays loads of different ILists coming from the Business Library, the business layer gets all data from SQL via NHibernate. So, for i.e. I could have a method that returns IList another that returns IList, another IList, etc… The point is that the active user would have access only to part of all the returns (almost all types of resultsets must be filtered from security), so I needed to implement a “data filter” on the library that would return only the allowed data rows to the WebSite. To accomplish this the IPrincipal from my website is used on the Library so I can get the User details to filter the data, but as our security model is highly complex spreading it over all our methods would create huge maintenance problems. So to solve this we have created a couple SQL SPs that return the allowed items for the current User and on the Business logic we just need to Join the requested data with the security data and we have the final resultset to send to the user. Right now this process of joining the data is using Linq to Objects where I Join an iList with a List (security) to only return the allowed resultset. The IList its coming from NHibernate by different ways, could be by a GetAll() method, an ICriteria.List() or IQuery.List() or even a NamedQuery.List(), and the Security data is always coming from one of two NamedQuery.List(). I am also planning to also implement threading to allow both SQL calls simultaneous and after thread.join() execute the LINQ join on both ILists. I added a sample code bellow to illustrate how a method would need to be executed.

The second option, and this is what we tried to get away off is to implement the Join on the SQL side leaving us with all call must come from SQL SPs that would do the join on the security results and not allowing the business code to get the complete use of NHibernate functionality.

public IList<Product> GetAllByName(string FirstLetter) {
    ICriteria GetAllCriteria = this.session.GetISession().CreateCriteria(typeof(Product));
    GetAllCriteria.Add(NHibernate.Criterion.Restrictions.Like("ProductName", FirstLetter));
    GetAllCriteria.AddOrder(NHibernate.Criterion.Order.Asc("ProductName"));

    // Here would go the Threading for the both calls
    IList<Guid> AllowedItems = SecurityBase.GetAllowedItemsForCurrentUser();
    IList<Product> AllProducts = GetAllCriteria.List<Product>();

    var ResultSet = from Prod in AllProducts
                    join Sec in AllowedItems on Prod.Id equals Sec
                    select Prod;

    return ResultSet.ToList<Product>();
}

Now My question, Is this a terrible approach/practice for Row-Level Security (please keep in mind that our security model is really complex and customizable – and that is by business design) , or are we moving on the right direction? Any other options we could go for?

Thanks in advance, Claiton

Answer 1

As an option, have you considered that instead of using the SQL SP to return the list of allowable items you use the same logic / join inside of a view and only queried the views / only provided permission to the view and not the underlying table.

Answer 2

I am currently working on a project with similar requirements. It is a green field project, no code has been written yet, we are just looking for a solution to row based security.

I would recommend you these two articles:

http://www.codeproject.com/KB/database/Modeling%5FDAGs%5Fon%5FSQL%5FDBs.aspx http://www.codeproject.com/KB/database/AFCAS.aspx

With regards to your threading idea, using System.Threading will give you grief, have a look at Ritcher's AsyncEnumerator or Microsoft Concurrency runtime (CCR)

Answer 3

I know this is a little late but have you taken a look at the following:

http://securedata.codeplex.com

This is an open source project I am the lead developer for and implements row level security seamlessly.