How can I use a portion of someones username (such as "justin@org-a") to determine which database config they are given (in this case, the database config that connects to the "org-a" database).
Much longer question:
I have seen a few different wiki articles that use hostname to drive configurations but nothing quite like what I’m looking for here. First, my goal. I’m creating a SaaS solution that is essentially a website that will be used by multiple organizations, all of whom need to keep their data private (no organization can see any data of another organization). My first thought was to create a column named “organization” in every table and tie every piece of data to a specific organization and also give each user an organization_id. Using scopes I could ensure that when viewing a given model, a user only saw data from their organization.
Call it fear, call it over-designing, but I’m worried that at some point having all of these organizations’ data mixed into the same tables will present an issue. Either I make a programming error and forget to limit the data to just that users’ organization, the database goes awry and somehow inserts/updates/reads data incorrectly, who knows. When an organization requests a dump of all of their data, I’d have to sift through it table by table ensuring I only extracted their data.
A better solution, I believe, would be to give each organization their own database. Two possible way to achieve this as I see it:
Have a common "master" database that stores username and organization_id. The site would use the master database to determine which organization-specific data should be used. Then, connect to that organizations database to continue authentication of credentials as well as set the database that will be used for the rest of the site.
Force usernames to contain some sort of organization-identifier such as "justin@org-a" which forces the login script to connect to the "org-a" database which will be used for the rest of the site.
I think the second option would work better in the long run seeing as it prevents the "someone at another organization has my username" issue since all usernames would have a unique organization identifier. It also does not require a "master" database for initial organization identification so I only ever need to connect to one single database.
My question to the great Yii community members is what part of the login process do I need to change (and what do I need to change in it) to allow the username to determine which config is used. Or even how can I user the username to merge a database-specific config into a primary config?
I can’t believe the phrase “multi-tenant” didn’t come to mind earlier since this is exactly what I’m looking for. To me, there are only two multi-tenant options: one database with tenant_id or separate databases (the third option of one database with separate tables just seems like the worst of both other options). Though I’m not housing any government secrets or medical records, I’m opting for the separate database option for a few reasons. First, I never want the chance of an errant piece of code exposing data for another client. Second, it will be much easier to move someone from SaaS to on-premise (or on-premise to SaaS). Third, when a client messes up their data, a restore from backup will be much easier. Essentially, the only thing that gets complicated is database maintenance. This can be easily handled by customizing the Yii migration tool to look through the database, migrating them all one-at-a-time.
The way I’m going to handle this is not through a persons login but through subdomains. Though I don’t have the code built out yet, here’s my plan:
Setup wildcard subdomains that will point to the applications web directory
Create a config file for each client ("db-client1.php" "db-client2.php" etc) that contains database connection info
Update index.php to read the subdomain and merge the client-specific config with the main web.php config
Using this approach I only need client databases (no "master" database), each client is connected to only their database from the beginning, and usernames (along with all other data) will be specific to that client (no auto increment IDs skipping numbers or usernames that look like "justin@client1").
I didn’t like the idea of subdomains at first but in the end this is the most secure option with minimal headaches.
I don’t recommend a separate db for each client. It is pretty simple for your application to care of the security aspect. There are many ways to achieve this using accessrules or RBAC but if you are using back and front end apps you can extend activeRecord and do something like this
/**
* restrict searches to the current client if in the frontend app
*
*/
public static function find()
{
if (in_array('client_id', self::attributes()) === true && Yii::$app->controllerNamespace === 'frontend\controllers') {
$query = parent::find();
$query->where(['client_id' => Yii::$app->params['client']->id]);
return $query;
}
return parent::find();
}
However, if you do decide to, you might want to setup your routing with subdomains using something like this
This way, you don’t need separate configs for each client, just store their details in the client db, read the clientsubdomain parameter from the request and off you go.
namespace frontend\components;
use yii\base\BootstrapInterface;
final class MyLibraryBootstrap implements BootstrapInterface
{
public function bootstrap($app)
{
//TODO: if user is OK
$app->set('db', [
'class' => 'yii\db\Connection',
'dsn' => 'sqlite:/mydbdir/'.$app->user->identity->username.'.sq3'
]);
}
}
