How to implement database access control on row basis

Question

Hi,

I'm currently developing a small business database application for which we plan to go towards multi-user access in the next time.

The database mainly contains projects (in a project table) with a couple of joined tables containing additional information.

One requirement of our customers regarding multi-user operations will be a fine-grained access control mainly based on project level (i.e. users only have access to some projects). I'm wondering how to implement this.

What makes the situation a bit more difficult is that database access mainly happens in a self-developed persistence layer which construct the needed SQL queries (resembling to nhibernate).

The only solution I could come up with is to implement stored procedures (or views for read access?) inside the database which implement the access checks. Since our persistence layer currently relies on full access to tables this would mean to implement a read view, one insert and one delete command for each table and to change the persistence layer in order to use these commands (instead of constructing INSERT/DELETE queries).

I'm wondering if there's any other solution not requiring to change our code...

Answer 1

This sort of thing can be challenging to get right. Among other things, requirements like this are a reason to build on top of a packaged system - they will already have fixed bugs you haven't written yet.

If you have to do it on your own, then I recommend you concentrate on getting the security aspect right, and worry about changing your code later. You wouldn't want to compromise security because of a desire to not change your code.

You might be able to arrange for a single additional piece of information to be passed into the stored procedures. Depending on your SQL Server version this could be a piece of XML, or a table-valued parameter. It would include all of the information necessary to determine the access that the user should have. Maybe just a user id, but who knows?

You'd want to create views and/or table-valued functions that used this information to filter the set of rows returned based on access.

Answer 2

One simple (but crude) approach is to create a VIEW for each group of users using the WITH CHECK OPTION. You may also need INSTEAD OF triggers on those VIEWs to allow more complex logic for INSERT, UPDATE and DELETE operations.