Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Dynamic row level security not working on service

Hi All I really need help before I need to put a static process in place. I have spent days researching trying to get it to work. 

 

Link to test file: Test data file

 

I have a test data table that looks like this with sensitive survey data (cut down version):

 

ResponsesSurveysShort_QuestionYearTerm_AllFaculty_School_CleanCourseCode_Description
2010Q120202020 SummerFaculty 4School 4Course 1
2010Q220202020 SummerFaculty 4School 4Course 1
2010Q320202020 SummerFaculty 4School 4Course 1
2010Q420202020 SummerFaculty 4School 4Course 1
2010Q520202020 SummerFaculty 4School 4Course 1
1012Q420202020 SummerFaculty 1School 1Course 2
1012Q520202020 SummerFaculty 1School 1Course 2
30060Q120202020 SummerFaculty 3School 2Course 3
30060Q220202020 SummerFaculty 3School 2Course 3
30060Q420202020 SummerFaculty 3School 2Course 3
30060Q520202020 SummerFaculty 3School 2Course 3
59029Q120202020 SummerFaculty 2School 5Course 4
59029Q220202020 SummerFaculty 2School 5Course 4
59029Q320202020 SummerFaculty 2School 5Course 4
59029Q520202020 SummerFaculty 2School 5Course 4
70013Q120202020 SummerFaculty 2School 3Course 5
70013Q220202020 SummerFaculty 2School 3Course 5
70013Q320202020 SummerFaculty 2School 3Course 5
70013Q520202020 SummerFaculty 2School 3Course 5

 

I have a users table like this:

School_CleanUsername
School 1[email protected]
School 2[email protected]
School 3[email protected]
School 4[email protected]
School 5[email protected]

 

I will put the real emails in before I test in the service. 

 

I am trying to ensure that user1 sees all data for School 1 etc, but nothing I do works when it gets into services. 

have added the emails to the role correctly in security within the service. 

 

I have implemented roles and it all works fine in desktop when I view as role, however it is not working in the desktop no matter what I do. Just the header of the table is shown with no data. I

I have tried multiple approaches, the latest one includes the following code:

CONTAINS('SECURITY (2)', 'SECURITY (2)'[Username], USERPRINCIPALNAME(), 'SECURITY (2)'[School_Clean], 'TEST'[School_Clean])

 

I really need some help!

Need Help

7 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Yes, I have added the role in the service and tested as that role, when I test as role this is where the table headers appear but no data. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi, if you happen to still be working on this two years later, or for anyone else that gets this as the first result:

        The Usernames might not be what you think it is. In a medium article I found this hint:
        "

        Identify user in hierarchy

        User Principal Name function, USERPRINCIPALNAME(), is the main method for identification. Note that this function return different values in desktop and in online service. The online name is what matter during RLS execution."

        I had the same problem, and I was using [email protected], but the actual IDS in Power BI were [email protected] . So if you test [email protected] in desktop it will work, but when you test the individual in live, it will use their ID email, and it won't work.

        Hope this helps someone!

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Anonymous 
    Once you have a list of email address of users, you should try a simple code in RLS:

     

    Username = Userprinciplename()

     

    Paul Zheng _ Community Support Team
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thanks yes I have tried that on the data table, I can 'View as' on the desktop and everything works as expected. And when I publish to the service, add the email addresses to the roles, and then test as role, I cant see anything. Its just a bit weird! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Anonymous 

        Your dynamic RLS should have worked properly, although you cannot see value with test as role.

        With my test, I also get blanks with "test as roles". To make it work properly you need to enter the selected person. Otherwise power bi service cannot determine whose view do you want among many users so returns blank.


        Paul Zheng _ Community Support Team
        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.