Handling data access in multi tenant site

Question

I would appreciate some pointers regarding data access/control in a MVC based multi tenant site:

Is there a better/more secure/elegant way to make sure that in a multi tenant site the user can handle only its own data. There are number of tenants using same app: firstTenant.myapp.com, secondTenant.myapp.com...

    //
    // GET: /Customer/
    // show this tenant's customer info only

    public ViewResult Index()
    {
        //get TenantID from on server cache
        int TenantID =  Convert.ToInt16( new AppSettings()["TenantID"]);
        return View(context.Customers.ToList().Where(c => c.TenantID == TenantID));
    }

If a user logs in for the first time and there is no server side cache for this tenant/user- AppSettings checks in db and stores TenantID in the cache.

Each table in database contains the field TenantID and is used to limit access to data only to appropriate Tenant.

So, to come to the point, instead of checking in each action in each controller if data belong to current tenant, can I do something more 'productive'?

Example:

When firstTenant admin tries editing some info for user 4, url has: http://firstTenant.myapp.com/User/Edit/4

Let's say that user with ID 2 belongs to secondTenant. Admin from firstTenant puts http://firstTenant.myapp.com/User/Edit/2 in url, and tries getting info which is not owned by his company.

In order to prevent this in the controller I check if the info being edited is actually owned by current tenant.

    //
    // GET: /User/Edit/

    public ActionResult Edit(int id)
    {
        //set tennant ID
        int TenanatID = Convert.ToInt32(new AppSettings()["TenantID"]);
        //check if asked info is actually owned by this tennant
        User user = context.Userss.Where(u => u.TenantID == TenantID).SingleOrDefault(u => u.UserID == id);

        //in case this tenant doesn't have this user ID, ie.e returned User == null
        //something is wrong, so handle bad request
        //

        return View(user);
    }

Basically this sort of setneeds to be placed in every controller where there is an access to any data. Is there (and how) a better way to handle this? (Filters, attributes...)

score 4 · Accepted Answer · answered Mar 17 '12 at 05:20

I choose to use action filters to do this. It may not be the most elegant solution, but it is the cleanest of the solutions we've tried so far.

I keep the tenant (in our case, it's a team) in the URL like this: https://myapp.com/{team}/tasks/details/1234

I use custom bindings to map {team} into an actual Team object so my action methods look like this:

[AjaxAuthorize, TeamMember, TeamTask("id")]
public ActionResult Details(Team team, Task id)

The TeamMember attribute verifies that the currently logged in user actually belongs to the team. It also verifies that the team actually exists:

public class TeamMemberAttribute : ActionFilterAttribute
{
  public override void OnActionExecuting(ActionExecutingContext filterContext)
  {
    base.OnActionExecuting(filterContext);
    var httpContext = filterContext.RequestContext.HttpContext;

    Team team = filterContext.ActionParameters["team"] as Team;
    long userId = long.Parse(httpContext.User.Identity.Name);

    if (team == null || team.Members.Where(m => m.Id == userId).Count() == 0)
    {
        httpContext.Response.StatusCode = 403;
        ViewResult insufficientPermssions = new ViewResult();
        insufficientPermssions.ViewName = "InsufficientPermissions";
        filterContext.Result = insufficientPermssions;
    }
  }
}

Similarly, the TeamTask attribute ensures that the task in question actually belongs to the team.

Thanks Rages, I'll use filter along with caching TenantID data on the server so I don't hit the DB every time. :) — Tihi, Mar 19 '12 at 12:00

Tihi · Answer 2 · 2012-03-19T12:36:08.910

Since my app is using subdomains (sub1.app.com, sub2.app.com.....) I basically choose to:

a) use something like the following code to cache info about tenants and

b) to call an action filter on each controller as suggested by Ragesh & Doc:

