Opaque tenant identification with SQL Server & NHibernate

We're developing a nowadays-fashionable multi-tenanted SaaS app (shared database, shared schema), and there's one thing I don't like about it:

public class Domain : BusinessObject
{
    public virtual long TenantID
    { get; set; }

    public virtual string Name
    { get; set; }
}

The TenantID is driving me nuts, as it has to be accounted for almost everywhere, and it's a hassle from security standpoint: what happens if a malicious API user changes TenantID to some other value and will mix things up.

What I want to do is to get rid of this TenantID in our domain objects altogether, and to have either NHibernate or SQL Server deal with it.

From what I've already read on the Internets, this can be done with CONTEXT_INFO (here's a NHibernatebased implementation), NHibernate filters, SQL Views and with combination thereof.

Now, my requirements are as follows:

• Remove any mentions of TenantID from domain objects
• ...but have SQL Server insert it where appropriate (I guess this is achieved with default constraints)
• ...and obviously provide support for filtering based on this criteria, so that customers will never see each other's data
• If possible, avoid SQL Server views.
• Have a solution which plays nicely with NHibernate, SQL Servers' MARS and general nature of SaaS apps being highly concurrent

What are your 开发者_如何学Pythonthoughts on that?

---

I find this document to be pretty much the holy grail of MultiTenant. http://msdn.microsoft.com/en-us/library/aa479086.aspx

Take a look at using the one of the approaches such as 'Shared Database Shared Schema' and then connect to the database using a different sql user for each Tenant. Each sql user will see a filtered subset of the data and will only be able to retrieve their own data, and when the insert data, it will automatically be assigned their own tenantID.

You'll find that you wont need to account for the TenantID's in your application any more and can just model the rest of the tables.

---

The approach I use for our SaaS application with shared db and shared schema is shown in the blog post by Jason Young.

This in connection with the post by Jason Dentler you provided in your question and mapping views instead of tables to your domain makes up for a pretty isolated solution. You can completely get rid of TenantID from your Models which I like very much.

In my application I use it with EntityFramework but unfortunately EF does not have such a great thing like DriverConnectionProvider which is really a shame.