Data-separation in a Symfony Multi-tenant app using Doctrine

Question

I am trying to implement a multi-tenant application, that is - data of all clients in a single database - each shared table has a tenant_id field to separate data

I wish to achieve data separation by adding where('tenant_id = ', $user->getTenantID()) {pseudoc-code} to all SELECT queries I could not find any solution up-front, but here are possible approaches I am considering. 1) crude approach: customizing all fetchAll and fetchOne functions in every class (I will go mad!) 2) using listeners: possibly coding for the preDqlSelect event and adding the 'where' to all queries 3) override buildQuery(): could not find an example of this for front-end 4) implement contentformfilter: again need a pointer

Would appreciate if someone could validate these & comment on efficieny, suitability. Also, if anyone has achieved multitenancy using another strategy, pl share. Thanks

Answer 1

I belive that the more feasible and secure way is to create a new function to retrieve a tenant aware query, this is an example... Replace MyModel with the name of your table:

// lib/model/doctrine/MyModelTable.class.php
class MyModelTable extends Doctrine_Table
{
  public function createTenantAwareQuery($userId)
  {
    $q = $this->createQuery('m')
      ->where('tenant_id = ', $userId);
    return $q;
  }
}

Then to consume this new function just call:

$myVar = Doctrine_Core::getTable('MyModel')->createTenantAwareQuery()
  ->where('something = ', $value);

In this way you create a "Tenant Aware query" when needed... you just use this function when needed... Even in the admin generator there is a way in the configuration file to override the default query method:

# apps/backend/modules/(module)/config/generator.yml
config:
  list:
    table_method: retrieveTenantAwareResult

The only thing left is to create this method.

Hope this answer works for you =)

Answer 2

I'm working out a solution using Doctrine Record Listeners by coding the preDqlSelect event. I think this is the best & easiest way to do things in a generic way, rather than having to modify every Table class and writing Tenant aware queries. With listeners, multi-tenancy will be completely transparent to developers.

Thanks for participating.

Answer 3

I have published the sfMultiTenantPlugin, find it here: http://www.symfony-project.org/plugins/sfMultiTenantPlugin