Turtles All the Way ! Removing Clear Text passwords from bash scripts invoking SQL*Plus

When discussing this issue with a colleague recently, we came to the question of how you protect a password that you need to access another password and he observed that, “before you know it, it’s Turtles all the way!“

Regular reader will be unsurprised to learn that I immediately thought of this…

Looks like we’ll be messing about on a shell then

… which may explain some of the more obscure references that might creep into what follows. Look, I’ll do my best not to get turtly carried away.

You may well have stumbled across this post because you’re supporting a venerable ETL application running on Oracle which employs a number Linux shell scripts. These scripts connect to the database via SQL*Plus. The password is hard-coded ( or possibly stored in an environment variable). Either way, it’s available in the application in clear-text and you’d like to do something about it.
Additionally you’ve probably just found out that the database schema’s password, that hasn’t changed for about 15 years, is now going to change once a month and you need a way stopping your scripts from breaking every time this happens.

In summary then, we want to harden our application and specifically :

• protect access to the database from the box that our shell scripts are executing on
• protect the database logon credentials themselves from being exposed

We’re going to start by looking at a typical shell script setup, complete with clear text password.
We’ll then look at the biggest single step we can take to make it more secure.
We’ll then review some the options available to remove the password from our shell scripts altogether.

The Discworld philosopher Didactylos is on record as saying you can’t trust anyone further than you can throw them. Bearing this in mind, we’ll take a look at the security limitations inherent in each solution.

The options we’re going to look at are :

• OS Authentication
• OpenSSL
• Oracle Wallet

We’ll conclude by looking at an architectural approach which does not render the application vulnerable in the same way.

Oracle Instant Client on Ubuntu…with added Aliens

“This is the voice of the Mysterons…have you got any Lemsip ?”
Yep, I’ve caught Deb’s cold and now sound like the alien menace from Captain Scarlet.
This provides a somewhat tenuous link to the subject at hand – namely installing Oracle Instant Client on Ubuntu.
I think I’d better explain. As you probably know, Ubuntu – being a Debian based Distro – uses the Debian packaging mechanism. Oracle, on the other hand, provides Instant Client for Linux in rpm ( RPM Package Manager) format. In order to bridge this divide, we’re going to need to use the alien utility. Look, I did say it was tenuous OK.

I’m doing this on a 32-bit Ubuntu installation ( 10.04, since you ask). If you’re running 64-bit, you’ll need to download the appropriate equivalent files.

Oracle Client on Ubuntu – Installation and Configuration

Having obtained a sick laptop and nursed it back to health ( i.e. installed Ubuntu 10.04), I’ve decided to do something a bit more useful with it.
I want to be able to connect to the Oracle 11g database on my server. This means, installing an Oracle Client.
I’ll be using the machine mainly for SQL*Plus ( although I may well be installing SQLDeveloper shortly). Therefore, rather than mess about downloading the client directly from the Oracle site, I’m going to use the XE client, which is available in the Oracle supplied apt package repository.
NOTE – if you simply must have the full Oracle Instant Client, then you can find details of that installation here.