Row Level Security in Power Apps Canvas App - SharePoint Data Source

[nicktalsma]

I am trying to figure out how to implement row level security in my Power Apps Canvas App...   The app will have a basic form where users will be able to create a new record. For example, a user will enter the Reporting Date (date picker), Fac Name (drop down) and Admits (free text). The Fac ID, Area and Region fields will auto-populate (Lookup formula referencing a SharePoint facility list) based on the data entered in Fac Name. 

Form (Insert Record):

Reporting Date	8/29/2023
Fac Name	Chicago
Admits	5
Fac ID	150
Area	Smith
Region	Wilson

From lowest level management to highest, each facility has a Program Director (info not stored in SharePoint list below), Area Director and Regional Director.  Typically a Program Director will only be responsible for one facility, but in same cases may oversee two or three. The Area Director will cover a small group of sites and the Regional Director will cover a larger group of sites.  

SharePoint Facility List:

Facility	Area	Region
150	Smith	Wilson
151	Smith	Wilson
152	Smith	Wilson
153	Jones	Wilson
154	Jones	Wilson
155	Jones	Wilson
156	Brown	Miller
157	Brown	Miller
158	Brown	Miller
159	Davis	Miller
160	Davis	Miller
161	Davis	Miller

Beyond creating a new record, I want users to be able to only read/edit records that were created for a facility where they have responsibilities.  For example, the Program Director garcia@example.com should only have access to facilities 151 and 152. The Area Director smith@example.com should only have access to facilities 150, 151 and 152.  The Regional Director wilson@example.com should only have access to facilities 150, 151, 152, 153, 154, and 155.  My initial thought is to create a SharePoint user list, like the example below. 

From there I am a little lost... I assume I will need to somehow filter a gallery/data table based on the current users 'User().Email' as it relates to the SharePoint user list?  I'm a novice a SharePoint and don't really know where to start here... 

SharePoint User List

Facility	User Email
150	moore@example.com
151	garcia@example.com
152	garcia@example.com
153	robinson@example.com
154	allen@example.com
155	harris@example.com
156	taylor@example.com
157	johnson@example.com
158	lewis@example.com
159	perez@example.com
160	clark@example.com
161	clark@example.com
150	smith@example.com
151	smith@example.com
152	smith@example.com
153	jones@example.com
154	jones@example.com
155	jones@example.com
156	brown@example.com
157	brown@example.com
158	brown@example.com
159	davis@example.com
160	davis@example.com
161	davis@example.com
150	wilson@example.com
151	wilson@example.com
152	wilson@example.com
153	wilson@example.com
154	wilson@example.com
155	wilson@example.com
156	miller@example.com
157	miller@example.com
158	miller@example.com
159	miller@example.com
160	miller@example.com
161	miller@example.com

Other thoughts.  Would there be a simpler way to store the Area and Region emails in the SharePoint user list since Area and Region are fields that are recorded on the form?  Maybe some sort of 'triple filter' that filters first by matching on Fac ID, then on Area, then on Region? 

This is all just filtering on the front end within Power Apps...  Which I am guessing is not super secure as anyone with access could get to all the data in SharePoint.  Is there a better way to do this?  Anything I saw on Google appeared to get pretty complicated within Power Automate and didn't really match my use case.  

Any help here would be much appreciated!  

[EddieE]

@nicktalsma 

Row Level security in SharePoint really isn't a thing. If you want that you need to look at something like DataVerse but that also comes with extra licensing costs.

This @RezaDorrani video walks through Item Level permissions in SharePoint and may have some ideas for you?

https://www.youtube.com/watch?v=EJyZfYMi4n0

I think the basic takeaway is that Filtering in PowerApps won't stop users from getting full access to your SP data, so you need to think outside the box if you want to setup something like this in an app.

[nicktalsma]

Understood about SharePoint data not being protected.  

I’d still be curious about how to go about filtering this within Power Apps so that folks are only looking at the data that matters to them. 

Thank you!

[EddieE]

@nicktalsma 

Ok then, I think you have picked a tricky app / data problem here. Basically, you need to use a function like 'in' to filter your data by person / email but you will run into delegation issues using 'in'.

One way I see that you may be able to tackle this is by setting up the 'SharePoint User List' with a Choice column for Facility rather than having a record per Facility & User Email, it would look like

User Email                          FacilityAccess
moore@example.com       150
garcia@example.com        151, 152
robinson@example.com   153
allen@example.com          154

brown@example.com       156, 157, 158
davis@example.com         159, 160, 161

This would also be much easier to manage when adding / removing access to facilities.

Then, in the App OnStart you could add something like this

Set(
    vFacilityAccess,
    Concat(
        First( Filter( UserList, UserEmail = Lower(User().Email))).FacilityAccess,
        Value,
        ", "
    )
)

