I need to model a database design to provide item level permissions to users based on the user's role access. I am using asp.net with sql server 2012 I am looking at similar experience on how SharePoint provides the security trimming when we access the resources with a SharePoint site (including search results)

  1. Is it a best practice to model this in the database or the application programming? (I have seen SharePoint implement this, but haven't gone in to figuring out how they have done it)

  2. I will also be using lucene.net for integrating with search. So even the search results should be security trimmed.

Are there any best practices/resources available to suggest how to model this permission?



Item level permissions will require both database modelling and application enforcement.

Essentially, You'll want to create an "item security" table that relates the RowIDs and PermissionIDs to each other. The application will then have to determine if the current user has the right permissions to view the item that they requested. The database can also leverage stored procedures to filter the rows returned for a particular table based on the permissions.

This kind of system can easily get complex very quickly. Make sure you plan ahead and do plenty of modelling and use-case testing of the model before moving forward.

Also, if SharePoint is a good fit for this application, you may consider just using SharePoint instead of reinventing the wheel.

EDIT: I had some free time, so I modeled up a schema that I've used in the past for this type of scenario: Data Schema for Item Permissions

You would want to check the user's permissions against the item they request on your site in your application code. If the user's permissions don't match, they don't have access. You may also want to implement some form of "admin" access where if a user has the "admin" permission they can access all items.

You can pass the user's ID in any stored procedures you use to check their permissions and to filter list results to only the items they have permission to access.

  • Thanks. The relational modelling will work when querying data directly. However since I am planning to use search extensively within the application the search results also need to be security trimmed. I am intending to use normal SQL for search, instead use either solr or elastic search. I am evaluating lucenetutorial.com/techniques/permission-filtering.html. – Sharath Chandra Jul 8 '14 at 11:40
  • This answer is still quite relevant to searching. The link you sent me says exactly what I said: You'll need adjust the query to compare the user's permission levels to the item permission levels. That search plugin lets you rewrite portions of the query, so make it only grab the items that match what the user has permissions for. – Rephistorch Jul 8 '14 at 13:16
  • I'm not 100% sure what architecture you'll be using for storing the database, but most systems allow for stored procedures and/or views. That would help you filter the search results as well. – Rephistorch Jul 8 '14 at 13:30
  • no disputes on the solution you have provided. Stored procedures and views will def work. This will not be a scalable solution say when we are doing free text search or tag based filtering (the results of which needs to be security trimmed) – Sharath Chandra Jul 8 '14 at 18:34
  • I'm curious, why wouldn't a text search on the ItemData field be filterable? You could still only join in items that the user has permissions for. – Rephistorch Jul 8 '14 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.