In a multi-tenant data architecture, what is the best way to implement a tenant filter view?

Question

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

So far, I have found 2 approaches for filtering data.

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

Using sql login for each tenant. So using SUSER_SID () as a filter in the views

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

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

Could you help me choose the right option?

Thanks Thanks

Answer 1

I think it comes down to a battle of security models. The database administrator can insist that you do the first. Being more pragmatic, I will most likely pass the tenant ID to my SPs or requests from the application level.

I would support this with a series of unit tests that would guarantee that one tenant would never see other tenant data, and I would only keep the current tenant on the server in a session or simply, never in a cookie or in URLs, or where or else that can be hacked on the client.

, .

, , , , , , , ..

Answer 2

, .