Secure the SharePoint lists from allowing Project Manager to add users inside a Projects the PM is not defined inside it

Question

We are in the Design phase of a Project Management system, and we have 2 technical approaches to follow:-

1. SharePoint as the Back-end + Power Apps & Power Automate

2. SQL server as the Back-end + ASP.NET Core MVC

Now at the beginning, I thought that the requirements can be achieved using the first approach (SharePoint). but the customer start giving us some requirements which i do not think SharePoint will support. one of those requirements are:-

• We will have 2 SharePoint lists; Projects + Project Personnel.

• Project will have those main fields:-

  • ID
  • Project Title
  • Description

• Project Personnel

  • ID
  • UserName
  • Role (Engineer, PM, Data Entry, Senior Engineer)
  • ProjectID (reference to the Project list)

• Now in our case we have those business rules:-

  • Only users who are defined inside the IT Admins Office 365 group can add projects (this is achievable in SharePoint by setting the list permissions)
  • User inside the IT Admins Office 365 group can add PM and other Personnel roles (this is achievable in SharePoint by setting the list permissions)
  • PM defined inside a Project can add other users to the project, but can not add others as PM. and PM defined inside a project can not add other users to other projects.

so not sure if requirement 3 can be achieved in SharePoint? i know i can do this inside Power Apps, but if a PM user access the Project Personal SharePoint list directly the PM can add users to any project.. now the customer want us to provide a secure solution, so we need to secure the back end. so i do not think SharePoint will achieve this. while if we use SQL we can secure the back-end using a server-side language such as ASP.net core mvc, and we can prevent users (PM for example) from directly accessing the SQL server databases and only allow direct access to the service account.. so can anyone advice if we should use SQL instead of SharePoint ? to implement our requirements in a secure way?

Thanks

Answer 1

I think the third scenario can be achieved in SharePoint with help of PowerAutomate and SharePoint Form Associated PowerApps.

• Once an entry is created under the "Project Personnel" list, trigger a Power Automate to set the item level permission in a way that, if anyone who accesses the list directly, will see only those items for whom either he/she is in the UserName field and grant access to the user who is the PM for that project.
• Setting this kind of item-level permission will allow PMs to see only their Project related personnel.
• To prevent other project members from editing the item, we can disable the allow edit in grid mode and customize the OOTB list form with SharePoint-associated Power Apps.
• This will open the New and Edit forms in PowerApps where you can write business logic to prevent the editing as per the criteria.