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)
-
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)
-
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?
/Sharath
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:
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.
5