This article describes an implementation of multi-tenancy using Yii and MySQL. “Multi-tenancy” is but one (yet 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.
For the sake of simplicity we’ll only have 3 tables in the database, with emphasis on the tenant and user tables:
tbl_tenant
[sql]
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`dbu` varchar(16) NOT NULL COMMENT 'MySQL username - tenant id in views',
`e_dbpwd` varbinary(1024) NOT NULL COMMENT 'Encrypted MySQL password for db login',
`business_name` varchar(128) NOT NULL COMMENT 'Business name'
tbl_user
[sql]
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`username` varchar(32) NOT NULL COMMENT 'Username -> login id',
`full_name` varchar(32) NOT NULL COMMENT 'User full name',
`tenant_dbu` varchar(16) NOT NULL COMMENT 'Tenant db username',
`tenant_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Foreign key to tenants table',
`tenant_owner` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'User owns the tenant and can make changes to it',
`h_password` char(64) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL COMMENT 'Hashed password',
`user_type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT 'User type; app admin, app staff, tenant admin, tenant user’
tbl_inventory
[sql]
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key',
`item_number` varchar(32) NOT NULL COMMENT 'Item number’,
`description` varchar(256) NOT NULL COMMENT 'Item description’,
`tenant_dbu` varchar(16) NOT NULL COMMENT 'Tenant db username',
`tenant_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'Foreign key to tenants table',
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.
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
[sql]
CREATE SQL SECURITY INVOKER VIEW `vw_tenant` AS
SELECT `tbl_tenant`.`id` AS `id`
`tbl_tenant`.`business_name` AS `business_name`
FROM `tbl_tenant`
WHERE (`tbl_tenant`.`dbu` = substring_index(user(),'@',1));
vw_user
[sql]
CREATE SQL SECURITY INVOKER VIEW `vw_user` AS
SELECT `tbl_user`.`id` AS `id`,
`tbl_user`.`username` AS `username`,
`tbl_user`.`full_name` AS `full_name`,
`tbl_user`.`tenant_id` AS `tenant_id`,
`tbl_user`.`tenant_owner` AS `tenant_owner`,
`tbl_user`.`h_password` AS `h_password`
`tbl_user`.`user_type` AS `user_type`
FROM `tbl_user`
WHERE (`tbl_user`.`tenant_dbu` = substring_index(user(),'@',1));
vw_inventory
[sql]
CREATE SQL SECURITY INVOKER VIEW `vw_inventory` AS
SELECT `tbl_inventory`.`id` AS `id`,
`tbl_inventory`.`item_number ` AS `item_number`,
`tbl_inventory`.`description` AS `description`,
`tbl_inventory`.`tenant_id` AS `tenant_id`,
FROM `tbl_inventory`
WHERE (`tbl_inventory`.`tenant_dbu` = substring_index(user(),'@',1));
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 logged in username returned by the user() function) 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:
[sql]
CREATE TRIGGER `tr_user_before_insert`
BEFORE INSERT ON `tbl_user`
FOR EACH ROW
thisTrigger: BEGIN
IF (SUBSTRING_INDEX(USER(),'@',1) = 'root') // the trigger doesn’t apply to root
THEN
LEAVE thisTrigger;
END IF;
SET new.tenant_dbu = SUBSTRING_INDEX(USER(),'@',1);
END
END
CREATE TRIGGER `tr_inventory_before_insert`
BEFORE INSERT ON `tbl_inventory`
FOR EACH ROW
thisTrigger: BEGIN
IF (SUBSTRING_INDEX(USER(),'@',1) = 'root') // the trigger doesn’t apply to root
THEN
LEAVE thisTrigger;
END IF;
SET new.tenant_dbu = SUBSTRING_INDEX(USER(),'@',1);
END
END
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...
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
[php]
class TTenant extends CActiveRecord
{
public function tableName()
{
return 'tbl_tenant';
}
public function rules()
{
// define all model rules but make sure that ‘dbu’ and ‘e_dbpwd’ are declared
// safe because they’re handled in TenantController
}
public function relations()
{
return array(
'users' => array(self::HAS_MANY, 'TUser', 'tenant_id'), // user table
);
}
public function beforeSave()
{
if ($this->isNewRecord) {
Common::createMySQLUser($this->dbu,$this->e_dbpwd);
}
return parent::beforeSave();
}
public function getListOfAllTenants() // used only by app staff to assign users to tenants
{
$criteria = new CDbCriteria(array(
'select'=>'id, business_name',
'order'=>'business_name ASC',
));
$listOfAllTenants=CHtml::listData($this->findAll($criteria), 'id', 'business_name');
return $listOfAllTenants;
}
// everything else that goes into the model
}
Tenant (MySQL) view model
[php]
class VTenant extends CActiveRecord
{
public function tableName()
{
return 'vw_tenant';
}
public function primaryKey() // required - Yii really doesn’t like database views
{
return 'id';
}
public function rules()
{
// pretty much the same as for the table except for the excluded columns
}
public function relations()
{
return array(
'users' => array(self::HAS_MANY, 'VUser', 'tenant_id'), // user MySQL view
);
}
// if beforeSave() method is needed, don't include MySQL user creation
public function afterSave() // required - Yii really doesn’t like database views
{
if ($this->getIsNewRecord()) {
$this->id = Yii::app()->db->getLastInsertID();
}
return parent::afterSave();
}
}
User table model
[php]
class TUser extends CActiveRecord
{
public function tableName()
{
return 'tbl_user';
}
public function rules()
{
// define all model rules but make sure that ‘tenant_dbu’ is declared
// safe because they’re handled in UserController
}
public function relations()
{
return array(
'tenant' => array(self::BELONGS_TO, 'TTenant', 'tenant_id'), // tenant table
);
}
// everything else that goes into the model
}
User (MySQL) view model
[php]
class VUser extends CActiveRecord
{
public function tableName()
{
return 'vw_user';
}
public function primaryKey() // required - Yii really doesn’t like database views
{
return 'id';
}
public function rules()
{
// pretty much the same as for the table except for the excluded columns
}
public function relations()
{
return array(
'tenant' => array(self::HAS_MANY, 'VTenant', 'tenant_id'), // tenant MySQL view
);
}
public function afterSave() // required - Yii really doesn’t like database views
{
if ($this->getIsNewRecord()) {
$this->id = Yii::app()->db->getLastInsertID();
}
return parent::afterSave();
}
}
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
[php]
class Common extends CComponent
{
public static function checkMySQLUserExists($uname)
{
$sql = "SELECT user FROM mysql.user WHERE user = :user";
$command = Yii::app()->db->createCommand($sql);
$command->bindParam(":user", $uname, PDO::PARAM_STR);
$dataReader=$command->query();
return ($dataReader->rowCount == 0) ? false : true;
}
public static function createMySQLUser($uname, $upwd) // needs hardening against db errors
{
// create user
$sql1 = "CREATE USER :uname@'%' IDENTIFIED BY :upwd";
$command = Yii::app()->db->createCommand($sql1);
$command->bindParam(":uname", $uname, PDO::PARAM_STR);
$command->bindParam(":upwd", $upwd, PDO::PARAM_STR);
$command->execute();
// grant priviledges according to your needs
$sql2 = "GRANT SELECT, INSERT, UPDATE, DELETE, TRIGGER, SHOW VIEW, EXECUTE ON mydb.* TO :uname@'%'";
$command = Yii::app()->db->createCommand($sql2);
$command->bindParam(":uname", $uname, PDO::PARAM_STR);
$command->execute();
// update tables
$sql3 = "FLUSH PRIVILEGES";
$command = Yii::app()->db->createCommand($sql3);
$command->execute();
}
}
protected/components/UserIdentity.php
[php]
class UserIdentity extends CUserIdentity
{
// these are the only places in the whole application where the user actually accessess a table
public function authenticate()
{
$user = TUser::model()->findByAttributes(array('username'=>$this->username));
...
}
protected function loadUser($_id=null)
{
if($this->_model === NULL) {
if($_id !== NULL) {
$this->_model = TUser::model()->findByPk($_id);
}
}
return $this->_model;
}
...
}
Here are the controllers...
TenantController.php
[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,
));
}
}
UserController.php
[php]
class UserController extends Controller
{
public function loadModel($id)
{
// methods isUserTenantAdmin() and isUserAppStaff() are in protected/components/WebUser.php;
// there are different methods for each user_type
// use the controller’s accessRules() method to control access
// this way you only need one controller for both tables and MySQL views
$model = (Yii::app()->user->isUserTenantAdmin()) ? VUser::model()->findByPk($id) : TUser::model()->findByPk($id);
if($model === NULL) {
throw new CHttpException(404,'The requested user does not exist.');
}
return $model;
}
public function actionCreate()
{
// set up model as MySQL view or table depending on user credentials
$model = (Yii::app()->user->isUserTenantAdmin()) ? new VUser : new TUser;
if(Yii::app()->user->isUserTenantAdmin() && isset($_POST['VUser'])) {
$postVars = $_POST['VUser'];
}
elseif (Yii::app()->user->isUserAppStaff() && isset($_POST['TUser'])) {
$postVars = $_POST['TUser'];
}
if(isset($postVars)) {
$model->attributes = $postVars;
if(Yii::app()->user->isUserTenantAdmin()) {
// force new user to belong to same tenant as its admin; the db trigger will insert the MySQL username
$model->tenant_id = VUser::model()->findByPk(Yii::app()->user->id)->tenant_id;
$model->tenant_owner = 0; // tenant owner is defined by app staff only
}
if ($model->validate()) {
if(Yii::app()->user->isUserAppStaff()) {
// force new user to belong to assigned tenant; the listData() in _form.php will insert the tenant_id
$model->tenant_dbu = TTenant::model()->findByPk($model->tenant_id)->dbu;
}
if($model->save(false)) { // already validated
$this->redirect(array('view','id'=>$model->id));
}
}
}
$this->render('create',array(
'model'=>$model,
));
}
public function getFetchAllTenants() // used in views/user/_form.php by app staff because tenant admin can
// only create users in its own tenant account as shown above
{
$modelTenant=new TTenant;
return $modelTenant->ListOfAllTenants;
}
}
InventoryController.php
Follow the same principles as for UserController.php
Almost done, now come the views...
views/tenant/_form.php
The standard _form.php created by Gii is fine except remove all references to columns ‘dbu’ and ‘e_dbpwd’, which are handled inside TenantController as shown above.
views/user/_form.php (uses Bootstrap extension syntax)
[php]
...
<fieldset>
<legend>Fields with * are required.</legend>
<?php echo $form->errorSummary($model); ?>
<?php echo $form->textFieldRow($model,'username',array('maxlength'=>32)); ?>
<?php echo $form->textFieldRow($model,'full_name',array('maxlength'=>32)); ?>
<?php if (Yii::app()->user->isUserAppStaff()) // only app staff can choose tenant
echo $form->dropDownListRow($model,'tenant_id',$this->FetchAllTenants,array('empty'=>'(Select Tenant)'));
?>
<?php if(Yii::app()->user->isUserAppStaff()) // tenant_owner is assigned by app staff only
echo $form->checkBoxRow($model,'tenant_owner');
?>
...
</fieldset>
views/inventory/_form.php
Similar to user/_form.php above.
And finally, the glue that binds everything together is an app behavior that detects the user’s credentials and switches the database connection according to it.
protected/config/main.php
[php]
...
'behaviors' => array(
'onBeginRequest' => array(
'class' => 'application.components.AppStartup'
),
),
...
‘components’=>array(
...
'db'=>array(
'connectionString' => 'mysql:host=localhost;dbname=mydb',
'username' => 'root',
'password' => 'password',
),
...
protected/components/AppStartup.php
[php]
class AppStartup extends CBehavior
{
public function events()
{
return array_merge(parent::events(), array(
'onBeginRequest'=>'beginRequest',
));
}
public function beginRequest()
{
// switch db credentials for logged in users
if (!Yii::app()->user->isGuest) {
$u = TUser::model()->findByPk(Yii::app()->user->id);
$tu = TTenant::model()->findByPk($u->tenant_id)->dbu;
$tp = TTenant::model()->findByPk($u->tenant_id)->e_dbpwd;
Yii::app()->db->setActive(false);
Yii::app()->db->username = $tu;
Yii::app()->db->password = $tp;
Yii::app()->db->setActive(true);
}
}
}
So, for internal operations, app staff users (which would have the root credentials anyway) or if the user is not logged in (a “guest”) the default db connection “root” prevails; otherwise it is overriden in every app request by the user tenant’s credentials. Remember, tenant users only interact with the filtered MySQL views and not directly with any tables; that is reserved for app staff and internal purposes only. Code your Yii controllers and views accordingly.
That’s it. It took me a long time to figure all of this out but it has been working successfully in a production app for six months already, with no problems. Enjoy!
Can I get the code file Webuser.php for checking the function definitions: isUserTenantAdmin() , isUserTenantStaff().