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

Question

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

MB-400824

SSC Eights!

Points: 836
More actions
February 1, 2018 at 2:13 pm

#343267

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!

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Evgeny Garaev SSCertifiable Points: 7428 More actions · Answer 1

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 SSC Eights! Points: 836 More actions · Answer 2

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 SSC Eights! Points: 836 More actions · Answer 3

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 - SSC Editor SSC Guru Points: 736270 More actions · Answer 4

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.

MB-400824 SSC Eights! Points: 836 More actions · Answer 5

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 - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

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 ...

karim.diouf SSC Enthusiast Points: 152 More actions · Answer 7

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 - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

That might help. Maybe use a CASE and return a 1 when IS_MEMBER() works in either case, or nothing otherwise.

karim.diouf SSC Enthusiast Points: 152 More actions · Answer 9

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