Multitenancy web apps

Forums › IT Pro and developers › Multitenancy web apps

Eitsop

584 posts

Ultimate Geek
+1 received by user: 153

ID Verified

#288476 2-Jul-2021 12:17

When people say multi-tenancy web apps they can mean

- Option1 single database with column in database indicating tenant

- Option2 single database with schema for each tenant

- Option 3multiple databases one for each tenant

What is the industry norm? does someone like Xero use option 1, as they are so super confident in their system that they could do this?

Or do they do option 2/3?

nigelramsay

80 posts

Master Geek
+1 received by user: 18

#2737892 2-Jul-2021 13:02

Option 1 is commonly used.

I can't speak for Xero, but it is likely that they would use this approach.

The product that I am responsible for, AddressFinder uses this approach. The tooling that we use comes with ready support for ensuring that it is impossible for different client records to get mixed up. Not to mention the testing that is done to confirm that this is the case.

Approaches 2 and 3 would likely have scaling issues beyond say 100 clients.

--

Nigel Ramsay
Wellington, NZ

Twitter: @nigelramsay

Read my blog at https://nigel.ramsay.org.nz
I work on AddressFinder at Abletech

timmmay

20857 posts

Uber Geek
+1 received by user: 5349

Trusted

Lifetime subscriber

#2737942 2-Jul-2021 14:12

1 is the industry norm, 2 and 3 would be difficult to manage at scale.

tchart

2396 posts

Uber Geek
+1 received by user: 577

ID Verified

Trusted

#2737945 2-Jul-2021 14:27

Depends on what the application is. A SaaS product I use is option 2 & 3.

The whole point of multi-tenant is that one system supports multiple users. Imagine a building with separate floors/offices for each tenant. You might share the elevators and toilets but tenant A cant get onto your floor or into your office (well they shouldnt be allowed to).

Same approach should go for multi-tenant web apps. Data leakage can be a huge issue especially when you are talking financial or personal data.

I would be very surprised/shocked if Xero used something like option 1. The risk is too great. Option 3 isnt always a scalable option unles sthe database is on demand. So option 2 is likley the most common way to silo data.

timmmay

20857 posts

Uber Geek
+1 received by user: 5349

Trusted

Lifetime subscriber

#2737949 2-Jul-2021 14:34

tchart:

I would be very surprised/shocked if Xero used something like option 1. The risk is too great. Option 3 isnt always a scalable option unles sthe database is on demand. So option 2 is likley the most common way to silo data.

Prepare to be surprised. A schema per customer would not scale well. Having a customer ID in a database table or similar is the way most web apps will be written.

BlinkyBill

1443 posts

Uber Geek
+1 received by user: 1100
Inactive user

#2738019 2-Jul-2021 16:57

tchart:

Depends on what the application is. A SaaS product I use is option 2 & 3.

The whole point of multi-tenant is that one system supports multiple users. Imagine a building with separate floors/offices for each tenant. You might share the elevators and toilets but tenant A cant get onto your floor or into your office (well they shouldnt be allowed to).

Same approach should go for multi-tenant web apps. Data leakage can be a huge issue especially when you are talking financial or personal data.

I would be very surprised/shocked if Xero used something like option 1. The risk is too great. Option 3 isnt always a scalable option unles sthe database is on demand. So option 2 is likley the most common way to silo data.

I doubt it. It’s likely to be option 1, the industry norm, with configuration settings to turn on/off features.

I could tell you for sure if you disclose the product. I’m assuming you mean a business system that end-users use.

BlinkyBill

1443 posts

Uber Geek
+1 received by user: 1100
Inactive user

#2738020 2-Jul-2021 17:00

Xero is option 1. I know the Xero guys pretty well.

Samsung

Shop now on Samsung phones, tablets, TVs and more (affiliate link).

mrdrifter

589 posts

Ultimate Geek
+1 received by user: 294

ID Verified

Trusted

#2738063 2-Jul-2021 19:27

Option 1 is pretty typical and has been getting easier with new technology. (Currently working on an application that uses this model and can support ~millions of users with ~thousands of records each). Segregating is actually the easy part with this, it's when you want to enable sharing between accounts for various purposes that it gets interesting.

Option 2 doesn't scale well with schemas and schema management overheads

Option 3 doesn't scale well but is used sometimes, usually for limited audiences such as privileged information on shared platforms i.e. dedicated/specialised govt multi-tenant services.

Eitsop

584 posts

Ultimate Geek
+1 received by user: 153

ID Verified

#2738071 2-Jul-2021 19:55

mrdrifter:Option 1 is pretty typical and has been getting easier with new technology. (Currently working on an application that uses this model and can support ~millions of users with ~thousands of records each). Segregating is actually the easy part with this, it's when you want to enable sharing between accounts for various purposes that it gets interesting.

Is this done with Fine Grain access at database level or somewhere else?

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#2738072 2-Jul-2021 20:00

BlinkyBill:

Xero is option 1. I know the Xero guys pretty well.

Correct. They also use multiple databases to scale out the database infrastructure/layer with sharding.

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#2738123 2-Jul-2021 20:14

attewell:

mrdrifter:Option 1 is pretty typical and has been getting easier with new technology. (Currently working on an application that uses this model and can support ~millions of users with ~thousands of records each). Segregating is actually the easy part with this, it's when you want to enable sharing between accounts for various purposes that it gets interesting.

Is this done with Fine Grain access at database level or somewhere else?

When you have multiple tenants in a single database you typically put the tenant key across all your tables. This allows you to protect data leakage with a simple WHERE clause across all your queries:

SELECT foo
FROM bar
WHERE tenant = xxxx
AND everything else we want to restrict on

This can apply to joins too to help in the same way.

Granted, in theory with good schema design and well designed apps you dont _need_ this everywhere. However the reality is everything has bugs! This helps stop any unintended data leakage. Also, depending on the RDBMS, you can gain some performance advantages with crafty indexes using the tenant key too.

Hope that answered your question - I'm not sure I fully understood what you're asking :-)

mrdrifter

589 posts

Ultimate Geek
+1 received by user: 294

ID Verified

Trusted

#2738125 2-Jul-2021 20:21

attewell:

mrdrifter:Option 1 is pretty typical and has been getting easier with new technology. (Currently working on an application that uses this model and can support ~millions of users with ~thousands of records each). Segregating is actually the easy part with this, it's when you want to enable sharing between accounts for various purposes that it gets interesting.

Is this done with Fine Grain access at database level or somewhere else?

Typically I've seen this with row level security, effectively mapping the user to each relevant row/record with a resource token (in Cosmos DB that I'm using, others will have their own terminology). This way when the user queries the data, the query is trimmed to only the rows that their token matches. For security in my scenario, the queries are all called by services that pass through the user token, and the services are limited to only running queries defined by my team, this stops a user from even attempting to run their own custom/malicious queries. Various other controls are in place to block xss and injection type attacks.

I'm not a Dev, I have specialists for the detail work these days and I'm sure there are other ways to achieve this. My main aim for my application is to ensure the data remains secure, while the platform remains scalable. Also if better technology comes along I can swap out components incrementally. Really the advantage of the cloud as the storage is relatively cheap and you only want to pay for compute when really neccesary.