How to use Security Filters to allow multiple companies to use the same app

[MultiTech]

Let’s say you’ve got an app, and you want to allow multiple companies to access and use the same app.

• The primary benefit here is that, as a developer, you only have one app to support.
  • If you were to create a copy of said app, one for each additional company that wanted to use it, then you’d have to support all those apps.

Any changes made to functionality (column structure, views, actions, automation, etc.) will need to be duplicated on each additional app.

• This then translates to hours of “extra” work you’ll need to do.

---

Instead, use Security Filters to filter the data based on the company the user belongs to.

• This keeps everything inside one app, so as a developer you only need to support that one.
• Also, each company that suggests an improvement to the system is benefiting everyone.

You’ll need:

1. a “Companies” table
2. a “Users” table - that’s a child to the Companies
3. other tables that then filter based on data from tables “higher” in the database
   • Like a “Clients” table being filtered to only show the “User_Assigned_Clients”
   • Or a “Projects” table being filtered to only show records based on the “Project_Client_Link” being present inside the “Clients” table

The process involves:

• Creating a [Company_Master_Email_List]
• Creating an automation to keep that list updated (based on changes to the user table)
• Applying cascading security filters (Companies filters first, then Users, then everything else is based on those two)

---

---

Here’s a video that shows the process

—| Table of Contents |—
00:00 - Intro
00:29 - Benefits of single-app vs. multi-apps
01:14 - Required tables
01:44 - Key to how it all works
02:28 - Adding [Company_Master_Email_List]
02:57 - Creating an action to set the master list
04:15 - Running the action
05:22 - Creating a REF action
07:11 - Creating a BOT to keep things updated
08:31 - Bot condition for when it should run
09:06 - Creating a new task for the bot
09:55 - Actually implementing the Security Filters
10:40 - Companies table
11:02 - Users table
12:01 - Where to get more info (https://www.patreon.com/posts/security-filters-53384681)
12:57 - Wrap up

[vafernandez]

How do you handle (in terms of confidentiality and data protection) the access that you have to the multiple companies working in your App as the creator of the App?

[MultiTech]

Depends on the level of compliance requirements and accountability the client wants

---

For example: anything containing HIPPA protected information requires that you be able to produce, on demand, a report about:

a. Who's ever had access to a patient's data, when they've accessed it
b. What purpose that person had when accessing the data
c. Any changes that might have been made to the data, who made them, and when
d. More details of course, lol

So for a requirement like that, I’ve built in a method inside the app to keep track of people using the app; and if it’s someone like me (where I can have multiple purposes when accessing data), it requires me to specify why I’m accessing the app.

• I record all this in a separate table, so when I need to make this report… I just run a Bot to generate a report for a Patient.

---

In terms of confidentiality - for myself

• Part of the “Terms of Service” for using the app that I’m “supporting” is that they agree that I have access to any and all data submitted in the system; as the developer, I need to have access to everything in order to debug, etc.

In terms of confidentiality - for others

• I, as the app developer, have control over who can access the AppSheet side of things… and the data side of things
  • So through that control, I only give access as necessary, and only to those that require it.

---

For Data Integrity

• This is where backups come into play
  • When making a significant change to a table, or if I’m doing something that requires me to generate new data in the table, I’ll make a copy of the sheet before I start anything
  • When I’m finished, I’ll delete the sheet; but this way I have a backup right there in case I mess something up and need a quick fix.
• Thankfully, Google Sheets keeps a version history - all the way back to the beginning!
  • So you can always revert back to a previous version and undo anything you (or someone else) may have done.

[WillowMobileSys]

MultiTech_Visions:

anything containing HIPPA protected information requires that you be able to produce, on demand, a report about:

Matt, I’m curious about the current process of handling HIPPA within AppSheet. It was required, in the past, that we contact AppSheet. Do you know what is requirement today for apps that need to be HIPPA compliant?

[MultiTech]

WillowMobileSystems:

Do you know what is requirement today for apps that need to be HIPPA compliant?

I’m not entirely sure… and I’m not sure who to tag anymore for more info.

I can tell you this for sure:

• By law you have to have an agreement between you (the people with the data) and your data service provider (where it’s stored, who processes on it, etc.) to keep track of all the compliance stuff. (Called a BAA)
  • Basically in this agreement, the person signing says:
    • I’ll be ready to produce a report of all that access stuff whenever needed… if needed
    • I’ll keep track of all that stuff, and store all that data basically forever… so if it’s ever needed, I’ll have it on had and ready to generate that report.

---

So you used to contact sales directly to talk to them about all this; they’d assign you someone as your case-manager; you’d get the BAA signed, and you’re off to the races.

Nowadays… not sure.

[WillowMobileSys]

MultiTech_Visions:

Basically in this agreement, the person signing says:

• I’ll be ready to produce a report of all that access stuff whenever needed… if needed
• I’ll keep track of all that stuff, and store all that data basically forever… so if it’s ever needed, I’ll have it on had and ready to generate that report.

Meaning the “people with the data” are on the hook to provide the audits, reports and keep the data - not the data service provider?

[MultiTech]

It means that whoever’s signing the BAA is responsible for providing their portion of that data - for everyone on their end that is involved.

So for instance, the telehealth provider client I have

• I have a BAA with them
• AppSheet (probly Google now) has a BAA with them
  • We’re both responsible for our portion of things.

[Alan_Thorp]

Hi Matt,

I'd like to find out more about how you keep track of app usage. Do you have any information anywhere about this topic?

[Techuke]

26Jan22 1957 Very helpful, complete, answer. Thank you from New Zealand.

[Alan_Thorp]

Hi Matt. @MultiTech 

Thanks for this post. I've applied this method to one of my apps, but have run into an issue that I'd like to raise with it.

If a user is added to two companies using the same email address for both then they will see both companies data sets when they use the app. This could potentially be a completely legitimate situation where they work for both companies as a contractor, or a typo that allows an unknow person at another company into a clients records.

I think that this is possible to do even if the user email field has a valid if statement preventing duplicates. This is because the users table will be filtered to the view point of the user entering the duplicate email, causing the app to not know that the email already exisits.

This issue will also roll over into the user slice method that you have detailed elsewhere because the slice will then have two rows, but the index selection of column data will always pick the first row, even for the second company.

I've only just come across this issue so haven't got an alternative method, or adjustment worked out yet. Will update if I get something though. Would be good to know you're view point on it though.

Alan

[dbaum]

For multi-tenant user management where tenant admins can unknowingly but validly need to add the same user, here's a post that references a pretty elegant technique outlined by @Jonathon as well as a more novice approach that I had separately described.

[Alan_Thorp]

Hi dbaum. Thanks for your reply. I'm not sure if this will cover the security concern that I'm highlighting here though. By having a user table that is filtered before an admin sees it there is no way to prevent them from adding user email addresses that exist in other companies.

As an example of my concern lets imagine that we have two Tom's. One works for U post and the other works for US Post. Tom at U Post is already using an app designed for multiple companies call Post Track under the email address <PII removed by staff>. An admin at US Post starts trialling the Post Track app and adds their Tom along with a bunch of other people. Unfortunately they make a typo and rather than have <PII removed by staff> they type <PII removed by staff>. Now Tom of U Post fame now has access to all the records of US Post as well as those of U Post.

Outside of the security concern I feel like it is very useful to be able to have users access multiple companies with one email address, for example as a contractor.

I have three possible solutions:

1. Bring the whole users table down, but only ever show a filtered view via a slice.
2. Bring in the whole users table as another table, check against this when new users are added.
3. Apply some validity check on the email addresses of users that conform to a domain list in a column in a company table, e.g. Company[Domain] = "uspost.com".

Points one and two would not allow duplicated emails. Point three would allow duplicates, but also has security holes in it.

Interested to hear comments. I'm probably going to go with the third one.

[Alan_Thorp]

@MultiTech @dbaum 

Any chance that either of you chaps have had a chance to consider this?

I can't think of a way to avoid either having a seperate sign up routine for new people where their email is checked against the existing multi-company users list, or have the whole users list downloaded every time and just sliced to only show same company users on the device.

I guess I could have it so that I have to add every user and only I have access to the unfiltered list, then the company admins just manage the people and mark them as removed when if they leave.

[MultiTech]

This is actually something that happens all the time - one person (one email) needing to be assigned to multiple ref connections.

• The solution that I've centered around is to create a join table between the User table and whatever table you're trying to many-to-many connect someone to.
• Then introduce a way for someone to select what's the "current job" or whatever, giving them the ability to select a single item out of their list of available options.
• Then create a set of slices to isolate those out for the user in a nice interface
   - Like an enhanced dashboard; literally solves this problem by implementing this into your system.

You could alternatively implement security filters, using the User's "Current Job Filter" or whatever to literally filter the data on their device

• But this introduces the need for a sync (which can take time) when you want to switch.
• I prefer to load all their data, and give them a dynamic way to select what they want to focus on.

----------------------------------------------------------------------------------------------------------------

In regards to how to prevent duplicate emails

• Don't filter the user list with a security filter; send the whole master list of users to everyone
• This way when someone tries to enter something, everything is there to check against.

But this means you need to isolate out somehow the specific users that the individual using the system is supposed to see.

• Slices do the job if you've got large static categories ("Active Users", "Unassigned Users"... that sort of thing)
• Creating supporting tables works if you need a system that can dynamically change based on the data entered by the users.
  • This involves creating a separate table to hold records that represent each "group"
  • You then many-to-many connect your users to these groups
  • And therefore when you look at the group, you see it's list of users.  (There's a few more steps involved with this, but you get the idea.)

