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?

This is a filtered page: currently showing replies marked as answers. Click here to see full discussion.

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.