This article describes an implementation of multi-tenancy using Yii and MySQL.

Introduction ¶

“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 "true" SaaS application.

Moreover, there are three main architectures for multi-tenant databases: separate database, separate schemas and a single shared 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 a single shared 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 (not shown are indexes and foreign key definitions):

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.

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

[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...

The Yii Part ¶

Yii is limited by the database in its handling of views because primary and foreign keys, indices and even the last inserted id are not exposed by the database to the view; it handles them through the underlying table. 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

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

class VTenant extends CActiveRecord
{
    public function tableName()
    {
        return 'vw_tenant';
    }
    public function primaryKey() // required - database views don't expose this to the schema Yii can access
    {
        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 - database views don't expose this to the schema Yii can access
    {
        if ($this->getIsNewRecord()) {
            $this->id = Yii::app()->db->getLastInsertID(); // I understand this works because MySQL handles it by each individual net connection so there should be no multiuser contention or race conditions
        }
        return parent::afterSave();
    }
}

User table model

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

class VUser extends CActiveRecord
{
    public function tableName()
    {
        return 'vw_user';
    }
    public function primaryKey() // required - database views don't expose this to the schema Yii can access
    {
        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 - database views don't expose this to the schema Yii can access
    {
        if ($this->getIsNewRecord()) {
            $this->id = Yii::app()->db->getLastInsertID(); // I understand this works because MySQL handles it by each individual net connection so there should be no multiuser contention or race conditions
        }
        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

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

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

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

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)

...
<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

...
'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

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);
        }
    }
}

The reason for this is Yii's design; refer to the Application Life Cycle in the Guide. For every request the app is initialized first from the config file, thus resetting the db connection. The only thing that's persisted from one request to the next is the PHP session so that's why one can access Yii::app()->user->id to retrieve the tenant credentials. A different approach that I haven't tried is doing it in the init() method of the Controller.php class from where all other controllers extend.

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, except perhaps retrieving from system wide tables that don't hold any tenant data such as countries/cities/units-of-measure/car makes/etc; 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!

PS - I'm working on a MS SQL Server implementation that uses schemas, filegroups and all that goodness to achieve tenant separation without the headache of commingled backups and disaster recovery. Stay tuned!