Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Row level security without relationship?

So I'm trying to implement dynamic RLS for days now, I'm loosing my mind 🙂

. I've read all the Radcad articles about it, but I keep getting stuck at one point. How do you implement a user table defining the level of security when you don't have a single column (but multiple) on which you can define a relationship in the fact table?

 

I have a table dertermining the security roles, where a single emailadres can have access to several CC-Names (see [email protected]) and a single CC-Name can have multiple emailadresses (see Consulting CI)

CC-NameIDParentIDEmail
Consulting CI12[email protected]
Advisory Consulting23[email protected]
Advisory34[email protected]
Advisory Total45[email protected]
TOP5 [email protected]
Consulting PS62[email protected]
Consulting CI12[email protected]

 

The fact table consists of a row for every employee depicting the total cost center structure they belong to: 

 

EmployeeIDCCL1CCL2CCL3CCL4CCL5Score
1Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP5
2Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP5
3Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP5
4Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP52
5Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP14
6Consulting CIAdvisory ConsultingAdvisoryAdvisory TotalTOP17
7Consulting PSAdvisory ConsultingAdvisoryAdvisory TotalTOP15
8Consulting PSAdvisory ConsultingAdvisoryAdvisory TotalTOP19


My question: how can I link those two tables together in order to create dynamic RLS? Is that really neccessary, or can I do something fancy with disconnected tables?

Thanks so much!

 

Best, Strike88

Need Help

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak , 

       

      Thanks for taking the time to get back to me. This doesn't solve my issue unfortunately, or I'm missing something.. I'm in need of help to implement this. How would that work for my particular situation?

       

      Best regards, 

  • ibarrau's avatar
    ibarrau
    Super User
    6 years ago

    Hi. RLS can be tricky, but you can do that. I'm spoiling my blog with this post because I was going to write about this haha.

    You can try adding a rule in your fact table RLS like this:

    [EmployeeDCCL1] IN SUMMARIZE(FILTER(First_table, First_Table[Email] = USERNAME()), Fist_table[CC-Name])

    I should show the cc-names of that email and check with the "IN" if they are in the fact.

    In addition to this, let me warn you that RLS should be applied to dimension. Applying rls to facts can result in low performance.

     

    Hope this works.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi ibarrau ,

       

      Thanks for trying to help me, I'm really enjoying this communicty!

      Unfortunately, I did not solve my issue with your solution. See screenshot below. What did I do wrong?

       

      Do I need to create a relationship between the tables?

       

      Thanks in advance!