0

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

1 Answer 1

0

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.
3
  • regarding your point 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... but the user can still make a REST API call to the SharePoint list and modify by pass the power apps form, what do you think? second point, i am storing the ProjectID inside the Project Personnel to link the Project/ProjectPersonnel, so there is no way inside SharePoint to prevent a PM from entering a ProjectID number they do not belong to .. am correct on both points?
    – John John
    Nov 14, 2022 at 12:20
  • for 1st point you are right. for 2nd point, there must be some business logic that populates the available project Ids to select for a given PM. Can't we implement the same in PowerApps? Nov 14, 2022 at 13:18
  • Yes we can do everything on Powerapps, but my point is that the validation on Power Apps work on the client side only and not on the server side..so any validation applied to Power Apps can be overridden/bypass if the user directly interact with SharePoint lists
    – John John
    Nov 14, 2022 at 13:25

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.