Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 259
  • Last Modified:

Connecting to MySQL DB over the Web with Access Question???

I have a MySQL DB with a Access 2k Front-End/Dashboard that's been running successfully for quite some time.  There's a lot of custom reports designed around the client's Business Logic over the years.   Currently these reports are emailed to area managers in different cities daily, weekly, monthly and annually which is getting to be a time consuming task due to growth.

One solution is to build a stripped down or light version of the Access front-end containing just the reports used by the area managers for now and distribute it as a self-contained application (Access Runtime) for those machines not running Office.  The concern is ... this will require connecting to the MySQL DB using ODBC outside of the client's firewall on port 3306.  Currently, the Access front-end connects using ODBC when the app starts up but they are all on the LAN inside the firewall.  I occasionally have port 3306 open for testing from my development office and everything runs just fine as far as the reports.

What is the main security downside to this concept (connecting through port 3306)??

Is there a better more secure way to accomplish the objective???

Thanks,

ET

0
Eric Sherman
Asked:
Eric Sherman
  • 3
  • 3
  • 3
3 Solutions
 
Kevin CrossChief Technology OfficerCommented:
The concern is like most open ports. Exploits that explicitly are looking for open port 3306 and insecure MySQL installations are vulnerable. Therefore, some folks will simply have a different external port for MySQL if not for locally. The more secure way is to setup a VPN connection and have the application used through that. It is via the Internet, but secured through SSL tunnel of the VPN session. No ports have to be open to the Internet as the client authenticates and gets a LAN IP Address; therefore, it will work like a client on your network with a slow network connection.
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks for the reply  mwvisa1 ....

<<<<< Therefore, some folks will simply have a different external port for MySQL if not for locally.>>>>>

So, you are saying here ...  one option is to assign MySQL to a different port (not 3306) , correct??

Yes, the VPN was my original recommendation but the client does not want to go that route.  Performance may be an issue.  


ET
0
 
xtermCommented:
There is no reason in the world not to use port 3306 as long as you use basic common sense:

1)  Put a kernel-based firewall on the MySQL server (or a hardware firewall in front of it) and only permit the hosts that need access
2)  The MySQL database already has user/IP access restrictions as a 2nd layer of protection
3)  There's been pretty much zero exploits for MySQL in the past decade even if (1) and (2) weren't factors

That being said, there's no harm in using a different port than 3306, but if people are scanning open ports, they're going to find the new one (the banner will still say its a MySQL server) so the added security is marginal.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
Eric ShermanAccountant/DeveloperAuthor Commented:
Thanks xterm ....

#2 I'm kind of familiar with as you can define those in MySQL administrator.

#1 sounds logical ... How would the hosts be identified????


Thanks,

ET
0
 
xtermCommented:
I would make the users visit a download site, authenticate, and grab the application.

Then you have their IP ($_SERVER["REMOTE_ADDR"] in php) which you can then use to open the port in iptables or ipf, whatever your firewall type is.

If you're real slick, you can set an AT job to delete that firewall permit line after a preset period, and require the user to come back and re-authorize his IP for another X amount of hours/days.

(I didn't pull this out of a hat - I actually do something very similar on one of my own servers.)
0
 
Kevin CrossChief Technology OfficerCommented:
ET, you already indicated you have a Firewall, which is why folks are coming through VPN. If you setup firewall rules for every IP address that is using the application in the firewall. Again, my comment on ports was to answer your question on what is the security risk. I will repeat it is no different than opening any other port. Changing ports is an option and there are plenty of reasons in my world to change MySQL ports, but I do not do so for exposure to the Internet. I use VPN for that.

With DHCP and users moving to different machines, usernames tied to IP addresses or hostnames is not feasible for me; however, that is a good security measure if you have a controlled set of IP addresses/devices involved, especially if dealing with site-to-site usage. In other words, the IP address should be coming from your other site's IP only.

When you create users, you would specify the IP restriction then http://dev.mysql.com/doc/refman/5.1/en/adding-users.html
Yes, you can do the same from MySQL Workbench Admin console.
0
 
Eric ShermanAccountant/DeveloperAuthor Commented:
Ok, thanks for the input xterm and mwvisa1 ....

Again, it's at a client's site .... I just wrote the Front-End Dashboard.  I'm not the IT manager over there.  If the client decides to got that route .... I will offer both of your comments as suggestions.

I test different aspects of the application using their data from my development office and often times their IT dept. will open the port for brief sessions, etc.   The reports seems to run pretty well (mostly SQL based) even though I'm outside their firewall and was the reason for the question.  I knew there would be some security issues involved but sounds like it can be managed properly.

ET
0
 
xtermCommented:
Well, good luck to you - I'm sure myself or mwvisa1 would be happy to assist you further should the need arise.. take care!
0
 
Kevin CrossChief Technology OfficerCommented:
As xterm said, "good luck!"
Best regards and happy coding,

Kevin
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now