Advanced User Permission Control

[_Dave_]

Hi All,

I have a business app that was built in AppSheet. I am currently looking to distribute the app and now need to consider the best way to implement security filters into the app.

The app is rather extensive, and so I plan to break it down, creating a launcher, and splitting the app into several 'Modules'.

Each user will have access to different Modules, and access within the module would also be varied. For example, a single Projects module would be available for our project management 3 teams, but only each team would be able to view their own projects. Furthermore, each team would have Production users than can View only projects that have a status of 'Production' and not have any access to the project price value fields, while our team sales managers would have access to Add, View, Edit and Delete the teams projects, and all fields.

This will be a fairly granular control, but I'm hoping this can be done with AppSheet.

My question now is based mostly on, How? I'm unsure on the best way to implement this, however, I did consider two options:

Option 1 - A Users table, which contains an EnumList for each app available, and options for each app, where multiple options could be selected to control user access.

Option 2 - A Users, Roles and User Roles tables, where Roles and users are created, multiple Roles are linked to Users in a bridging User Roles table. One Role per entry.

I do have a sample App I have created, with both options in place, if someone could explain how I can make this publicly available, I can share a link to it here.

I have attached a screenshot below, of an example however, Option 1, which I feel might be the most practical.

I'm looking for any practical feedback, solutions and options from people that have experience in implementing advanced user permission systems that are rather granular.

Thanks for your time.

[AleksiAlkio]

Before thinking multiple apps, permission and security filters.. what data source are you using and how much data you would have if it would be all in one app?

[David2]

Thanks for your reply, I'll be using Google Sheets as my source. Initially there won't be much data, small start out business, but it'll be a growing app, where more features will be added as needed and as the business expands. There will be plenty of views for each data source depending on how we want to show data. Along with this some parts of the app will require a number of virtual columns that will run calculations.

At this stage, one app might suffice, but I'm thinking into the future as well.

[AleksiAlkio]

How much data approx. after 1..2 years?

[David2]

Possibly 50,000 - 100,000 rows

[AleksiAlkio]

How many tables?

[David2]

Currently 23, but in the future, possibly closer to 50

[AleksiAlkio]

With your table qty and data row amount, I would probably create the app using real database. In general it's easy to propose.. create a launcher and bunch of apps so they would be simple, light and quick to open. Sometimes this works, but in reality, this approach won't work as most of those apps need most of your tables as well. And most of the time they are of course.. those biggest. Then you find you in a situation where you are opening apps again and again and waiting. Yes bigger apps need more time to sync, but it's happening on the background.

When creating bigger apps, it's more critical to understand how to avoid complex virtual columns, how to write security filters so they are pushed to your database and how the app is structured properly. When the table amount is 50 or less, and the app is built properly, you can assume the sync time is going to be somewhere 8..10 secs.

[David2]

Thank you for the explanation and insight.

Could you please clarify 'create the app using real database.'?

Do you mean using the databases built into appsheet, instead of google sheets?

Or do you mean steer away from appsheet all together to a database specific tool?

[AleksiAlkio · Accepted solution]

I would use AppSheet.. nothing against that 🙂 But I would concider to use real databases like SQL or MySQL together with AppSheet.. or maybe with AppSheet Database as well. It depends what kind of features you need from your app.