1

I am working on a project that requires custom granular security/privacy settings. Basically I need to be able to restrict access to data based on the user executing the query. Easy enough, right? Here a couple of gotchyas though..

  1. The users must be stored in a users table. Creating Windows user accounts, SQL server users or SQL server roles is unacceptable for this application.
  2. The security criteria has to be defined by any number of sources, i.e. a user's location, pay grade, specialty, etc..

Right now we're implementing this by generating a SQL "where" clause and appending it to the end of every query before it's executed. This restricts us from being able to leverage newer technologies such as Entity Framework and also carries with it its own other limitations and performance problems.

Any help or ideas would be greatly appreciated. Please let me know if additional clarification is needed.

Thanks! Jason

Jason
  • 565
  • 1
  • 6
  • 18

1 Answers1

1

You can still use Entity Framework. There are a couple of ways to do this with EF:

  • Create a custom wrapping provider that injects WHERE clauses, like this.

  • Only expose IQueryables that contain pre-Where()'d data (but beware of relations)

  • Throw an exception in the entity ctor if the current user doesn't have access to it (may not be a good idea)

SLaks
  • 868,454
  • 176
  • 1,908
  • 1,964
  • Although that seems like the solution I'm looking for I do have a couple questions.. I'm not sure if I'm just following the code incorrectly or not but I can't seem to figure out exactly where I'd be injecting the `where` clause. Could you possibly post a snippet that leads me in the right direction? – Jason Nov 07 '11 at 01:48
  • For creating a custom wrapping provider that injects the `WHERE` clause – Jason Nov 07 '11 at 13:02
  • If I'm following the code correctly it seems pretty complex to get the `WHERE` clause injected into the query? – Jason Nov 07 '11 at 13:41