I’m trying to figure out the best way to create access control to multiple apps for various user types. I have a single service which is connecting to a sql database that has many tables. Each user may have view or edit access to various tables (which correspond to various apps). So, if I were to just create roles in the admin console I’d end up creating hundreds of roles to account for various configurations of apps and permissions. For example user1 may have view access to app2 & app3, and edit access to app5, and user2 may have view access to app5 & edit access to app1. You get the idea. There are a few hypothetical ways that I can see going about it, and I’m hoping to get some feedback on which would be the best method, or if another method would make more sense.
GLOBAL DEFINITION VIA GLOBAL LOOKUPS
I could define app1Viewers, app1Editors, app2Viewers, etc as global lookups and have the values be an array of user ids. Then in server-side event scripts for each table I would check that the current user’s id was contained in the app1Viewers array before allowing a GET or in the app1Editors array before allowing a POST/PUT/DELETE. This seems resource efficient since the global lookups would be available to the system, but I don’t think I can create or edit global lookups via the api - which I’d need to do to manage users from a custom admin front-end.
GLOBAL DEFINITION VIA “ACCESS” TABLE
I could create a separate table, make it only accessible to admins, and create a record for each app with viewers & editiors fields that contained an array of user ids. Then in server-side event scripts for each table I would check that the current user’s id was contained in the appropriate array within the Access table. This makes sense in terms of being able to create/edit the Access table from my custom admin console, but I guess I’d be adding an additional request to every request made in order to grab the Access table data and compare.
USER DEFINITION VIA USER LOOKUPS
Similar to option 1, but I’d define viewer & editor lookups for each individual user and reference those through event scripts. This would work fine as long as I could easily update those lookups via the api.
USER DEFINITION VIA CUSTOM USER SETTINGS
Similar to option 2, but I’d define viewer & editor custom user settings for each user and reference those through event scripts. Makes sense for easy control via api, but I’m still adding extra requests and I’m not sure how secure custom user settings are.
I feel like I must be missing something - that there is some more built-in way to accomplish this that I’m missing. But I don’t know what that would be. Any thoughts would be very much appreciated.