Row Level Security in SharePoint

I do this so often that I never considered this to be something I would write up, but I had a question on it last week so I felt maybe I should at least explain the Q&D on how to make this work reliably with minimal hassle.

When you are doing certain applications it occasionally requires more elaborate security settings that lock down the data by row versus by list or site.  This walks through one of the simplest ways to complete the task with minimal risk.

TLDR: Power Automate Is Key

The basic setup here is quite simple.  The only steps required are the Stop Sharing and the Grant Access steps from the SharePoint tree in Power automate.

The main thing to understand is that you have to STOP sharing before you can change the settings for a specific row at least one time. This resets the SharePoint security to allow row-level for a specific item to work as expected.  

You also need to ensure your SharePoint list is NOT using any customized settings under Item Level Permissions:

If you set this up for User-level permissions then you cannot override the security on individual rows.  

For dynamic sharing, just know that you need to pass in email addresses (generally) in a semicolon delimited list.

How Well Does This Work?

Surprisingly well.  Given that this includes using Power Automate, that should be extra shocking.  If you have your lists set up in the standard manner with them under a Team and your core group who is working on the project but then want to share w/ non-Team members for certain tasks, then this is great.  It lets your apps work much simpler logic-wise (just show the user everything they're allowed to see) and those decisions get made before the apps are deployed.

I have an application that is doing this hundreds of times per day that has been operating on the same list now for 6 years and hundreds of thousands of rows.  I do archive this bi-weekly, but the list itself is always chewing on about 3-5000 entries without issue.  Every row has customized security that is changed several times per day based on ~8 distinct flows on the back-end for that specific scenario.

If it works in this use-case, then it probably will work for most crazy ideas your end-users come up with.

Standard Setup

In the most basic configuration, this can simply be that you have a more elaborate list that includes who should be allowed to see information.  Say you have a RACI of people in your list.  If you grab those as a part of your ingestion or creation in your PowerApp, then you can send a pre-built list to Power Automate and have a very simple solution to a more elaborate problem.

Let's say you have a list of people in two fields of your SharePoint list:

• peopleWhoCanEdit
• PeopleWhoCanOnlyView

Your PowerApp can enable sharing at any time for an individual row by taking these values and sending them to your Power Automate flow.

Maybe these emails are valid?

In the simple case, just resetting a single row is as simple as sending in the ID, the list of emails you want to allow to view and the list of emails you want to edit:

The rest of your flow is just to:

• Stop sharing this item
• Share it with one of the two groups
• Share it with the other group

Since you are passing in strings that are already semicolon delimited, there is very little logic you need to add.  Sure, you could add in some checks to ensure that there was anyone to share with, but otherwise this is all you need.

Again, this is a very simple use-case, but also fairly common.  Don't overthink if you don't have to.

Having said that...

Advanced Setup

In more advanced scenarios, we can wind up with lists that are updated within our apps or sometimes even manually.  In these scenarios there are often one or more Status fields that dictate status and who should/shouldn't view/edit during particular phases of the lifecycle of an entry.  See my post on Power Automate advanced triggers here.

In that post, I give the example of this series of possible statuses:

• Pending  (user has created it but hasn't submitted it)
• Submitted (user has submitted it)
• Leader  <-- Our trigger value
• Leader:Pending (awaiting leader review)
• Leader:Approved (leader approved)
• Leader:Denied (leader denied)
• Leader:Cover (leader wants someone else to handle this and move to another leader)
• Completed:Approved (completed and approved)
• Completed:Denied (completed and denied

In the flow that triggers on the highlighted status, I am:

• stopping sharing with everyone
• share View rights w/ the submitter/creator
• share Edit privileges with the Leader
• updating the status to the next in the list (Leader:Pending)

I use the same steps as you see in the simple configuration above, however, I am building those lists of values within Power Automate itself.

How you build your list though might depend on how you're getting that information.  If you are sharing with multiple editors/viewers, then you might have to build your list from multiple sources.  See my post on Stupid Power Automate tricks and the first one titled: Building comma/semi-colon delimited strings from SharePoint query results.

Once you have that list built, you can then drop it into your Grant Access steps as applicable.

Troubleshooting

This is where the easy stuff gets hard, but also easy.

The hard part is that there is no simple way to see what row is now shared w/ what people.  If it breaks or doesn't work for some reason, then you cannot just glance at it to see what is wrong.

HOWEVER, the easy side of this is: just run the security reset again.  It will stop sharing, reshare, and you're all set.

I've glanced at some ways I might see the security settings via API, but I really don't care because it rarely breaks and is easy to fix (run it again).

Final Thoughts

When I originally started with PowerApps I really pushed back on customized security.  I will admit that I'm still biased in this direction because: simple designs have simple problems.  However, over the years I have trusted this pattern more and more.

This works well, scales well, and takes only a moment for a security reset on a row.  Combining this with a Run Only user configuration (as described here) and you could do a very stupid design where you allow people who don't have the rights to the data change the rights to the data (I have come across the occasional scenario when this is the least stupid decision).  

Regardless, I wouldn't hold back in using this design for your apps.  If you've laid out a RACI or a workflow and just need to only allow access to certain information at certain times to certain people, then it is essential that you know how to make this work reliably.