Hello everyone,
I’ve searched the forum without finding some recen information that addresses this question.
Short version of my question>
How do you change the Schema, User name and Password of a Database connection on runtime?
Now, If you want more background>
Our solution is multitenant and allows many companies to access the same Database instance, it allows up to 900 companies on the same instance. We separate the data of each company by using different Schemas We do NOT do it using a column (like say companyid) because we have specific requirements and reasons that won’t be changing anytime soon.
Now, on our current .Net solution, we manage the connection strings so that they point to the correct schema depending on the selected company, this is done right after the user logs in to a company. Also, each user of our solution users connect to the database using their own database login. The reason for this is that it allows to manage security at the database level as well and the database audit logs can show who did what and when, EVEN THOUGH they did it from outside our solution. I’m aware that Servoy can manage many security settings for database objects, I’m also aware of its logging capabilities. However, here, its a regulatory requirement for the Financial sector to be able to manage user acess at the database level allowing third party products (for example Forti-DB) to produce vulnerabilty, usage and compliance reports.
Any ideas on how to do change the connection parameters on runtime? If it was possible to manage the connection strings ourselves, we’d be happy to. Thanks in advance.
jd2p:
Short version of my question>
How do you change the Schema, User name and Password of a Database connection on runtime?
Short answer: You can’t.
For one it would affect ALL clients on the server, not just the one you want it for. Keep in mind this is a 3-tier architecture, not a 2-tier one.
What you need to do is define a connection for each schema and then ‘switch server’ when a tenant identifies itself by logging in. This switching has to be done before any of the forms are loaded that use this connection or else they will use the old connection.
You can check the checkbox in the connection settings that it’s a clone of schema x (the original). This way it will update all schemas when you import a new solution that has schema changes in them.
Also be aware that the rawSQL plugin doesn’t support switchserver so you need to use a global for the connection name I guess. (unless Servoy started supporting it already, have to check the release notes).
What you need to do is define a connection for each schema and then ‘switch server’ when a tenant identifies itself by logging in.
Wow, that hit me hard, one of our customers has 90 companies on a single instance which means we would have to define each connection. Also, that addresses the Schema parameter, but each application user has their own database user, would we need to define these connections as well? Meaning that if we have 5 users for each of the 90 companies, we would have to define 450 connections, correct? Is there way to create them programatically, even though it will require the application server to restart in order for changes to take effect?
There has to be many software companies that manage multitenancy by using separate repositories and it is also a common requirement to use independent database users as well. Is there anyone else out there? I would really appreciate the input and would like to see if we’re really alone on this or it’s a feature that would be useful to the community.
Sounds to me you have been designing for a 2-tier application. Servoy is a 3-tier architecture so all connections are controlled by the server, not the clients.
Although I believe there is some functionality coming in the next release (after 6.1) that does allow some control over the connection definition. I would have to fact-check that but something was announced at ServoyWorld if I am not mistaken.
ROCLASI:
Hi jd,
Sounds to me you have been designing for a 2-tier application.
I suppose we have, since we’ve been primarily moved by the customer needs, regional market tendencies and regulatory requirements (the separate database user IS a regulatory requirement for Financial institutions here) and we have started using Servoy up until this year. However, I don’t think that being a 3-tier architecture is mutually exclusive with allowing application users to connect with their own database logins.
Although I believe that is some functionality coming in the next release
That would be great, those anyone else know more about this?
Thanks very much for your input Robert! Maybe someone else that comes from our old-fashioned-2-tier-ways can share their experience with similar cases?
Hey everyone,
Is being able to set the username/pwd for a connection at runtime a feature someone else would find useful? I would like to see if other members would give good use to this and if it’s worth posting a feature request.
a little late maybe, but this feature would probably prove useful to us too. So if you’re still interested we could open a feature request on JIRA. Let me know.
Defining hundreds of connections for every and each tenant strikes me as quite cumbersome, especially given how the server interface handles all these, in a not very handy way, that is.
Hello studiomiazzo! You’re not late at all, actually I kept trying to get more people to support this and even created another thread in which I explained in more detail… you can see it here->https://www.servoy.com/forum/viewtopic.php?f=22&t=19053. So we could use some of that to create the support request, or maybe you could add to that if you see something’s missing out? Im glad to know I’m not alone in this, so far I only found one more member that would up-vote this feature.