Hi,
is there a good method around on using each private dbs for client-users, who then also be able to give access to their data to their own sub-users ?
Basicall, like in Google-AppSheet, where the tables with client-data are kept in the client’s own account, we want to place the client-data at the db-hosting of the client. Each client having different db-host service.
So the app has its own db for the system, and accesses the client-user-db for client-user-data, while sub-users of the client can access the client’s data from any device and any location.
Is there a proven way known for this kind of requirement ?
Regards,
Frank
It really depends on your setup.
Easies way I can imagine:
- each customer uses it’s own virtual host that is pointing to directory common to all customers
- outside of the webroot you can have config files with names that correspond to vhost names and which will define db end point and credentials used by the vhost (one file per vhost)
- above file should not be included in repo (as it contains credentials, right?)
- cake’s app.php should read the config file depending on vhost used and fill db config.
- for local development every dev would have to create config file.
I did not read carefully.
Anyways I would not split db into customers / app directory. If you need to isolate users do it in full.
As stated in this section, " By default, all Table objects will use the default connection".
So one simple way to manage things would be to set the ‘default’ connection differently for each user.
ConnectionManager::setConfig('default', [ 'url' => $user->dns,]);
So there are two physical databases, one at the client and one that is yours (“owned by the app”)? And the CakePHP server has access to both?
If so, use two different database connections, and use the one depending on what data you need.
Since they’re separate databases, you can’t join across them, or mix them in the same query.
I don’t understand your requirements about “sub users”, but I suspect you should read your database manual closely.
Hi and thanks for responses )
Yes, app-owned db and multi client-db.
Ok sure, split queries.
Regarding sub-users, clients can grant access to their data to the users the add under their account.
Quantity of client-users is not limited, other than system resources. So if theoretically 10 mio. clients address 10 mio client-db, it should be handled dynamically, without straining the resources of the server with other than the currently active users.
I’m assuming that clients don’t have access to the cake server, so credentials are secure… for instance, db and other passwords are stored in app.php but that’s fine because that file is not accessible by users. I also assume the schema of all client dbs are the same, only the data differs.
Store each client’s db info and credentials in the app database. At startup (say, Controller.initialize or Controller.startup), read the client’s db information from the app db and create a “clientdb” connection using ConnectionManager::config(). This will be accessible globally. App db will be available via “default” connection and client db via “clientdb” connection.
In each table definition (in the Model/Table/ENTITYTable.php file), add
public static function defaultConnectionName() {return "clientdb";}
Then Cake will know to go to the client database when accessing that table. Pretty neat.
Now that I think about it, that may even allow cross-db queries, not sure if Cake is smart enough to do that although it has enough information…
As for subusers - either store the client db credentials in the subuser’s record in appdb, or have some sort of pointing scheme. It’s basically on you. But the point is that any subuser has access to the main user’s db info and credentials and uses them to create the clientdb connection on startup.
As for scalability, AFAIK Cake doesn’t cache db connections between calls, so you’re OK there. If you did somehow implement db connection caching, the cache would have to be smart enough to handle differing db’s across calls by looking at the db details not the connection name. And manage clientdb connections with some sort of FIFO cache if there were theoretically millions of possible db’s.
This is for Cakephp 3.x, not sure if anything has changed in 4 which would affect the above.