Hello,
(Sorry for my google-translate)
I’m putting together a Yii2 advanced application with MySQL; what I want is that the users of the Frontend at the moment of entering their username and password define the database that corresponds to them, in such a way that each company has its own database.
I resolved this as follows:
I created an administration database "app_admin"; in that database I have two tables "user" and "accounts "; the table "user" controls the access of the users and if it is correct its usr and psw I obtain your account data where among others I have "accounts.dsn", "accounts.dbusr "and" accounts.dbpsw "which are the data of access to the database of the account that corresponds to that user that is entering.
I have another database(s) of data for each of the accounts "app_xxxx", "app_yyyy", and so on.
And in the codes in this way:
"frontend/index.php"
...
$application = new yii\web\Application($config);
// is there a user logged?
if (!Yii::$app->user->isGuest) {
// dynamic configuration for 'db' from user->account
Yii::$app->db->dsn = Yii::$app->user->identity->account->dsn; //'mysql:host=localhost;dbname=app_xxxx';
Yii::$app->db->username = Yii::$app->user->identity->account->dbusr; //'root';
Yii::$app->db->password = Yii::$app->user->identity->account->dbpsw; //'';
Yii::$app->db->charset = 'utf8mb4';
}
$application->run();
"common/config/main-local.php"
<?php
return [
'components' => [
'db' => [
'class' => 'yii\db\Connection',
],
'db_admin' => [
'class' => 'yii\db\Connection',
'dsn' => 'mysql:host=localhost;dbname=app_admin',
'username' => 'root',
'password' => '',
'charset' => 'utf8mb4',
],
...
"common/models/user.php"
...
class User extends ActiveRecord implements IdentityInterface
{
...
public static function getDb()
{
// use the "db_admin" application component
return \Yii::$app->db_admin;
}
public function getAccount()
{
return $this->hasOne(Accounts::className(), ['pin' => 'account_pin']);
}
...
"backend/models/accounts.php"
...
class Accounts extends \yii\db\ActiveRecord
{
...
public static function getDb()
{
// use the "db_admin" application component
return \Yii::$app->db_admin;
}
...
"common/models/LoginForm.php"
class LoginForm extends Model
{
...
public function login()
{
if ( ($this->validate() ... ) ) {
// dynamic configuration for 'db' from user->account
Yii::$app->db->dsn = $this->getUser()->account->dsn; //'mysql:host=localhost;dbname=app_xxxx';
Yii::$app->db->username = $this->getUser()->account->dbusr; //'root';
Yii::$app->db->password = $this->getUser()->account->dbpsw; //'';
Yii::$app->db->charset = 'utf8mb4';
return Yii::$app->user->login($this->getUser(), $this->rememberMe ? 3600 * 24 * 30 : 0);
} else {
$this->addError('username', Yii::t('app', 'No tiene acceso a esta sección.'));
return false;
}
}
...
When starting the application the user is a guest and is sent to the login, the user and accounts model are in the app_admin database; if the user’s credentials are correct, the data of his account is obtained to define the corresponding database, for example “app_xxxx”; the rest of the queries are made in that database.
My doubts are:
Am I doing the right thing?
Is this solution safe?
Is there any better way to do it?
On the other hand; I have some tables that depend on "user"; For example, I have a many-to-many relationship with a "junction table" between users and branches: user -> user_subsidiaries -> subsidiaries; As we already were; the table "user" is in the database "app_admin" and the table "subsidiaries" is in each of the database of each account.
How are these types of relationships resolved?
Sorry for extending so much and thank you very much for your attention,
Greetings.