Ever had the worry that when you connect to a database to send some data to it, that some eavesdropper on the network might just be waiting to catch all your passwords (Oracle sends passwords in plain text – by default – go figure) and sensitive data like Credit Card numbers and security codes, bank account details etc?

You have two options, you can ask SQL*Net to encrypt the network traffic, but that then applies to everyone, or you can set up your own system whereby you can tunnel your SQL*Net traffic over an SSH link.

Assume the following:

  • Database = perf10g and Oracle Names/Tnsnames.ora has this alias.
  • Host = greenbird.
  • Port = 1521.
  • You have Putty installed.

The process to set things up is as follows.

Edit Tnsnames.ora: You need a new alias for the SSH version of the database, so add the following to your tnsnames.ora. Don’t worry if you are using Oracle Names:

perf10g_ssh.WORLD =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 9999))
      (SID = perf10g)

Note the change in host and port.

Edit Sqlnet.ora: You must be sure that sqlnet.ora will actually look in tnsnames.ora instead of just Oracle Names, so check for the following:

NAMES.DIRECTORY_PATH= (onames, tnsnames)

Obviously, if you don’t use Oracle Names, the onames part is irrelevant.

Run Putty: If you try to tnsping perf10g_ssh or connect to it, you will get a “No Listener” error. This is because the SSH tunnel is not yet set up and there isn’t a listener on localhost:9999.

In a dos command window, type the following – this assumes that Putty is on your %PATH%:

ssh -N -L 9999:greenbird:1521 username@server

The above causes any traffic on localhost:9999 to be forwarded via SSH to port 1521 on server Greenbird. This is where you have a listener already running.

Username@server is simply the username and server name of any server to which you have access via SSH. dba@bluebird will work, for example. You will need to know the password for the chosen username though.

The SSH session will open and prompt you for a password in the normal manner. Type it in and the tunnel will open. You will also have an SSH session connected to username on servername, but because of the -N option, you can’t actually type any commands into it.

Now if you tnsping perf10g_ssh or connect to it using Toad, SQL*Plus or any application, you will send all your data via SSH to the listener and no-one will be able to eavesdrop on what you are sending.

If you have already set  up your SSH keys in the username on servername than you won’t even be prompted for a password

If you remove the -N option from the command line, you will login as usual but you will be able to type commands. However, once you have made your database connection (to perf10g_ssh) you can type the exit command into the SSH session and it will begin the logout process. Until such time as you exit from the Oracle session, it will sit there unmoving. Once you disconnect from Oracle, the logout will complete and the tunnel will vanish.

Remember to connect to a username@perf10g_ssh to use the SSH tunnelling.

Putty Setup

If you cannot be bothered to type in the command line above and wish to set Putty up with a saved session, the following describes the process.

  • Start Putty as normal.
  • On the Connection page, enter username@servername – in a similar manner to that above.
  • Make sure that the SSH option is selected for the protocol.
  • On the SSH→Tunnels page, make sure both options at the top are unselected. These are “Local ports accept …” and “Remote ports do the same”.
  • Enter 9999 for the Source Port. This is what you need to add in your tnsnames.ora file.
  • Enter greenbird:1521 as the Destination.
  • Make sure that Local is selected.
  • Leave Auto selected for IP version.
  • Click the Add button.
  • Back on the Connection page, fill in a name for the saved session, ssh_perf10g for example.
  • Click the Save button.

Now, and in future, simply select this saved session and click the Open button to create your tunnel.

How to get the most out of Toad for Oracle

Most developers and DBAs use Toad for Oracle to reduce time and effort to develop and manage Oracle databases. But did you know that with Toad for Oracle you can automate administration tasks and proactively manage your databases while embracing performance optimization and risk mitigation? Did you know Toad can now find and control sensitive data across all your Oracle databases? What else can Toad do that you didn’t know about? Which edition will benefit you the most?

Whether you are currently a Toad customer or just getting started with our free 30-day trial, learn more and access Toad for Oracle 13.1.1 – Getting Started Guide.

Start the discussion at forums.toadworld.com