|Database traffic is a very attractive target for hostile packet sniffers since data packets can contain highly sensitive information. Protection of this traffic is no longer optional, privacy laws make it mandatory. The way to fight back is for database traffic, if not all traffic, on exposed networks to be encrypted. Linux provides a very economical and effective means of protecting your data packets from prying eyes in a TCP/IP environment.|
We'll look at securing the environment where the database servers live, accessing a remote database with a client local to the database server, and then how to protect multi-tier client server access. Specifically we'll examine encrypting and tunneling a JDBC connection through a firewall from a Windows client.
In a few easy steps we'll use openSSH, from www.openSSH.org, to tunnel database traffic via port 22 using the port forwarding capability in openSSH. Port forwarding allows clients that lack any knowledge or SSH binaries to still tunnel their database requests. Yes, Linux is going to help those functionally challenged desktops again.
From their web site "OpenSSH is a FREE version of the SSH protocol suite of network connectivity...". So openSSH is "open source secure shell", the logical progression from command shells and rsh. Linux has more shells than your average beach. A few examples are ksh, bsh, bash, csh, ash, tcsh, sorry if I omitted your favorite. The rsh shell, or remote shell, provides unencrypted access to another machine. This is great if you can trust every system on the network. OpenSSH encrypts the packets between systems, so packet sniffers can't peek at what you're sending. You don't much care if your grocery list is compromised, but you'll be rather upset if they get your credit card number.
So what does a shell have to do with database packets? You could open a shell locally to the system with the database server, but this won't support client-server applications. We'll look at both of these cases in more detail.
Let me demonstrate my grasp of the obvious: before we worry about the database traffic on the network the servers themselves need to be secure. A good place to start is: "Beginner's guide to armoring Linux" at http://www.spitzner.net/linux.html. This article is a bit dated, eg it refers to ipchains instead of iptables, but it is a good place to get started. Although it is Linux focused, it is also highly relevant to other Unix OS systems. This article is yet another example of the Linux community providing a lucid explanation of a complex problem, and the steps to address the issues.
You'll also need a good firewall environment and Building Internet Firewalls, 2nd Edition, Zwicky, Cooper, Chapman (http://www.oreilly.com/catalog/fire2/index.html) is an excellent resource.
In figure 1 there is a workstation accessing a database on a remote server. The user opens a shell on the host "saam" and runs client database software. The security exposure with telnet includes clear text transmission of the userid, password, and all the data packets.
So don't even think of working at the system called "poh" and using:
[poh/home] $ telnet saam
The userid, password, every query, and all the data returned is available to any packet sniffer snooping your network.
SSH to the rescue: the telnet replacement
SSH provides a simple solution for this environment. Instead of telnet the user runs ssh to access the remote host. For this example we'll use the Postgres database (see www.postgres.org). The user logs into the server poh and runs the following command:
[marty@poh home] $ ssh saam -1 myuserid The authenticity of host 'saam.biz (192.168.1.65)' can't be established. RSA key fingerprint is b2:...yada, yada, yada...a:f1. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added 'saam.biz,192.168.1.65' (RSA) to the list of known hosts. email@example.com's password: Last login: Mon Nov 17 16:35:35 2003 from 192.168.1.103 [myuserid@saam tmp]$ psql mypsqldb
By using the above access, the entire session is encrypted and secure.
For client server applications ssh as a replacement for telnet is not an option. We need a way to protect JDBC, ODBC, and native database clients. These clients connect directly to the listener port of the database server, so there is no opportunity to run ssh from the command line. Some clients offer Secure Sockets Layer (SSL) for encryption, see the section titled Other Options for Secure Traffic for more information.
So how does Linux save the world, or at least a small part of it? SSH port forwarding encrypts traffic between servers. The client software thinks it is connecting to the database on a local server port behind the local firewall, when in fact it is being tunneled to a remote server behind a distant firewall. Figure 2 shows the implementation we'll examine in detail, this time we'll use DB2 Version 8.1 as the server.
The database client application still runs on server poh. The database still resides on the remote server saam. The real beauty of this solution is that the client is totally unaware that there are three hops getting to the remote server saam.
Here are the detailed configuration steps for each system shown in Figure 2. The port forwarding command is entered on "tuxpoh", and the client is configured to connect to "tuxpoh".
SSH forwarding on "tuxpoh"
We want a port on "tuxpoh" forwarded to "saam" by way of "tuxsaam". The ssh man page provides the following syntax:
[tuxpoh/home] $ ssh -g -L 9090:saam:50001 tuxsaam
This opens port 9090 on host "tuxpoh" for forwarding. This is a local port on "tuxpoh" where the database clients will connect. The remote target is host "saam" and the port on the "saam" host is 50001. DB2 is running on "saam" and listening on port 50001 for client connections. The "tuxsaam" host ssh process will provide the ssh forwarding to "saam". Interestingly, the "poh" and "saam" systems may or may not have ssh at all! All the encryption/tunneling work is being done on "tuxpoh" and "tuxsaam".
The flags are important. The -L option specifies port forwarding, The -g option specifies that the listener port on "tuxpoh" should be available to other systems, eg. "poh" on the local network. If you want to use privileged ports the forwarding command needs to be run as root.
To find the port number that DB2 is using issue the following command on the "saam" system:
$ db2 get dbm config | grep SVC TCP/IP Service name (SVCENAME) = db2c_DB2
Then look in the services file for the port number:
$ grep db2c_DB2 /etc/services db2c_DB2 50001/tcp
If your DB2 server is on Win32 look in:
C:\WINNT\system32\drivers\etc>grep db2c_DB2 services db2c_DB2 50001/tcp
Client configuration on system "POH"
The client is configured to access the database server on a port on the system "tuxpoh". There are many different possibilities for clients, the example here is for JDBC. There are a bunch of samples that come with DB2. Look in the sqllib/samples/java/jdbc directory. If you look in the Util.java program it has a number of options for the connection URL. After creating the tunnel per the instructions above, the following syntax will use the TutRead.java program and an openSSH tunnel to query the database on "saam".
dbc>java TutRead tuxpoh 9090 dbuserid yourpasswdhere THIS SAMPLE SHOWS HOW TO READ DATA IN A TABLE. Connect to 'sample' database using JDBC type 4 driver. ---------------------------------------------------------- USE THE SQL STATEMENTS: SELECT TO PERFORM A BASIC SELECT. Perform: SELECT deptnumb, deptname FROM org WHERE deptnumb < 40 Results: DEPTNUMB DEPTNAME -------- -------------- 10 Head Office 15 New England 20 Mid Atlantic 38 South Atlantic Disconnect from 'sample' database.
This method is applicable to other databases, for example see the Postgres documentation at: http://www.postgresql.org/docs/7.4/static/ssh-tunnels.html. It is also not limited to database traffic. There is an example of using this for mail access in LinuxJournal, August 2003, p80.
The openSSH tunnel is not the only option to secure database traffic. Some have issues with ssh forwarding as a security risk, see Zwicky et al, page 503. Depending on the client software, there may be secure sockets layer SSL support, but configuration can be challenging, see: http://www.postgresql.org/docs/7.4/static/ssl-tcp.html.
Another method of securing traffic is SOCKS (not to be confused with Sox, a sore subject in my city circa October, 2003). It turns out that ssh has SOCKS support built in, see the man page and the -D flag. There is good coverage of this topic on page 233 of Zwicky.
Privacy of information is becoming mandatory and unencrypted network packets are a huge exposure. We've looked at ways to encrypt this traffic with low cost Linux alternatives. I hope you find this a useful vehicle to secure your networks.
- Web Click Stream Analysis using Linux Clusters
- Simulating Massively Parallel Database Processing on Linux!
- The Federation - Database Interoperability (Part 1)
- The Federation - Database Interoperability, the Adventure Continues (Part 2)
- Winning Database Configurations: An IBM Informix Database Survey
Marty Lurie started his computer career generating chads while attempting to write Fortran on an IBM 1130. His day job is in Systems Engineering for IBM's Data Management Division, but if pressed he will admit he mostly plays with computers. His favorite program is the one he wrote to connect his Nordic Track to his laptop (the laptop lost two pounds, and lowered its cholesterol by 20%). Marty is an IBM-certified DB2 DBA, IBM-certified Business Intelligence Solutions Professional, and an Informix-certified Professional. He can be reached at firstname.lastname@example.org.