Multiple clients - separate DB - how to handle connstring value
Hi, In a multi-tenant SaaS application every client has aseparate DB and different connstring value. I can't have all connstring values in the web.config. I can retrieve it from the DB and save it in a sessionvariable (on a client login) but I have to carry that value through the layersto the DAL for every request. Also, the usage of session vars is not recommended inasp.net mvc. Is there a better way dealing with this issue?
Bill Dimitrijevic, Dec 09, 2012
That is definitely an option. However, security would be my primary concern, and although your site (theoretically) will have the only keys, there is still added risk. Having your connection string fly back and forth, even encrypted, is a vulnerability. Disgruntled employees could easily take a copy of the keys in retaliation for being terminated.
Weigh the cost of that initial connection string fetch against the decryption and I don't think you'll find that extra hit that substantial. Either way, your going to have that added overhead of coming up with a usable connection string. When weighing performance versus security, I would always choose security. Clients would prefer to hear that you have to upgrade your servers to improve performance, than an apology that you system allowed someone to get their data.
Deen Core, Dec 10, 2012
Thanks for your opinion.
You are right for the web.config. I was thinking the same but I was just too lazy to explain it.
If I use a session table to save the current users connstings then I have to create another DB ('central' DB) with a session table.
That means before each call to the client DB I have to call the 'central' DB and retrieve the connstring.
Every request - at least two calls to a DB server.
What about getting the full connstring value on the login and encrypt/decrypt it.
Carry it through the layers as an encrypted string, and before the DB call (in the DAL layer) decrypted it.
Still not very elegant and efficient.
Bill Dimitrijevic, Dec 10, 2012
While I am by no means an "expert" in design patterns, in fact, I'm rather new to them, and seeking a better understanding, I'm going to offer my humble opinion for a couple of reasons. One, that I may learn from those who tell me that I'm wrong and offer better ways for me think about it. Two, if I am right, then it will reinforce my thinking. So, that being said, here goes...
I can't have all connstring values in the web.config.
While you can have all the connection strings in the web.config file, it's really not the best place for them. The application would have to modify this file every time a new client came on board. While this may be technically doable, it's not reasonable, and I don't think it would scale very well. The possibility of a corrupted web.config file would increase greatly with a popular application.
I can retrieve it from the DB and save it in a sessionvariable (on a client login) but I have to carry that value through the layersto the DAL for every request.
This would be my approach, although I would modify the use of session vars. I would create a client configuration table that would hold the database name and any other unique data required in the connection string. I would use this information to build the connection string when the client connects to the application. Then I would have a Session table that would maintain information that needs to be preserved for the duration of the clients session. The built connection string would be a piece of this information. However, even doing this, you are going to have to have a SessionID that will need to be preserved on the client that ties to the Session table. And yes, that information will have to travel through your application layers, or be included in all your Viewbags or page forms by default so it's passed seamlessly, and reliably. You will also have to develop the additional code to manage the Session table.
I know I'm not talking patterns here, but I would love to see how this approach might translate into a design pattern. Is this a feasible approach, I think so. But I would love to hear from others about the pros and cons to this line of thinking.
This is not meant to be a coded solution to your question, so there is no code included. It is more of a design possibility.
Deen Core, Dec 10, 2012