Mutli tenancy backstop check on Linq-to-sql entity load()

Question

I am writing an aspx application that will host 1000's of small customers in a communal SQL Server database. All entities will be created and loaded via Linq-To-Sql.

Surrogate keys (identity columns) will be used throughout the schema for all table relationships and so starting with a root customer object I should be able to navigate to exclusive sets of data for a particular customer using regular Linq queries (SQL joins).

However from a security standpoint the above is a bit fragile so I wish to add an extra layer of tenancy check as a security backstop. All entities in my entity model will have a non-indexed int TenantId field.

I am looking for critical comments about this solution from a performance perspective.

public partial class MyLinqEntity

  partial void OnLoaded() // linq-to-sql extensibility function
  {
    if ( this.TennantId != HttpContext.Current.Session["tenantId"] )
      throw new ApplicationException("Logic error, LINQ query crossed tenantId data                      boundary");
  }

  partial void OnCreated() // linq-to-sql extensibility function
  {
    this.TennantId = HttpContext.Current.Session["tenantId"] );
  }

Answer 1

Sorry this is mostly random thoughts….

I do not like all the objects depending on HttpContext.

Also I don’t know if looking in the session for each object is fast enough. I think you will be OK on speed, as a database lookup will normally be a lot slower then anything you do in process.

I would tend to use a dependency injection framework to auto create an object that has session scope to do the check. However if you don’t have a need for a dependency injection framework elsewhere this will be over kill.

As all your database rows will have tenantId column, I hope you can move the check into a linq-to-sql “row read callback”, so you don’t have to put it into each object. I don’t know “linq-to-sql”, however I expect you could hook into it’s query creation framework and add a “where tenanted = xx” to all database queries.

Having a “where tenanted = xx” in all quarries will let partition the database if needed by customer, so making “table scans” etc cheaper.