Introduction
This article describes an implementation of multi-tenancy using Yii and MySQL. “Multi-tenancy” is but one (and crucial) aspect of SaaS applications; refer to the IBM document Convert your web application to a multi-tenant SaaS solution for more information on what else makes a webapp a SaaS application.
Moreover, there are three main architectures for multi-tenant databases: separate database, separate schemas and a single database. Refer to the Microsoft document Multi-Tenant Data Architecture for more information.
As MySQL doesn’t have schemas (at least the same way MS SQL Server and Oracle do) the only choices are a separate database for each tenant and single database with “commingled” tenant data. SaaS applications with a relatively small number of tenants can be easily managed with a database for each one, but for large numbers of tenants its maintenance can be unwieldy. Therefore a single database whose tables contain a “tenant_id” column to separate tenant data is the practical choice for many applications. The problem then becomes one of insuring that even a programming error will not expose a tenant’s data to another tenant.
One excellent resource was an article published in 2010 called [Multi-tenant Stategy for SaaS using MySQL 5] which has unfortunately disappeared from the original site but is still available at archive.org. I applied its concepts to a Yii application and am sharing my findings here.
The Database Structure
For the sake of simplicity we’ll only have 3 tables in the database, with emphasis on the tenant and user tables:
tbl_tenant
tbl_user
tbl_inventory
The first thing to notice is that the tenant table has columns for a MySQL username and its password. The reason is that tenant separation begins through an individual MySQL login for each tenant and its corresponding users.
Also notice that both the user and inventory tables not only have an integer foreign key to the tenant table, but also a column with the tenant’s MySQL username as well. Its purpose will be explained next.
The “Trick”
Actually, it’s not a trick but rather a clever use of MySQL’s updateable views. As long as you meet MySQL’s requirements, the strategy works. Therefore we also have 3 MySQL views, one for each table:
vw_tenant
vw_user
vw_inventory
The views are exactly the same as their corresponding tables except that they don’t have the tenant’s MySQL username column; therefore they qualify as “updateable views”. The tenant view doesn’t include the MySQL password either. Moreover, the WHERE clause makes it practically impossible for a tenant (through its MySQL username) access another tenant’s data because the “SQL SECURITY INVOKER” clause enforces it.
One more thing... For each tenant to insert its own data there must be an insert trigger that loads the tenant’s MySQL username into the table record so that the view’s WHERE clause can work:
This will allow tenant users to insert records that will only be visible to their tenant. Repeat for all tables that will hold tenant data. The root user is exempt and the tenant table doesn’t require a trigger because that is handled in the TenantController.
In summary, tenant data separation is accomplished through individual MySQL database logins, coupled with updateable MySQL views filtered by said MySQL login which each logged in user has. Users interact with the MySQL views and not directly with the tables; therefore a programming error or SQL injection or whatever will not expose another tenant’s data.
Now that the database foundation is laid out, next comes...
The Yii Part
Yii doesn’t really like database views, so we have to coerce it. A separate model is required for each table (which the root user - app staff will interact with) and MySQL view (which the tenant users will access):
Tenant table model
Tenant (MySQL) view model
User table model
User (MySQL) view model
Inventory table and MySQL view models
Do the same as for the user table and MySQL view.
After the models come the controllers, but first we need a few helper methods...
protected/components/Common.php
protected/components/UserIdentity.php
Here are the controllers...
TenantController.php
class TenantController extends Controller
{
// as tenant maintenance is mostly handled by the app’s staff and not the tenants
// themselves, it only uses the TTenant model (tbl_tenant).
// if a tenant_owner is allowed to change something (e.g. business_name),
// create a separate AccountController where she can interact with the
// VTenant model (vw_tenant)
public function actionCreate()
{
$model=new TTenant;
if(isset($_POST['TTenant'])) {
$model->attributes=$_POST['TTenant'];
// search for an available MySQL username
$tntdbu = bin2hex(Yii::app()->getSecurityManager()->generateRandomBytes('4'));
// Yii 1.1.14 only, there are other ways to generate a random 8 character hex number
while (Common::checkMySQLUserExists($tntdbu)) {
$tntdbu = bin2hex(Yii::app()->getSecurityManager()->generateRandomBytes('4'));
}
$model->dbu = $tntdbu;
$model->e_dbpwd = bin2hex(Yii::app()->getSecurityManager()->generateRandomBytes('4'));
// or some other clever way to assign a random password
if($model->save()) {
$this->redirect(array('view','id'=>$model->id));
}
}
$this->render('create',array(
'model'=>$model,
));
}
}