Item-Level Permissions in SharePoint List based on Column Value

Question

We have a SharePoint site that will be used to handle claims and enquiries. We have three tiers of User with different permissions. All users must be logged-in to access the site:

• Admin
• Internal User ("Approver")
• Guest User (“Client”)

SharePoint List & Issue

A SharePoint list is (“Trade List”). The SharePoint List is populated automatically by an integration with a data warehousing system. All Client data arrives in the SharePoint List, there are up to 30+ clients and 5,000 lines of data at any moment. The data is provided in this format to SharePoint List “Trade List”:

Tax Reg No.	CompanyName	ETD	Cstm No	Entry Date
988313294002	ACME Inc.	760	026751H	01/04/2021

In Column A of ”Trade List” there is a unique tax identifier of the Client.

We wish for each item/row in SharePoint List “Trade List” to only be visible to the logged-in user with the tax identifier in Column A. This is effectively Row-Level Security (“RLS”) for the “Trade List”.

I am comfortable with Power Automate and removing permissions/awarding permissions when an item is created. I would need help specifically with the next step.

Identity Table (“ID Table”)

Through a separate system, we already manage an ID table in this following format:

fname	lname	CompanyName	Email	Tax Reg No.
John	Smith	Acme Inc.	jsmith@...	988313294002
Joe	Bloggs	NewCo	joe.bloggs@...	787545492000

Implementing item-level permissions on each item in the “Trade List” using a Power Automate flow, based on the column value 'Tax Reg No.' and a lookup for the associated User and email address.

Is anybody able to best advise on how to advise on the Implementation of Row-Level Security (RLS), so that a Client can only see SharePoint List items associated with their unique Tax Reg No. when logged-in?

Answer 1

You could first break the inheriting permissions of the list and delete all the users in it.

Then you could use the power automate like this:

The function of 'body' in the screenshot is: body('Send_an_HTTP_request_to_SharePoint_3')['d']['id']

You could change the roledefid according to your need (I use the id for Read permission in my test):

As a result, when I create a new item, person in the 'person' column will be granted 'Read' permission(In my test, Wendy Li is the site owner, so this account itself has full control).

You could also read this article for more information: https://social.technet.microsoft.com/wiki/contents/articles/53927.sharepoint-online-break-inheritance-and-set-list-item-level-permissions-with-power-automate-part-1.aspx