This creates a Text string called vFacilityAccess that holds the facilities the User has access to, which you can reference in a gallery filter to only show facilities that the User has access to. To do that, you can follow @RezaDorrani 's video here where he talks about showing and hiding data - without delegation warnings - and using a Flexible Height gallery to do this.

https://www.youtube.com/watch?v=44j2VRbdWjk

Instead of using the ComboBox demo'd by Reza to help filter, you'd use the text string created above, if that makes sense?

[nicktalsma]

This sounds like a great solution!  I much prefer the use of the choice column over the many rows in my original post. 

To simply managing these lists further, I think I will want to create 4 SharePoint User Lists.  

1. Facility based list as you demonstrated above
2. Area based list that has Area Director Emails and the Area they are assigned to.  This can reference the Area field from the form.  Prevents me from having to type out each site for each user and keeping up to date as sites come online/offline.
3. Region based list that has Region Director Emails and the Region they are assigned to.  This can reference the Region field from the form.  Prevents me from having to type out each site for each user and keeping up to date as sites come online/offline.
4. Admin/Executive based list.  This would only need a single column of emails as these folks would have global access. Prevents me from having to type out each site for each user and keeping up to date as sites come online/offline.

As Area Director, Region Director and Executive turnover is infrequent. I think this will leave me with just managing the facility based list day-today. 

Then I guess would just need a filter in the gallery / data table that looks at those 4 lists using IF OR?  

I should have some time to play with this next week.  Thanks for your help @EddieE 

[nicktalsma · Accepted solution]

I ended up going a slightly different direction.  Instead of trying to filter a gallery/data table by the user's permission's all at once - I decided to add all the allowed facilities in a collection and add to a dropdown.  The gallery/data table can then be filtered by the facility dropdown.  Not exactly how I wanted it, but I think this approach will be a little cleaner than Reza's hack in the YouTube video. 

So I have a 3 column list setup in SP (called sp_users) with column headers email, type and permission. 

I created 5 collections based on the 5 types of users groups there are possible (Admin, Facility, Area, Region & LOB), filtering by current user email.  

The permission column will either have the Facility ID in it or the Area, Region or LOB name - or it will say Admin if the user should have global access.

I check the first collection (UserCollectionAdmin) if the user is an Admin (FacilityCollection is an existing collection that list all of our facilities). If so they get all active HRS facilities, otherwise the remaining 4 collections are checked and combined.   So it is possible for someone to have a combination of Facility, Area, Region & LOB permissions in the PermissionCollection.

Finally I add a concatenated column and end up with my final ListCollection that my dropdown uses.  

ClearCollect(UserCollectionAdmin, Filter(sp_users, type = "Admin", email = Lower(User().Email)));
ClearCollect(UserCollectionFacility, Filter(sp_users, type = "Facility", email = Lower(User().Email)));
ClearCollect(UserCollectionArea, Filter(sp_users, type = "Area", email = Lower(User().Email)));
ClearCollect(UserCollectionRegion, Filter(sp_users, type = "Region", email = Lower(User().Email)));
ClearCollect(UserCollectionLOB, Filter(sp_users, type = "LOB", email = Lower(User().Email)));


If(LookUp(UserCollectionAdmin,"Admin" in permission,true),ClearCollect(PermissionCollection, Filter(FacilityCollection, status = "ACTIVE", lob = "HRS")),

Concurrent(
            ClearCollect(col6, Filter(FacilityCollection, status = "ACTIVE", lob = "HRS", facility_id in ShowColumns(UserCollectionFacility,"permission"))),
            ClearCollect(col7, Filter(FacilityCollection, status = "ACTIVE", lob = "HRS", area in ShowColumns(UserCollectionArea,"permission"))),
            ClearCollect(col8, Filter(FacilityCollection, status = "ACTIVE", lob = "HRS", region in ShowColumns(UserCollectionRegion,"permission"))),
            ClearCollect(col9, Filter(FacilityCollection, status = "ACTIVE", lob = "HRS", lob in ShowColumns(UserCollectionLOB,"permission")))
);
ClearCollect(PermissionCollection, col6, col7, col8, col9);

Clear(col6);
Clear(col7);
Clear(col8);
Clear(col9);

);

ClearCollect(ListCollection, AddColumns(PermissionCollection,"name_id",location&" - "&facility_id))

Added distinct and sort to have a alphabetized list of facilities with the name and ID of the facility.  

Sort(Distinct(ListCollection, name_id),Value,SortOrder.Ascending)

May not be the most elegant solution, but it seems to work for me. 

[EddieE]

@nicktalsma 

Sounds like you've got it sorted how you want/need it so well done mate!

[inservo]

I wonder how PowerApps could ever scale without this being available? Is this a 'upsell measure in disguise' by MIcrosoft so that they purchase more DataVerse licenses or what?

Quite honestly, this makes the approach of PowerApps and ShP basically unusable / un-maintainable if you have more than 50 users.

Is there really no other solution to this problem?