Row Level Security with multiple users for one row

Question

I'm trying to implement Row Level Security in SQL Server 2016.

The problem is, I can have multiple users that should have read permissions over given rows, and when I write some complex condition in the predicate the performance gets like very very very bad.

I tried to keep all usernames in one column of the table and in the predicate to search through them for the SYSTEM_USER with % LIKE % but performance is low.

Example of the values in the Usernames column in my controlled table for one row:

domain\john.wick;domain\red.eagle;domain\spartak.something....

Here is my function:

CREATE FUNCTION fn_securitypredicate(@Usernames AS nvarchar(4000))  
  RETURNS TABLE  
WITH SCHEMABINDING  
AS  
  RETURN
  SELECT 1 as Result
  WHERE @Usernames LIKE '%' + SYSTEM_USER + '%'

With this execution time from 2 sec became 50 sec. Any suggestions for improvement.

CREATE SECURITY POLICY [Policy]   
ADD FILTER PREDICATE [fn_securitypredicate]([Usernames])   
ON [dbo].[Products];

1. Please post here your code 2. What performance can be achieved with like %...%? Why don't you search for exact match? — sepupic, Oct 02 '17 at 18:01
Ok, my question still remains, why don't you use the exact match? WHERE @Usernames=SYSTEM_USER' — sepupic, Oct 03 '17 at 12:33
Because there are multiple users for each row, and I can't use exact match.... otherwise that would be the most optimal solution. The example i gave is for one row, ";" separated values for each row in the column Usernames — Stefan Taseski, Oct 03 '17 at 14:14
If you want to speed up your code, you should normalize your table. When you have only one user in the row, index can be used. When you use the row with multiple users and like %...%, no index can help you. Can you group your users in a roles/windows groups and have one role/group per row? — sepupic, Oct 03 '17 at 18:47

score 1 · Accepted Answer · answered Oct 02 '17 at 18:19

This is the solution I came up with for my previous team.

This requires a a users table, a users permissions table as well as a permission column on your controlled table. It should also have a user group and user group permissions table to scale with users.

users                   user_permissions            controlled_table
+-----------+---------+ +---------+---------------+ +---------------+------+------+
| user_name | user_id | | user_id | permission_id | | permission_id | pk_1 | pk_2 |
+-----------+---------+ +---------+---------------+ +---------------+------+------+
| admin     |       1 | |       1 |             0 | |             2 |    1 |    1 |
| user1     |       2 | |       2 |             1 | |             2 |    1 |    2 |
| user2     |       3 | |       2 |             2 | |             3 |    1 |    3 |
| user3     |       4 | |       2 |             3 | |             4 |    2 |    1 |
|           |         | |       2 |             4 | |             3 |    2 |    2 |
|           |         | |       3 |             1 | |             1 |    2 |    3 |
|           |         | |       3 |             2 | |             1 |    3 |    1 |
|           |         | |       4 |             2 | |             5 |    3 |    2 |
|           |         | |       4 |             3 | |             4 |    3 |    3 |
|           |         | |       4 |             4 | |             2 |    4 |    1 |
|           |         | |         |               | |             3 |    4 |    2 |
|           |         | |         |               | |             3 |    4 |    3 |
+-----------+---------+ +---------+---------------+ +---------------+------+------+

For performance, you will want to add the permission_id to whatever index you were using to search the controlled table. This will allow you to join permissions on the index while searching on the remaining columns. You should view the execution plan for specific details on your indexes.

Yea but that means that for each row in the controlled table there's gonna be a select on some other table. It's like doing INNER JOIN. It's slowing the process way too much. In the stored procedures that i use, there are a lot of aggregations and if the normal execution of the procedure is 2 sec, with the way you suggest it became 3 minutes and 50 seconds ... — Stefan Taseski, Oct 03 '17 at 12:16

Row Level Security with multiple users for one row

1 Answers1