1

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?

1 Answer 1

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: enter image description here

enter image description here

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):

enter image description here

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).

enter image description here

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

1
  • This is great and really helpful walkthrough. I have established this Flow as a 'when item modified' trigger, and I am manually modifying the column of 'Owned By' user, to establish the User who has permission to edit the item. If I am looking at a full automation, how would I use Power Automate to 'lookup' the Tax ID in Table 1, and associate this with a User Email/User ID?
    – sambr
    Oct 27, 2022 at 11:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.