I'm working on a SQL Server 2012 database where the entity
[scenario] has a One-To-Many relationship with a
[client] table. The users working with this database might have access to only a subset of clients and for each client might have different type of read/write/delete permission. Hence the 2-dimension row level security. Below is an example of the type of design I'm thinking of:
User Client Read Write Delete user1 no_client True True False user1 client1 True False False user1 client2 True True True user2 no_client True True True user2 client1 True True True user2 client2 True True True
I have managed to implement this kind of design based on the SQL Server Label Security toolkit and its white paper. My implementation of the toolkit relies on the creation of three different roles for each client (i.e.
To me it does not seem a very good design given that the database might contains thousands of clients. Furthermore, my understanding is that this toolkit was based on the assumption that the set of label is fully known in advance. Extending the row security to new security labels is quite cumbersome.
Is there a better way to implement this kind of 2-dimension row level security?
EDIT: I think I might have found a decent solution. Here what I did:
- Created a table that maps the users to the client id for each he/she has some sort of permission, with an additional field that specifies which kind of permission ('Read', 'Write' and 'Delete');
- Implement the row level access through a view that filter the rows of the main scenario table using the table created above;
- Prevent the insert/update/delete actions based on after insert triggers;