I'm looking for some opinions on implementing row level access control to a SQL Server confidential data table.
Only certain rows can be accessed (select, update, delete....) by certain users.
I've searched around and so far what I have found points to the following scheme:
- define users, groups of users, roles with actions, and row membership categories, in an access control table
- add a membership category column to the confidential data table, and populate it with content that specifies what access control membership category it belongs to
- allow users to use stored procedures (probably via a web front end) to access the data they are entitled to, using a view with a where clause. The where clause would check the user id against the access control table to see what row membership
categories that user was entitled to access, and use this to restrict the rows being returned.
So to summarise the requirement, there are many rows in the table. There are many users. Some users can access all rows. Some users can only access rows that fall into a particular category. The category is stored with each row.
Any comments appreciated....
View Complete Post