Opaque tenant identification with SQL Server & NHibernate
- by Anton Gogolev
Howdy!
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 thoughts on that?
