How to implement Tenant View Filter security pattern in a shared database using ASP.NET MVC2 and MS SQL Server

Question

I am starting to build a SaaS line of business application in ASP.NET MVC2 but before I start I want to establish good architecture foundation.

I am going towards a shared database and shared schema approach because the data architecture and business logic will be quite simple and efficiency along with cost effectiveness are key issues.

To ensure good isolation of data between tenants I would like to implement the Tenant View Filter security pattern (take a look here). In order to do that my application has to impersonate different tenants (DB logins) based on the user that is logging in to the application. The login process needs to be as simple as possible (it's not going to be enterprise class software) - so a customer should only input their user name and password.

Users will access their data through their own sub-domain (using Subdomain routing) like http://tenant1.myapp.com or http://tenant2.myapp.com

What is the best way to meet this scenario?

Answer 1

The easiest way is to have a Tenants table which contains a URL field that you match up for all queries coming through.

If a tenant can have multiple URL's, then just have an additional table like TenantAlias which maintains the multiple urls for each tenant.

Cache this table web side as it will be hit a lot; invalidate the cache whenever a value changes.

You can look at DotNetNuke. It is an open source CMS that implements this exact model. I'm using the model in a couple of our apps at it works well.

BTW, for EVERY entity in your system you'll need to have a tenantid column acquired for the above table.