Yii 1.1: A Multi-Tenant Strategy using Yii and MySQL

17 followers

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


`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


`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


`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


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


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


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:

CREATE TRIGGER `tr_user_before_insert`
BEFORE INSERT ON `tbl_user`
FOR EACH ROW
   thisTrigger: BEGIN
     IF (SUBSTRING_INDEX(USER(),'@',1) = 'root') // the trigger doesnt 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 doesnt 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!

Total 7 comments

#15940 report it
JFReyes at 2013/12/31 08:39am
RE: yii preferences

@nineinchnick:

Point taken... I'll update the wiki to reflect this. Thanks!

#15939 report it
nineinchnick at 2013/12/31 08:11am
yii preferences

I like the article. I use PostgreSQL daily and it looks like it could be used here as well.

I wouldn't say that Yii doesn't like views. The databases don't, because they don't allow to specify a PK or FKs for a view. Probably because they're constraints and view gets the data from the underlying query, on which those constraints don't apply. So it's not really possible, and such Yii can't take a hint from the schema on what is the PK.

So it's not about preferences. Don't make Yii look grumpy :-)

#15911 report it
JFReyes at 2013/12/28 03:38pm
RE: linking

@blaces:

I'm not sure I understand your question but here it goes...

  1. TenantController is intended for the app staff only, to manage all tenants.
  2. UserController is also for the app staff to manage all users, and perhaps also the tenant_owner to manage her users.
  3. If you want something like self-registration, create a separate RegistrationController that will collect the registration info through a CFormModel and view, then create the TTenant and TUser records directly without going through any other controllers, using the MySQL root account. There would be some duplicate code from the TenantController and UserController actionCreate() methods inside it.
  4. If you want a user to be able to update its profile, change password, etc. then create a separate AccountController that will allow that through the VUser model.

In general terms you create a controller and its views to handle a particular feature of the app; inside that controller you can use any and all models, but remember that if if it receives tenant user input it's best to stick to models based on the filtered MySQL views (e.g. VUser, VInventory, etc).

#15909 report it
blaces at 2013/12/28 01:18pm
linking

No, I just wanted to know, which strategy is used to a custom app controller connect to the TalentController, how they are work together. I found many strategy about it on the internet. (This is why I said the example, I don't wanna a complete registration. I just wanna know how it works together :) )

However I am looking for the continue of this article :)

#15905 report it
JFReyes at 2013/12/28 07:18am
Thanks...

@selorm: Thanks!

@blaces: I'm glad you liked it but the article is about multi-tenancy only and not self-registration, subscription billing, tenant customization and all the other stuff that makes up a complete SaaS application. Some of these subjects are discussed in the forum and wikis, though perhaps not in a SaaS context, but are still applicable.

#15904 report it
blaces at 2013/12/28 06:55am
Nice work

Nice work man :). I will wanna do a multi-tenant webapp too. However, I see here you do an theory implementation of the multi-tenant strategy. I prefer if you can give more examples. (For example how view a gallery, or registration form for each tenants/webapps, how it is work with your TenantController etc.)

#15903 report it
selorm at 2013/12/28 06:03am
great insight

Really interesting findings you have here. I'm developing a sass based app with yii and this would really be useful.Thanks

Leave a comment

Please to leave your comment.

Write new article
  • Written by: JFReyes
  • Updated by: CeBe
  • Category: How-tos
  • Yii Version: 1.1
  • Votes: +8
  • Viewed: 9,054 times
  • Created on: Dec 27, 2013
  • Last updated: Apr 14, 2014
  • Tags: saas, multi-tenant