In Multi-Tenant Data Architecture, what is the best way to implement tenant filter view?

Question

I am implementing a SaaS application using ASP.Net MVC 2 and SQL Server database. I am using Shared Tenancy approach.

To filter data, so far I have found 2 approaches.

Option 1: http://msdn.microsoft.com/en-us/library/aa479086.aspx#mlttntda_tvf

Using sql login per tenant. Thus, using SUSER_SID() as a filter in the views

Option 2: http://blogs.imeta.co.uk/jyoung/archive/2010/03/22/845.aspx

Storing tenant id in the Context_Info. Thus, using a sql function that reads tenant id from the Context_Info as a filter in the views.

Can you please help me pick the appropriate option?

Thanks Thanks

Answer 1

I think this comes down to a battle of security models. A DBA may insist you do the former. I, being more pragmatic, would likely pass the tenant ID into my SPs or queries from the application layer.

I would back this up with a whole lot of unit tests that ensure one tenant can never see another tenants data, and I would only store the current tenant on the server in session or simmilar, never in a cookie or in URLs, or anywhere else that can be hacked on the client.

This makes it much easier to add new tenants, as there is no DB config required.

Of course, sessions can be hacked, so you need to take all precautions to ensure that however you store tenant ID on the server, it is immune from spoofs, etc.

Answer 2

I would add that inline table-valued functions can also be useful in building any isolation layers.