Recommended approach for user permissions

[PowerRanger]

Hi community,

I have a question in regarding user permissions and I am trying to find the best way to implement the following requirements.

1. I have a Canvas App for the End-Users

2. Data will be stored in Dataverse (multiple tables with relationships)

3. The following personas are involved

A. End-User (all employees)

B. Requester (actually the End-User but somebody could create a record on behalf of somebody else)

C. BU related HR departments (Each BU has its own HR Department). So there are multiple HR departments

D. Sales Department

E. Reviewer

F. Admin

Requirements:

1. End-Users (all employees) should be able to create/edit a record which they have created

2. The record contains a Lookup field to EntryID. The End-User is able to select any users from EntryID. The selected user should also get read/write permissions on that record (Requester)

3. If the Status-Reason change to "In Progress" the End-User and the user selected in Step 2 (Requester) should only have read-permissions

4. Based on the certain validation checks a specific HR department should get Read-Access

5. Based on the certain validation checks the sales department should get read access

6. The End-User is able to select a Reviewer (actually the same logic as for the requester). The reviewer should get read-access to the record

What would be the recommended way of setting this up?

[Devvj]

Hi @PowerRanger 

Its pretty tricky, but in essence first line of defence is the security roles, which will apply for the logged in user, however if you want to use more of a conditional access type of thing it gets more complicated fast 😄
You can get the security rules of a user as a table using the following powerfx:

LookUp(Users,'Primary Email'=User().Email).'Security Roles (systemuserroles_association)'

Then you can use that in the logic above.
Hope that helps some bit atleast 🙂

[PowerRanger]

@Devvj

I can Imagine that that this will be tricky and I haven't found a solution yet... I have to add the security at database level. Hiding or disabling fields in the app won't be sufficient.

What I already have thought of is that after a record was created by the end user I will use a PowerAutomate Workflow to change the owner to a service account and then share the record according to the requirements. But as we have to deal with thousands of records I am not sure how this will impact the performance or if there will be any limitations. 

Any other ideas?