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. client1Read, client1Write and client1Delete).
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:

  1. 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');
  2. Implement the row level access through a view that filter the rows of the main scenario table using the table created above;
  3. Prevent the insert/update/delete actions based on after insert triggers;
  • 1
    Any chance you could upgrade to SQL Server 2016 or higher? – Tom V Mar 14 '18 at 14:06
  • 1
    I am aware that SQL Server 2016 has introduced Row Level Security features, but being in a company environment it's not possible to upgrade at the moment. – gmusto Mar 14 '18 at 14:54

Your Answer

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

Browse other questions tagged or ask your own question.