Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

RLS for School

  • 1.  RLS for School

    Posted Aug 09, 2020 06:35 AM
      |   view attached

    Hello everyone,

    I am trying to set up RLS for schools and have currently been coming across some issues. I have gone through a lot of videos multiple times and have not been successful. I was wondering if you all would be able to help me out here.

    I have a kind of tricky situation. I feel like the solution should be pretty easy and I am not looking at it in the correct order.  I am going to start explaining from the beginning.  I have a students that holds basic student information.

    I am trying to create row level security for teachers and principals(Teachers being level 1 and Principals Level 2). The teachers should have access to see information to only the student they have in their class whereas the principal should have access to all the students within the school. 

    My teachers table looks somewhat like this, 
    And, the table above is the only table that has the teacher and students with their IDs on the same table. 
    I have a directory table as below, 
    I have tried all kinds of different scenarios but there I have not been able to come up with a proper solution to create RLS for them so I am providing a sample of the tables as they are here to see if there is a way around. I have tried creating bridge tables and calling them by USERPRINCIPALNAME and Level but have not been able to find a way to limit the teachers from accessing all the student's records. I have a deadline coming up and any help would be very much appreciated.


    ------------------------------
    Lola Gallagher
    ------------------------------

    Attachment(s)

    pbix
    rowlevel_test.pbix   72K 1 version
    Academy - Online Interactive Learning from Experts


  • 2.  RE: RLS for School

    Top Contributor
    Posted Aug 10, 2020 09:03 AM
    Your setup is actually pretty trivial.  All you need is two roles "principal"  and "teacher"

    The "principal"  role doesn't need any RLS rules. The "teacher" role needs to impact the teachers table.  As you mentioned you need to use the teacher's email address and compare it to USERPRINCIPALNAME() for the rule in that table.

    The rest is done by the data model. The teachers table needs to filter the students table (NOT the other way round)

    Ideally you would pull the teacher email into the teachers table.  If you don't want to do that you can have the directory table filter the teachers table , again in a single direction,  and apply the RLS rule to the directory table instead of the teachers table.

    Don't call the email column in the directory table "USERPRINCIPALNAME" - that will just get you confused.  call it email.

    The rule would then be

    [email]=USERPRINCIPALNAME()


    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 3.  RE: RLS for School

    Posted Aug 10, 2020 12:30 PM
    Thank you so much for your time and consideration. The principals should only be able to access the information for their school which is where the school ID comes in. If you look closely there are two school IDs. I apologize, I should have mentioned that before. This is where it kind of gets tricky for me as I have not been successful to implement it for all of them afterwards.

    ------------------------------
    Lola Gallagher
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 4.  RE: RLS for School

    Top Contributor
    Posted Aug 10, 2020 01:39 PM
    Edited by Lutz Bendlin Aug 10, 2020 01:47 PM
    Sorry didn't catch that - in this case you would need to apply the appropriate rules to the "Principal" role based on the school ID too.

    Couple questions:  Can student IDs repeat across schools? Can Employee IDs repeat across school? Can a student have multiple teachers?

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 5.  RE: RLS for School

    Posted Aug 10, 2020 01:53 PM

    This is where I am struggling to create a proper security table that would give principals access to all the students within their schools and teachers access to only their students. 

    Student IDs are distinctly connected to each student. Thus, it would happen if the student switched over to another school, but then his/her school ID would also be automatically be updated along with the teacher's information. A teacher can teach the same class in more than one school. So, if the student moves to another school and still takes the same teachers' class, then the teacher would be able to view his records.



    ------------------------------
    Lola Gallagher
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 6.  RE: RLS for School

    Top Contributor
    Posted Aug 10, 2020 04:15 PM
    Principals need access to schools, but teachers need access to students.  Set up your tables and relationships accordingly. I would add a schools table with school id and principal email, and keep the teachers table for teachers and their students only.  Both teachers and principals tables would be dimensions pointing to the students fact table, with the scores table a child fact table to the students table.  Don't do 1:1 relationships, enforce 1:M with unidirectional search filters.

    ------------------------------
    Lutz
    ------------------------------

    Academy - Online Interactive Learning from Experts


  • 7.  RE: RLS for School

    Posted Aug 10, 2020 09:17 PM
    That is not pretty doable. I will slowly be adding more tables and would like to keep security coming in from one table only.

    ------------------------------
    Lola Gallagher
    ------------------------------

    Academy - Online Interactive Learning from Experts