(Following code is from the blog on : http://www.developer.com/design/article.php/10925_3801931_2/Introduction-to-Multi-Tenant-Architecture.htm )

// <summary>
// This class is used to manage the Cached AppSettings
// from the Database
// </summary>
public class AppSettings
{
// <summary>
// This indexer is used to retrieve AppSettings from Memory
// </summary>
public string this[string Name]
{
  get
  {
     //See if we have an AppSettings Cache Item
     if (HttpContext.Current.Cache["AppSettings"] == null)
     {
        int? TenantID = 0;
        //Look up the URL and get the Tenant Info
        using (ApplContext dc =
           new ApplContext())
        {
           Site result =
                  dc.Sites
                  .Where(a => a.Host ==
                     HttpContext.Current.Request.Url.
                        Host.ToLower())
                  .FirstOrDefault();
           if (result != null)
           {
              TenantID = result.SiteID;
           }
        }
        AppSettings.LoadAppSettings(TenantID);
     }

     Hashtable ht =
       (Hashtable)HttpContext.Current.Cache["AppSettings"];
     if (ht.ContainsKey(Name))
     {
        return ht[Name].ToString();
     }
     else
     {
        return string.Empty;
     }
  }
}

// <summary>
// This Method is used to load the app settings from the
// database into memory
// </summary>
public static void LoadAppSettings(int? TenantID)
{
  Hashtable ht = new Hashtable();

  //Now Load the AppSettings
  using (ShoelaceContext dc =
     new ShoelaceContext())
  {

      //settings are turned off
      // no specific settings per user needed currently
     //var results = dc.AppSettings.Where(a =>
     //   a.in_Tenant_Id == TenantID);

     //foreach (var appSetting in results)
     //{
     //   ht.Add(appSetting.vc_Name, appSetting.vc_Value);
     //}
                ht.Add("TenantID", TenantID);

  }

  //Add it into Cache (Have the Cache Expire after 1 Hour)
  HttpContext.Current.Cache.Add("AppSettings",
     ht, null,
     System.Web.Caching.Cache.NoAbsoluteExpiration,
     new TimeSpan(1, 0, 0),
     System.Web.Caching.CacheItemPriority.NotRemovable, null);

     }
  }

score 0 · Answer 3 · answered Mar 16 '12 at 10:54

0

If you want to execute common code like this on every Action in the Controller, you can do this:

protected override void OnActionExecuting(ActionExecutingContext filterContext)
{
    base.OnActionExecuting(filterContext);
    // do your magic here, you can check the session and/or call the database
}

answered Mar 16 '12 at 10:54

Patryk Ćwiek

14,078
3
55
76

That would be good. But how would I set it up so that different tables (with different keys) could be checked. Perhaps by creating [CheckSiteAccessAttribute("table (or class) as parameter", "class.KeyId as second parameter ")] And in attribute code check if for given paremeters (class, key) there is a appropriate TenantID assigned...... – Tihi Mar 16 '12 at 12:26
Yup, that would probably be the best way. You can decorate single Actions with different parameters, guess I didn't read your question close enough the first time. :) – Patryk Ćwiek Mar 16 '12 at 14:30

score 0 · Answer 4 · answered Mar 17 '12 at 05:05

We have developed a multi tenant application using ASP.NET MVC as well and including the tenant ID in every query is a completely acceptable and really necessary thing to do. I'm not sure where you are hosting your application but if you can use SQL Azure they have a new product called Federations that allows you to easily manage multi tenant data. One nice feature is that when you open the connection you can specify the tenant ID and all queries executed thereafter will only effect that tenants data. It is essentially just including their tenant ID in every request for you so you don't have to do it manually. (Note that federating data is not a new concept, Microsoft just released their own implementation of it recently)

Thanks for the info Nick. Plan is to move (and start working on a transfer) the whole app to Azure once we reach a larger number of customer base. :) — Tihi, Mar 19 '12 at 11:20

Handling data access in multi tenant site

4 Answers4

Linked