Dynamic Connection Strings, Seperate Databases, and Security

[Steve Workings]

Fishing for thoughts here:

Customer has multiple, identical MySQL databases -- one for each customer (a "customer" is a company with many users).

So, I need to use a dynamic connection string to use the right database based on login.

Security tables are in MySQL, so each customer's database, of course, has their own set of security tables and records.

But, there's only set of Active Link tables allowed in the webroot, and they have to be pointing to something, right? I'm not sure what.

I can't base the active link tables on the dynamic connection string because I don't know what database/table/ to use until the user logs in. And how do I decide how to check for a user login if, at the time of login, I don't know which database might contain the security tables? Chicken & egg problem is a simple analogy.

Or, maybe it's simple and clear and I'm thinking too hard.

So, casting the net here....

[Progytech]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Hi Steve,
For obvious reasons this kind of application should be on a single relational database using a CompanyID, but you already know that.

I am very new to A5 development, but I would handle this in one of these ways:

1. If all users ID are emails and each company users cannot exist in another company, then I would grab the email and try to locate it in any of the databases, once found, then use that connection for the rest of the application, of course I don't know if you can have dynamic connection on the fly.
2. Have a Company DBF table with the CompanyID and ask for the ID in the Login form, the Company DBF would have the connection to the database and start using it after identifying the CompanyID.
3. If you have the option to convince your customer to use a single database, then re-design the database model to include the CompanyID and link that to the UserID so you know which data to show using custom views already filtered by the CompanyID, this way all your forms will be the same for any company and the data will be already filtered with the correct CompanyID and user can add more filters at the A5 level. You would only need the CompanyID in the Parent tables since the details will be link to the Parent FK.

Hope those suggestions give you some ideas, I definitely prefer the last one (#3) as I have used that model before for a .Net WinForm application.

[Steve Workings]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Thanks Edhy. Yes, I know what it "should" be but I have to work with the parameters I've defined here. Not my choice, this is what I inherited, and have to deal with it. I'm looking for solutions to what I'm dealt here, and redesigning it as a "normal" multi-tenant system is not in the cards.

[omagarc]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Seems to me you'll have to have some sort of proxy table where the user logs onto the company whereby you include the connection string to that company database. It'll have to be two log ons. One to define the company and the second to logon ot the company tables.

You should also tell the customer you can't chew gum and whistle at the same time!

[Steve Workings]

Re: Dynamic Connection Strings, Seperate Databases, and Security

I think I have a working model after tonight. It's the same flavor as Omar suggests. Briefly:

- Dialog2 replaces Login component
- UserID is checked against a system table of all system UserIDs (gotta feed this table yet from the "local" databases)
- UserID is used to get Client_ID and associated database names, credentials
- Construct session.__protected__* variable to feed the dynamic connection string
- Important: Security active link tables use the dynamic connection string
- a5ws_login_user() can now be executed, and response.redirect() to home page

Basic work shows this does the job, hope to mostly complete it by tomorrow.

Thanks for watching at home!

[-Jinx-]

Re: Dynamic Connection Strings, Seperate Databases, and Security

This post reminds me of a tool and die place I used to work at. We'd quote jobs that we didn't want ridiculously high so the worst case was we'd get the job and make out like bandits.

Can you create a new merged database that alpha connects to and just set triggers to update the proper existing db? Or pretty much anything else with a mirrored/linked db. That would at least take care of the connection string problem. Otherwise some linked servers might work.

ETA: whoops, looks like you've got it worked out already.

[Terry]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Perhaps I am an oddball, but I don't agree with the prevailing opinion that a multi-tenant database is always the "proper" solution. I previously discussed my reasons for this in the following post:

http://msgboard.alphasoftware.com/al...l=1#post629298

A nonprofit that I consulted with was really blindsided and badly burned when we found out after conversion to a SaaS CRM system that the MySQL database was multi-tenant and precluded our use of the company's advertised adhoc reporting tool without significant additional cost.

Terry

[parkjammer]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Steve... I would be curious to get an update on your final solution. Perhaps you have code snippets to share also. Thanks.

[NormanRMouton]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Steve

Did you get it right?

Can you share it with me.

Willing to pay.

[Steve Workings]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Yes Norman, got it right as described in msg #5. What part(s) do you need help with?

[NormanRMouton]

Re: Dynamic Connection Strings, Seperate Databases, and Security

Steve

- Dialog2 replaces Login component - I understand
- UserID is checked against a system table of all system UserIDs (gotta feed this table yet from the "local" databases) - I understand
- UserID is used to get Client_ID and associated database names, credentials - I understand
- Construct session.__protected__* variable to feed the dynamic connection string - I do not understand
- Important: Security active link tables use the dynamic connection string
- a5ws_login_user() can now be executed, and response.redirect() to home page - I do not understand

Thank You

[lvasic]

Re: Dynamic Connection Strings, Seperate Databases, and Security

I have a multi-tenant database that can use adhoc reporting tools. I've seen this type of set-up for well over 15 years. The adhoc reporting tools use views that are keyed by company ID.