I have a feature class (SQL Server table) that contains data from multiple clients (organizations). I want to expose the data, but control access so an org can only view/update its own data. I've created a flow as shown below (this is done for each org)
- Create a layer file (.lyrx) with a query definition against the single table to pull out the org's data (the table has a key field - ORG_ID)
- Create a project (.aprx) based on the layer file and publish a REST endpoint on my ArcGIS server
- Create a feature service in ArcGIS Online based on the REST endpoint
- Share the feature service into an AGOL group where I have invited only members of the organization
- From that feature service I build web maps, web mapping apps, surveys etc and share them into the group also
This all works very nicely and I've scripted it so it is easy to onboard new orgs. But I have one problem I can't solve. I need to make sure that the ORG_ID is not altered for existing records (I think I can accomplish this by hiding the field in the layer definition) and I need to make sure that the ORG_ID is correct on added records. I've looked into SOE's and DB triggers. The trouble there is there isn't enough context to know what the value is supposed to be. I tried many other approaches but nothing has worked completely. Any suggestions?