db connect strategy
Posted: Tue Apr 10, 2012 7:21 pm
I've been following the database connect question thread closely, as it pertains to my project directly.
I now understand MUCH better the limitations and dangers of connecting to a remote db directly from a compiled application. The two issues that concern me most are:
1- db access login and password could be easily cracked.
2- most virus protection software flag warnings on odd port usage.
I heard over and over again, how doing so is a bad idea, but given the requirements of my project, I'm compelled to look for a safe workaround. Let me briefly describe this project so you'll understand my motivation.
I've sold many desktop applications over the years. But the marketplace is very different now. With the advent of iOS and Android-- these days-- most applications are being sold for next to nothing. Plus-- there's a LOT more competition than 10 years ago. Thus selling an application, authored by a single developer, for $200+ is nearly impossible. But-- offering a client/server application could easily put me over the top on these higher licence fees, and put me in an arena where there is little competition. No longer would I be offering a application to a single user, but rather to a owner/moderator who would then be managing a community of potentially 100's.
While it's certainly possible that I could require all owner/managers who purchase the license to set up a On-Rev account, to avoid the pitfalls of a direct connection, it would complicate my support and setup work greatly. So I've come up with a possible solution to get around this, and I wanted to get some opinions of how viable this really is.
Each license would be locked to a domain name. The license and db user/password would be encrypted into license files specific to that domain. Thus I would not need to compile a new executable for each new licensed domain, but rather just a new license file.
The common login and password for the user client, would be restricted to a single new account que file. Since only a user with admin privileged can set up such a new account with the needed array of restricted permissions, in addition to the main user's client application, there would be a separate management/maintenance client app that only the owner/moderator of the domain would have. I pretty sure this is possible using mySQL or Postgres permissions.
Once the new accounts requests were written to a new account que file, it would be the the management client, with the admin rights, that would manually or automatically approve and set up the new user with the needed permissions. Thus all the critical management operations would be restricted to a owner/moderator's management client app. Then once a new user is approved, a unique user name and password would give users access to only creating new records based on their user ID. Again, I'm pretty sure db permissions can be set up like this, with these restrictions.
The point is, this way, only the owner/moderator has complete write and delete permissions. Users can only write records that include their user ID. What do you think? I know it requires a LOT more work up front, but is it viable?
And lastly, as for how to prevent each new user from having a odd port attempt warning from their anti-virus software, which is less critical, I still haven't figured that one out.
PS: The main reason I've been fighting the cloud only solution is because my app is very CPU intensive. Not so bad as a desktop app, but if all processing was done in the cloud, scaling up would become very problematic.
Note: edits include many clarifications. Thanks for the feedback.
I now understand MUCH better the limitations and dangers of connecting to a remote db directly from a compiled application. The two issues that concern me most are:
1- db access login and password could be easily cracked.
2- most virus protection software flag warnings on odd port usage.
I heard over and over again, how doing so is a bad idea, but given the requirements of my project, I'm compelled to look for a safe workaround. Let me briefly describe this project so you'll understand my motivation.
I've sold many desktop applications over the years. But the marketplace is very different now. With the advent of iOS and Android-- these days-- most applications are being sold for next to nothing. Plus-- there's a LOT more competition than 10 years ago. Thus selling an application, authored by a single developer, for $200+ is nearly impossible. But-- offering a client/server application could easily put me over the top on these higher licence fees, and put me in an arena where there is little competition. No longer would I be offering a application to a single user, but rather to a owner/moderator who would then be managing a community of potentially 100's.
While it's certainly possible that I could require all owner/managers who purchase the license to set up a On-Rev account, to avoid the pitfalls of a direct connection, it would complicate my support and setup work greatly. So I've come up with a possible solution to get around this, and I wanted to get some opinions of how viable this really is.
Each license would be locked to a domain name. The license and db user/password would be encrypted into license files specific to that domain. Thus I would not need to compile a new executable for each new licensed domain, but rather just a new license file.
The common login and password for the user client, would be restricted to a single new account que file. Since only a user with admin privileged can set up such a new account with the needed array of restricted permissions, in addition to the main user's client application, there would be a separate management/maintenance client app that only the owner/moderator of the domain would have. I pretty sure this is possible using mySQL or Postgres permissions.
Once the new accounts requests were written to a new account que file, it would be the the management client, with the admin rights, that would manually or automatically approve and set up the new user with the needed permissions. Thus all the critical management operations would be restricted to a owner/moderator's management client app. Then once a new user is approved, a unique user name and password would give users access to only creating new records based on their user ID. Again, I'm pretty sure db permissions can be set up like this, with these restrictions.
The point is, this way, only the owner/moderator has complete write and delete permissions. Users can only write records that include their user ID. What do you think? I know it requires a LOT more work up front, but is it viable?
And lastly, as for how to prevent each new user from having a odd port attempt warning from their anti-virus software, which is less critical, I still haven't figured that one out.
PS: The main reason I've been fighting the cloud only solution is because my app is very CPU intensive. Not so bad as a desktop app, but if all processing was done in the cloud, scaling up would become very problematic.
Note: edits include many clarifications. Thanks for the feedback.
