Dynamically change db connection upon login

Yii 1.1 General Discussion
1

Hi All:

I’ve searched the forum extensively and have tried a number of the solutions posted but I’m still stuck; please help…

I’m developing a multi-tenant SaaS application using the approach in the now extinct link “www.reachcrm.com/2010/03/11/multi-tenant-strategy-for-saas-using-mysql5/”. This approach entails having a separate MySQL user for each tenant, and using MySQL updatable views with a tenantID in the WHERE clause instead of direct access to most tables to keep the tenants’ data separate. The MySQL part works nicely and Gii can create models where the data separation is implicit as long as the db connection is set for the particular tenant.

What I want to do is change the default db connection to use the tenant’s MySQL credentials after login. I don’t want to use a separate ‘db2’ component, thus keeping ActiveRecord simple because the tenants will need to access some common tables. I’ve tried putting the switchover code (both approaches displayed next)




$connection = new CDbConnection('mysql:host=localhost;dbname=mydb', $dbu, $dbp);

$connection->setActive(true);

or




$component=Yii::createComponent(array(  

        'class'=>'CDbConnection',

	'connectionString' => 'mysql:host=localhost;dbname=mydb',

	'username' => $dbu,

	'password' => $dbp,

));

Yii::app()->setComponent('db',$component);

in afterLogin() in the WebUser component and also in the loginAction() action of SiteController. I don’t think putting it in onBeginRequest() would work for me as the site has many pages that should be accessible by guests, plus the administrative staff is not tied to any particular tenant either.

The problem is that when login completes, the db connection gets reset to the default ‘root’ user as specified in config/main.php.

Any ideas on how to solve this are greatly appreciated.

2

my problem is same as you,by using your code i tried the overriding of getDbConnection() in every model,so my primary database is active in the application and coming to particular model my defined database is active

try this

thanks buddy

3

Is there still no solution?

Changing getDbConnection() in CActiveRecord is IMHO no clean solution. There are other places, where the old connection is already referenced, e.g. in the CommandBuilder class.

Do you see any problem using this approach?




        $db = Yii::app()->getComponent('db');

        $db->setActive(false);

        $db->username = 'root';

        $db->password = '';

        $db->setActive(true);