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

  • 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!

  • 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

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

  • 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!

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

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

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

  • 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:

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

  • 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

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

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