Data-separation in a Symfony Multi-tenant app using Doctrine
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 开发者_Python百科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
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.
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 =)
I have published the sfMultiTenantPlugin, find it here: http://www.symfony-project.org/plugins/sfMultiTenantPlugin
精彩评论