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?