----------------------------------------------------------------------------------------------------------------

PS: sorry for the late reply.

[dbaum]

As far as I understand your various points, I think my approach that I referenced indeed covers it. In that scenario, every user account in the app is associated with a single company and a user can have multiple app accounts--each with a separate company. The technique of using a join table, which I referenced from @Jonathon and which @MultiTech described earlier today, can streamline the data structure by segregating the user's data that is independent of their association with each company.

@MultiTech is correct that using security filters "introduces the need for a sync (which can take time)" whenever a user needs to switch to their account provisioned by another company. Depending on your needs, that can actually be a critical feature, since the alternative of using slices still downloads all users' data to every device even though it doesn't deliberately display all the data. Also, it is not necessarily an inconvenience (i.e., if in any given app session a user is typically working only in the context of a single company anyway).

[Jonathon]

To add to the conversation

---

@Alan_Thorp wrote:

I can't think of a way to avoid either having a seperate sign up routine for new people where their email is chaecked against the existing multi-company users list, or have the whole users list downloaded every time and just sliced to only show same company users on the device.

---

For what you're looking to do, there is no way to avoid this constraint. This constraint isn't unique to AppSheet - any service, multi-tenant or otherwise, will validate user emails against a broader list of user emails. And they don't expose their entire userbase on the client to do it.

In a single-tenant scenario where you can assume the userbase knows eachother, it may be okay to load the entire user table. In a multi-tenant scenario, if you care about data privacy / PII, I would avoid loading the entire user table. You can perform the check server side either with a Google App Script, or by having an automation that runs with 'Bypass Security Filters' toggled on.

Working within AppSheets constraints, consider using a helper table to submit new users through (e.g. user_invite). For a new email, the automation can create both the user and tenant_user junction. For an existing email, the automation would simply create the junction.

The structure you're aiming for should look something like this:

[dbaum]

---

@Jonathon wrote:

In a multi-tenant scenario, if you care about data privacy / PII, I would avoid loading the entire user table. You can perform the check server side either with a Google App Script, or by having an automation that runs with 'Bypass Security Filters' toggled on.

Working within AppSheets constraints, consider using a helper table to submit new users through (e.g. user_invite). For a new email, the automation can create both the user and tenant_user junction. For an existing email, the automation would simply create the junction.

---

@Alan_Thorp: IMHO, this is the right approach.

In case it's helpful, SUMMARY TIP: User permissions, roles, and settings - Google Cloud Community has a brief overview of multi-tenancy (with a newly added reference to this thread's succinct explanation and diagram from @Jonathon) and various other topics related to user access.