MySQL communication around a firewall

[H.L]

My Java web start application connects to a remote MySQL server hosted
on a Tomcat server via the MySQL connector JDBC driver. The idea was
that it connects through the standard port 3306, but the administrators
are reluctant to allow anything through the firewall. We have raised the
idea of communicating through another port. That port would be opened to
connections from outside the firewall. This seems to imply that the
client must also have opened that port. What kind of problem is this
likely to cause? I would have thought that all ports were open on the
client side. Perhaps this is just the case for 3306 and some other
default values. It would then become an issue of whether we want to ask
users to start mucking around with their network settings. I need to
shed some light on this thing. Thanks in advance.

Håkan Lane

 

[Matt Humphrey]

My Java web start application connects to a remote MySQL server hosted
on a Tomcat server via the MySQL connector JDBC driver. The idea was
that it connects through the standard port 3306, but the administrators
are reluctant to allow anything through the firewall. We have raised the
idea of communicating through another port. That port would be opened to
connections from outside the firewall. This seems to imply that the
client must also have opened that port. What kind of problem is this
likely to cause? I would have thought that all ports were open on the
client side. Perhaps this is just the case for 3306 and some other
default values. It would then become an issue of whether we want to ask
users to start mucking around with their network settings. I need to
shed some light on this thing. Thanks in advance.

Some places limit outgoing connections to only well-known ports, e.g. 80,
22, 23, etc. It's not a limitation on the client port number, but on the
outgoing connection to an unapproved service. Can you switch your protocol
to port 80 on a different host or virtual host?

Matt Humphrey http://www.iviz.com/

 

[Nigel Wade]

My Java web start application connects to a remote MySQL server hosted
on a Tomcat server via the MySQL connector JDBC driver. The idea was
that it connects through the standard port 3306, but the administrators
are reluctant to allow anything through the firewall.

Hardly surprising. Allowing outside connection to a database server is not
something I would contemplate.

We have raised the
idea of communicating through another port. That port would be opened to
connections from outside the firewall. This seems to imply that the
client must also have opened that port. What kind of problem is this
likely to cause? I would have thought that all ports were open on the
client side. Perhaps this is just the case for 3306 and some other
default values.

But you still have exactly the same (very serious) security implication of
exposing your database to the outside world. Your web client has to
authenticate to the database, and that authentication is hard coded into the
client for all to see. Anyone can extract it and connect directly to the
database and presumably issue SQL requests you did not intend. At the very
least it means you have to take steps to harden the database.

It would then become an issue of whether we want to ask
users to start mucking around with their network settings. I need to
shed some light on this thing. Thanks in advance.

The normal solution to this problem is to use a servlet acting as a proxy. Your
web client talks to the servlet, the servlet in turn talks to the database. The
servlet is behind the firewall so should be ok connecting to the database. The
database authentication is done there, by the servlet, protected by the
firewall. The servlet controls what actions the client can perform on the
database.

If the servlet is deployed to the same server that the web client downloads from
so much the better as the web client won't need to be signed.

 

[Lew]

The normal solution to this problem is to use a servlet acting as a proxy. Your
web client talks to the servlet, the servlet in turn talks to the database. The
servlet is behind the firewall so should be ok connecting to the database. The
database authentication is done there, by the servlet, protected by the
firewall. The servlet controls what actions the client can perform on the
database.

If the servlet is deployed to the same server that the web client downloads from
so much the better as the web client won't need to be signed.

This is an example of a very powerful pattern I learned as the "resource
manager" paradigm. I don't know the formal pattern name, if there is one, but
the idea is that a shared or otherwise vital resource sits behind a manager,
also called a dispatcher, a controller or a driver. There may be many worker
tasks or threads or components within the resource itself, but the central
manager collects all requests and is in charge of dispatching all work to the
resource, and conversely responsible for collecting the resource's response
and replying with it to the requester.

The database engine itself is such a resource manager, where the data store
itself is the resource. Nigel's solution brings that out one more layer: the
entire DBMS, engine included, is a resource from the point of view of the web
application. The dispatch servlet becomes the resource manager, concerned not
only with the data /per se/ but with security and authorization. Adding that
layer of indirection protects the data store from inconsistent or malicious
actions.

Note that resource managers need not necessarily be singletons. The data
access servlet can have multiple concurrent instances, possibly distributed
about a server farm, for complete scalability with no conflict with its
purpose: to manage authorized access to the data resource.

 

[Arne Vajhøj]

My Java web start application connects to a remote MySQL server hosted
on a Tomcat server via the MySQL connector JDBC driver. The idea was
that it connects through the standard port 3306, but the administrators
are reluctant to allow anything through the firewall. We have raised the
idea of communicating through another port. That port would be opened to
connections from outside the firewall. This seems to imply that the
client must also have opened that port. What kind of problem is this
likely to cause? I would have thought that all ports were open on the
client side. Perhaps this is just the case for 3306 and some other
default values. It would then become an issue of whether we want to ask
users to start mucking around with their network settings. I need to
shed some light on this thing. Thanks in advance.

If you can get permission to run a web or app server that connects to
MySQL and they will route HTTP traffic to that server through the
firewall, then you an go that route.

You can expose a web service. Java web app or PHP or ASP.NET.

It would be a fair assumption that the clients will be able to
do outbound HTTP either direct or via a proxy server.

Arne