SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Row-level security for users belonging to multiple AD groups?


Row-level security for users belonging to multiple AD groups?

Author
Message
MB-400824
MB-400824
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 177
Hi,

I am new to RLS, and I am trying to solve the following problem: We have fact tables that have multiple client data. We also have users that can have access to more than one client. We would like to use AD groups. I am trying to see if we can use RLS. A use case would be:
Client Month DataType
ABC Jan M
ABC Feb M
XYZ Jan R
XYZ Jan M
SSS Feb M

User1 should be able to see clients ABC and SSS
User2 should be able to see clients XYZ and SSS

From my reading on RLS so far that might not be possible. I have created User1 and added them to two different AD groups, so what ends up happening is that they only see SSS client records. I assume it is because the FUNCTION always will return just 1 as result. Or am I not being creative enough with my WHERE clause in the FUNCTION.

Thanks in advance!
Evgeny Garaev
Evgeny Garaev
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4428 Visits: 1530
I think that is easier on the application side. Also you may consider using encryption for sensitive data instead of RLS. https://docs.microsoft.com/en-us/sql/t-sql/functions/decryptbykey-transact-sql
MB-400824
MB-400824
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 177
Thank you. I am afraid that I cannot use the application side for this. Plus MS advertises RLS as easier to do than on the application side. I was looking at the decryptbykey, but that seems to be done at the field level. My need is to isolate all fields for the client in the Fact table, not only one.
MB-400824
MB-400824
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 177
I was able to find out the answer to this. As I said, I am new to RLS and all the examples that I was seeing where for the most part User1 being part of only one AD group and seeing all the records for the given criteria.

In my particular case, the issue was my coding the function incorrectly. The client column is actually an integer and the AD group name has leading zeros when the client is less than 5 characters long. I had forgotten to pad with 0s when dynamically concatenating the client id to the AD group name, i.e. This Users_00123 means something different than Users_123, so this failed as a check: IS_MEMBER('Users_123') = 1. Once I started padding with the leading 0s, that check was successful and all worked fine.

Evgeny, thanks for the feedback!
Steve Jones
Steve Jones
SSC Guru
SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)

Group: Administrators
Points: 583355 Visits: 20885
Can I ask how you're coding the function? Are you hard coding the AD names in there? I'd think you'd want these as data somehow linked to the user and data, but I'm curious what you've done.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
MB-400824
MB-400824
SSChasing Mays
SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)SSChasing Mays (630 reputation)

Group: General Forum Members
Points: 630 Visits: 177
Sure. In my situation, each user has direct access to the data via SSMS.
Here is my function, I have replaced our actual domain, with the word 'domain' below, but all the rest is as is:

CREATE FUNCTION RLS.clientAccessPredicate (@ClientId INT)
RETURNS TABLE
WITH SCHEMABINDING
AS

RETURN SELECT 1 AS accessResult
WHERE IS_MEMBER('domain\Users_' + RIGHT('00000'+ CAST(@ClientId AS VARCHAR(5)),5)) = 1
OR IS_MEMBER('domain\DEV_ADMIN') = 1;
GO

It is working as intended, but I have been testing performance since I posted this question and I am finding that on the larger tables 1 million + records, that rls is very slow. And aggregation is close to impossible. I am trying to follow all the best practice advise, but still not great results.
Steve Jones
Steve Jones
SSC Guru
SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)

Group: Administrators
Points: 583355 Visits: 20885
Is it the same if you use an item in a table? I'm wondering if there are issues calling out to AD here with performance. I'd like to think this runs once, but ...

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Karim Diouf
Karim Diouf
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 101
I believe your performance issue is related to the following OR statement:
OR IS_MEMBER('domain\DEV_ADMIN') = 1;

An option is the use disjunction in the predicate. Please look at that:
https://blogs.msdn.microsoft.com/sqlsecurity/2015/03/16/row-level-security-for-middle-tier-apps-using-disjunctions-in-the-predicate/

Steve Jones
Steve Jones
SSC Guru
SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)SSC Guru (583K reputation)

Group: Administrators
Points: 583355 Visits: 20885
That might help. Maybe use a CASE and return a 1 when IS_MEMBER() works in either case, or nothing otherwise.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Karim Diouf
Karim Diouf
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 101
I'm not sure adding a CASE which will be evaluated on every single row of your 1 millions rows will improve the performance.

Options here are:
- Use disjunctions in the predicate as I mentioned before
- Use an intermediate table (a bridge table) - which holds the distinct associations (Users --> Customers). You would then apply the predicate on that bridge table. And finally join with your fact tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search