I think this is more a question of better techniques and design than anything else. I tried to find similar queries about this, but could not find them. I really found Row Level Security with the Entity Framework , but I think the context here is a little different.
First I will try to explain my scenario:
I have a .net 3.5 website that uses a shared business library to access my SQL 2008 databases through NHibernate. All code is C #, with NHibernate 2.1. My WebSite displays a lot of different IList coming from a business library, the business layer gets all the data from SQL through NHibernate. So, for example, I could have a method that returns an IList, another that returns an IList, another IList, etc. .... The fact is that the active user will have access to only part of all returns (almost all types of results should be filtered out of security), so I needed to implement a “data filter” in the library, which would return only the allowed row data in WebSite. For this, the IPRincipal from my site is used in the Library, so I can get user data to filter the data, but since our security model is very complex, distributing it through all our methods will create huge maintenance problems. Thus, to solve this problem, we created a couple of SQL SPs that return the allowed elements for the current user and business logic, we just need to join the requested data with security data, and we have the final set of results to send to the user. Currently, this data merging process uses Linq for objects, where I join iList with a (security) list to return only the allowed result set. IList coming from NHibernate in various ways can be GetAll (), ICriteria.List () or IQuery.List (), or even NamedQuery.List (), and security data always comes from one of two NamedQuery.List (). I also plan to execute threads to execute SQL calls at the same time, and after thread.join () performed LINQ joining on both ILists. I added a sample example below to illustrate how the method should execute.
The second option, and this is what we were trying to leave, is to implement Join on the SQL side, leaving us with the whole challenge, should come from SQL SP, which will connect to the security results and not allow business code to get full 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: is this really a terrible approach / practice for line-level security (please keep in mind that our security model is really complex and customizable - and this is a business design), or are we moving in the right direction? Any other options we could go for?
Thanks in advance, Claiton