Managing permissions on sensitive SharePoint Online list items that have to be routed for approvals

Question

In SharePoint 2010 workflows, the "Replace Permissions" step allowed us to limit permissions to list/library items so that the user and a specified manager and even a permissions group name specified in a field on that form could have permissions to that item.

Now that we are moving to SharePoint Online and Power Apps, I am trying to figure out the best way to handle these unique permissions requirements. The out of the box "item level permissions" that allow a user to only view/edit their own submissions won't work for us. We have a large number of offices all submitting forms - like a telework request that contains someone's home address, for example - to a single list and managers need to see their employees' requests but not those from other offices. Plus the HR group that handles telework would need to see ALL of the requests. We have 100+ district and division offices, so having each area keep their telework requests on their own subsites COULD be an option, as long as there's some easy way for HR to have oversight over the whole thing without having to manually go to each individual office's site.

Setting permissions via a Flow seems rather complex, especially compared to how easy it was in SharePoint 2010 workflows. Plus, there's a limit of 5,000 unique permissions per list. There are a couple of instances where we are going to get more submissions than that (like on a purchase request) in a year.

So is the solution to suck it up and set up a Flow that replaces permissions, and then for any large library, break it up by fiscal quarter or whatever to ensure we stay under 5,000 unique permissions per list? Or is there a way to maintain data on a ton ofsubsites with oversight by the program office at a high level? Or is there some other way to best handle our requirements?

Answer 1

Hello @Nicole Timmons ,

I am sorry about reply you so late.

According to your first demand, you could refer to the following steps:

1. You could create Multi-Team sites based on different Departments, such as IT Depart Team site, Sales Depart Team site and Market Depart Team site. Please grants Full Control permission to manager of the team for each Team site. For example, IT Depart Manager "user1" has Full Control permission for IT Depart Team site.
2. Then, in different Team sites create the "Telework Application" list.
   Please go to List settings >> Permissions for this list >> Stop Inheriting Permissions >> select the users or groups you want to remove and click Remove User Permissions >> give appropriate permissions to team members
   
3. Finally, go to List settings >> Advanced settings >> enable "Create/Read/Edit items that were created by the user" feature >> you will see the following appearance
   
   

The second demand:

1. In HR Depart Team site, create a subsite named "Telework HR" for the HR group specializing in telework. And, grants Full control permission for HR group members that handle telework.
2. Then, create the Telework Report_xxx list based on different Departments.
3. Finally, you could create MS flow to copy SharePoint list items between two different lists. For example items of "Telework Application" list On IT Depart Team site copy to Telework Report_IT list.

You could to refer to this article to view how to copy SharePoint list items between two lists using MS Flow.

Thanks,
Echo Du

………………………………………………………………………………Updated Answer……………………………………………………………………………………

Hello @Nicole Timmons ,

According to your first demand, you could create a site content type and deploy this content type to all lists under the same site collection.

Please following steps:

Ⅰ Create a Site Content Type

1. Go to the top level Site settings >> Site content types >> click on Create button to create a new content type
2. In my test environment, I created a content class called Telework. The detailed information is shown in the following figure:
   

Ⅱ Add Site Content Type to List

3. Go to any SharePoint list under the current site collection >> List Settings >> Advanced settings >> enable "Allow management of content types" >> OK
   
4. On the List Settings page, scroll down to Content Type section and click Add from existing site content types link
5. On the Add Content Types page, select your created content type under the Available Site Content Types List >> Add >> OK
   
6. Return to this SharePoint List page >> when you click New button, you will find that there are two content types for you.
   

Ⅲ Manage List/Item Permission

1.Go to List Settings >> Advanced settings >> scroll down to Item-level Permissions section, enable "Read items that were created by the user" and "Create items and edit items that were created by the user" >> OK

2.On the List Settings page >> Permissions for this list >> Stop Inheriting Permissions >> select the users or groups you want to remove and click Remove User Permissions >> give Full Control permission to the specific team manager

For your second requirement, I will implement and update my answer as soon as possible.

Thanks,
Echo Du

=======================

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you for taking the time to write up that very detailed response! I see a couple of problems with this approach for our specific situation.

1. We have more than 100 offices in our agency - district offices in each state and U.S. territory as well as divisions (like HR and IT) at headquarters. If we did this approach and kept each form separately on each office's teams site, what happens if the telework request changes? Would I have to manually change it on each site? Is there a way to deploy a content type to lists so I can make a change once and have it pushed out to all of the teams sites automatically? (My experience is only handling content types with form libraries - in the past I had only used lists to store data that my form library forms referenced.) I manage all forms for the agency and we have SO MANY different forms. So if I have to maintain each form separately on each teams site, that would definitely not be a solution that would work in our situation.
2. Even within a single office, there may be information that is sensitive enough that one manager should only be able to see their own staff's data. So this solution would require us to create teams sites for each individual MANAGER - and I can't even imagine how many sites we would end up with then.

If a form had, let's say, two people picker fields and a dropdown field that contained the name of the office, it would be great if there was a way to create a view of the library that could be filtered based on those values (if you = people picker 1 (employee) or people picker 2 (supervisor name) or your office in active directory = the office dropdown then you can view the record)... But then there would need to be able to be a view for the program office to see all the forms that regular users can't get to (perhaps in a web part on a permissions restricted page?), and there would need to be a way to prevent the rest of the users from creating a new view that could then be unfiltered. Would that be possible somehow?

Answer 3

Thank you very much @Echo Du_MSFT !

I may have found a solution that could work without us having to use item-level permissions on everything that is sensitive. After reading this article yesterday (https://techdailychronicle.com/create-sharepoint-list-items-in-a-folder-using-power-automate/) I have been researching how to use Power Automate to create a permissioned folder structure:

• We have a list that has the name of each office and I was hoping that I could create a Flow to reference that list and create a folder structure using those names.
• Then I want to attempt to use the Flow to update the permissions of each folder based on another column in the office names list that contains the name of the associated permissions group (HR_Telework; IT_Telework, etc.).

Obviously creating all of these folders could be done manually, but with 100+ offices and tons and tons of forms, I hoped I could automate this process.

Then in order to prevent the managers within an office from the sensitive information related to another manager's employees, I could use people picker fields with the employee and manager's names to create a filtered view, and I would remove the ability for users to create their own views. I would create a view of ALL data in that folder but hide it from the default view through the Per Location view settings. And then I could put the All Items view in a web part on a permissioned page that only the office's senior management can see.

In order for HR to have oversight over ALL telework requests, I could use the Highlighted Content Web Part (https://sharepointmaven.com/how-to-roll-up-content-from-sharepoint-sites-using-highlighted-content-web-part/) on a permissioned page for the HR team.

Does that seem like it could work? If you have any other suggestions, I look forward to hearing them!