Row Level security or Multiple Tables

Question

Im looking for documentation on best practices for the following scenario.

A hosted application contains some "global" data and some "Per-tenant" data. A "Tenant" should have no access to another tenant's tables, and I'd like this to be enforced at DBMS level (in addition to being enforced to Application level).

So as an Example the application may offer to Video rental shops a management system.

The Shop Owners and staff and also information pertaining to people who rent movies are global. Once a customer signed up they would not need to do so again to rent from another shop.

The Shops (tenants) have their own databases (list of customers, inventory, currently booked-out movies, etc). So when a shop-owner logs in the application presents only his own data, which I don't have a problem with.

Should the system create a new set of tables for each Tenant/Shop?

Or Should the system use DBMS security / authorizations... in other words in the back end the inventory of Shop 1 is in the same table as that of Shop 2, but through DBMS security the views only allow selecting rows for that Shop for who the user is signed into the application.

I'm not sure if my question is clear enough?

Answer 1

My bias is to use a single table with appropriate row-level security.

There are potentially huge maintenance advantages to a single set of tables. If you end up with n copies of each table, that means that you have to run n copies of each script every time you want to make a change. Frequently, that means that you end up with at least a few very slightly different versions of the application running at a time because someone forgot to apply script 7 of 23 in a monthly build to one set of tables and someone else created an index on one set of tables to address one customer's issues without adding it to every customer which makes debugging much harder.

A single set of tables has significant scalability advantages. Adding new customers just requires adding a new row to the customer table not deploying a new schema/ database with the new customer's copies of the tables. Adding new customers also doesn't directly add ongoing work for the DBAs. If you have separate copies of the tables, you need someone to deploy tables to create a new customer and every new customer means additional work for the DBA at least to run the scripts one more time every time there is a change.

A single set of tables may also offer performance advantages. If you're using a separate set of tables, each customer would realistically need a separate connection pool in the middle tier. It would defeat the purpose of having separate tables, after all, if your middle tier is connecting as a user that can see every tenant's data because then you'd be implementing row-level security in the middle tier and dealing with all the complexity of multiple sets of tables in the back end. That makes it tough to scale across servers-- do you create a connection pool for every client on every server? Do you send certain clients to certain servers? Do you not preallocate connections and incur the cost of waiting for connections to be established more frequently?

That being said, there are cases where separate tables might be preferred. If your customers are frequently large institutions, for example, separate tables will make it much easier to do things like move a customer to a dedicated box (or at least a dedicated VM) if the customer wants to upgrade to dedicated hardware so that they don't risk performance being affected by other customers. Those large institutions may want greater control over outages and upgrades so it may make sense to have separate tables to allow different customers to be upgraded at different times in order to work with that customer's schedule. Those institutions may find it easier to tell an auditor that their data is physically separate from all other customers rather than explaining that the data is physically intermingled but security controls are in place to guarantee row-level security. If you're going to have relatively few relatively large customers, the amount of maintenance overhead you introduce by having separate tables may not be particularly significant given the general day-to-day maintenance tasks that each client likely requires. In that sort of environment, different clients often have sufficiently different configurations that the problems they encounter are relatively unique even when they're running exactly the same version of the software.