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.

A typical Application Shell Script

Let’s start with some assumptions :

• the shell scripts are all part of an application codebase rather than being for any database admin tasks
• the scripts are all owned by a single OS user
• That single OS account may be used by one or more people (e.g. a DBA and a Support Developer).
• the scripts run unattended ( probably as part of an overnight batch, or triggered by a CRON job)

In addition, you should know that the examples that follow were tested on CentOS7 and Oracle 18c XE. However, it’s worth noting that the functionality of the tools used here have not changed for several releases ( both OS and database).

First the good news, each script calls an environment setup script so that we only have to change the password in one place. The script is called scriptenv.sh :

As an example we have this application script (om.sh) :

…which runs the query in vorbis.sql :

It’s probably worth noting here that, as far as I can see, putting the password in an environment variable on Linux is not outrageously insecure .

Connecting as the Application Owner

Just a quick side note :

This sort of arrangement often involves connecting to the database as the application owner.
This is quite convenient as the Application Owner schema implicitly has full privileges on all of the application’s database objects. However, it also means that the credentials we’re using to connect to the database are highly privileged.
It’s therefore an outstandingly good idea to consider using a less privileged account to connect.
There are several ways to achieve this (e.g. using an account which has access to the application only via execute permissions on the Application Owner’s PL/SQL packages).

For now though, we’re going to start with one simple change which will harden this application more than implementing any of the tools under discussion …

File Permissions

The solutions to this problem tend to share the premise that if you have access to the files on the OS, then you have access to the database.
Therefore, in this scenario, the most important step you can take in protecting your database from unauthorised access is to ensure that only those OS users that need it, have permissions to see our application scripts.

Remember that the OS account may be accessed by multiple members of the Support Team. By the very fact of these people having been given the credentials for the OS account, they are also authorised to connect to the database.
In order to run a bash script on Linux, both Read and Execute permissions are required. As the script owner, the OS user will also need write access to change the scripts.
Therefore, our first step should be to ensure that all of the files we’ve got in our app have permissions set such that only the OS user can use them :

Permissions are now restricted to the script owner only :

-rwx------ 1 atuin atuin  141 Mar 18 21:31 om.sh
-rwx------ 1 atuin atuin   53 Mar 18 21:26 scriptenv.sh
-rw------- 1 atuin atuin  201 Mar 21 14:09 vorbis.sql

There you have it. At a stroke your application is much more secure. Anything beyond that is likely to be a variation on the theme of obfuscation where the password is concerned.

On the other hand, stopping here would make this post a bit short and we’d still have those passwords in our scripts…

Hang on a minute, doesn’t Oracle have an Authentication strategy that explicitly embraces the concept of delegating authentication to the OS ?

OS Authentication

Older readers may well recall a time when it was common for Oracle usernames to include the prefix “OPS$“.
This string is the default value for the os_authent_prefix database initialization parameter.
By creating a database user with this prefix, it’s possible to delegate authentication to the OS, as we’ll now demonstrate by creating an OS user called simony who will connect to the database as the externally identified user ops$simony.

We’ll start by creating the account on the linux server :

We’re going to override the standard TNS config files on the server with private ones for this user by setting the TNS_ADMIN environment variable to point to a custom set of files, which we’ll create in a directory under our users $HOME :

This directory needs to contain a tnsnames.ora, which looks like this…

eta =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = didactylos.virtualbox)(PORT = 1522))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = ETA)
      )
    )

…and a sqlnet.ora, which contains a directive specific to OS Authentication :

sqlnet.authentication_services = (NTS)

In the database, we need to check a couple of parameters :

NAME                 VALUE                
-------------------- -------------------- 
remote_os_authent    FALSE                
os_authent_prefix    ops$                 

   

We need to change the remote_os_authent parameter to TRUE.

In the meantime, as an appropriately privileged user :

Now, we can create the ops$ user in the database. To do this, we need to make sure where in the correct target PDB…

We now need to re-start the database for the parameter change to take effect.

Back on the linux server, as simony, we can now connect to the database without specifying a password :

So, provided you’ve authenticated as simony on the linux server, Oracle will allow you to connect to the database. Well, not quite…

On a different machine connected to the same network, I’ve created a user called simony and re-created the tns_admin directory and files. Oracle however, is not fussy about which host I’m connecting from :

Provided you can reach the database, and you’re current client session is under a user named simony, you can connect to the database. In effect, the database authentication is now on username alone.

It’s worth noting that, on Windows clients, configuring OS Authentication requires that the Windows Domain or machine name is included in the database OPS$ username and is therefore evaluated when connecting.

As we can see, the same does not apply on Linux. Therefore, you may well consider this to be rather less secure than having passwords in clear text in your bash scripts.

Encrypted passwords with OpenSSL

The next option to rid ourselves of those vulgar clear text passwords is OpenSSL, which we can use to :

• encrypt the password and save it to a file
• read the encrypted password from the file and decrypt it at runtime

The openssl utility comes as standard on most modern linux distros. We can see how it works by encrypting a simple test string :

This will produce an output that may look something like this :

U2FsdGVkX1/kpUzVwi4UNxkcjcxyK4cklEwfmZGgiAQ=

Note that you won’t get the same output every time due to the application of the salt.

We can decrypt the string using the command :

…which returns the string we originally encrypted :

Abraxas

So, the first thing we need is a script to set/change the password.
In this instance, the script ( update_db_creds.sh) is owned by the same OS user that runs the shell scripts, but is designed to run interactively…

This script prompts for four parameters.
The Database Schema and Database Name are associated with the password by the filename in which the encrypted password string is stored.
The password itself, together with the passphrase used in the OpenSSL command are accepted but not displayed.
Once the script has run, there are two files created in the current directory, both of which have read and write permissions for the file owner only.

An example run would look like this :

This creates these files :

ls -dl .[^.]* 
-rw------- 1 mike mike 16 Mar 23 09:57 .brutha_hr_eta.txt
-rw------- 1 mike mike 65 Mar 23 09:57 .sweeper_hr_eta.enc

Next, we need the script that’ll do the password retrieval and decryption for us at runtime. This one is called get_db_password.sh :

Now, we can plug this into our existing environment setup script…

…and om.sh will execute as before, this time without the need for any of that mucky clear-text password nonsense…

Well, that’s a bit better. We’ve managed to protect the password…to an extent.
As before, access to the database is protected by the os file permissions on the application files.
Whilst the password itself is obfuscated, decryption would be trivial for anyone connected as the script owner.

OK, we’ve “shelled” out all that cash for Oracle, it must include a solution to this problem, right ?

Oracle Wallet

Q : when is an Oracle Client not an Oracle Client ?
A : when you realise that you can’t find the mkstore file that all the Oracle Wallet write-ups tell you to run as a first step.

If you’re using one or more downloads that comprise Oracle Instant Client, you will most likely come up against this issue.
Depending on which components you have installed, you may be able to take advantage of this workaround.

It’s worth noting here that, at least in some circumstances, Oracle’s intention was that the wallet be created on the Database Server and then copied to the required application servers. To quote from Oracle WebLogic Server documentation :

Oracle recommends that you create and manage the Wallet in a database environment. This environment provides all the necessary commands and libraries, including the $ORACLE_HOME/oracle_common/bin/mkstore command. Often this task is completed by a database administrator and provided for use by the client. A configured Wallet consists of two files, cwallet.sso and ewallet.p12 stored in a secure Wallet directory

If you’re not creating the wallet on your database server, it’s the full-fat client that you need – the one that’s designed to run on Application Servers and which can be found together with the Database Software downloads rather than the Instant Client downloads.
For example, you can find the 21c Oracle client by navigating here, selecting the option to show all downloads, then scrolling all the way down to the Oracle client.

Wallet Setup

Right, now we’ve got that straight, let’s logon to the OS as the script owner ( atuin) and create a couple of directories and set the appropriate permissions :

The next step is to create a wallet – in the wallet directory :

At this point you will be prompted for a password.
NOTE – you’ll be prompted for this password pretty much anytime you access the wallet, so don’t forget it !.
Also, be warned, if it’s not up to snuff, mkstore will reject your password with :

PKI-01002: Invalid password. Passwords must have a minimum length of eight characters and contain alphabetic characters combined with numbers or special characters.

Now we need to get Oracle to look in the wallet every time we connect from atuin.

By setting the $TNS_ADMIN environment variable we can override the “standard” tns configuration files ( usually found in $ORACLE_HOME/network/admin) with our own.

We need to create the config files we need. We’ll do this in the newly created tns_admin directory.
Let’s start with the sqlnet.ora :

This tells Oracle to use the specified wallet when connecting.

Now for the tnsnames.ora :

In keeping with our file permissions policy…

ls -l
total 8
-rw-------. 1 atuin atuin 157 Mar 25 17:13 sqlnet.ora
-rw-------. 1 atuin atuin 203 Mar 25 17:14 tnsnames.ora

Now all we need to do is to add a credential to our wallet with the same name as the entry in the tnsnames.ora.

You can accomplish this with

…where <password_for_hr> is the password for the database hr user.

However, remember that we’re on Linux here, which has that ever-so-helpful history command.
Whilst the password change may be handled by a single admin, such as a DBA, anyone else connecting as that user (e.g. the rest of the support team) will be able to view it’s history.
Therefore, if we were to run this command, we could see the unencrypted password in the history :

    7  mkstore -wrl /home/atuin/wallet -createCredential hr_on_eta hr theTurtlem0ves!

Now we can fiddle around in bash to ensure that the command isn’t captured in the history.
Perhaps a simpler option however, is to not provide the password on the command line and instead make mkstore prompt for it :

As the password no longer appears on the command line, it doesn’t show up in the history :

   22  mkstore -wrl /home/atuin/wallet -createCredential hr_on_eta hr

If we now look in the wallet directory, we can see that the files have all been created with permissions for the current user only :

ls -l

total 8
-rw-------. 1 atuin atuin 573 Mar 25 17:30 cwallet.sso
-rw-------. 1 atuin atuin   0 Mar 25 17:28 cwallet.sso.lck
-rw-------. 1 atuin atuin 528 Mar 25 17:30 ewallet.p12
-rw-------. 1 atuin atuin   0 Mar 25 17:28 ewallet.p12.lck

Let’s do a quick test. From the command line, we can run :

…which should result in :

This means that, by amending our scriptenv.sh to be this :

…and our om.sh application script to this :

we can again connect to the database without exposing the password in clear text :

By default this will only work for the user who owns the wallet, due to the permissions on the wallet’s component files.
By setting the permissions to this :

$ ls -l
total 0
drwx------. 2 atuin atuin 57 Mar 25 17:49 scripts
drwxrwxrwx. 2 atuin atuin 44 Mar 25 17:14 tns_admin
drwxrwxrwx. 2 atuin atuin 90 Mar 25 17:28 wallet

$ ls -l tns_admin
total 8
-rw-rw-rw-. 1 atuin atuin 157 Mar 25 17:13 sqlnet.ora
-rw-rw-rw-. 1 atuin atuin 203 Mar 25 17:14 tnsnames.ora

$ ls -l wallet
total 8
-rw-rw-rw-. 1 atuin atuin 573 Mar 25 17:30 cwallet.sso
-rw-rw-rw-. 1 atuin atuin   0 Mar 25 17:28 cwallet.sso.lck
-rw-rw-rw-. 1 atuin atuin 528 Mar 25 17:30 ewallet.p12
-rw-rw-rw-. 1 atuin atuin   0 Mar 25 17:28 ewallet.p12.lck

… I can then use the same wallet (and tns_admin) to connect as “mike”…

I may have overdone it a bit with the chmod a+rwx there, but you get the point.
As with the OpenSSL solution, access to the database is ultimately controlled by OS file permissions.

Restricting the wallet to the original host

It’s worth noting that a wallet created in this fashion can be copied for use on another machine that does not have mkstore, or any of it’s associated jar files installed.

I was able to copy the contents of the wallet directory to my local pc, create the appropriate TNS_ADMIN config files and connect to the database using the same connect string.

If you’d rather that the wallet was used only on the machine on which it was created you can specify the auto_login_local parameter when creating the wallet, or even update the existng wallet using orapki :

Note that the create option here does not overwrite the existing wallet, but merely changes the specified property.

This time, when I copy the wallet to my local pc, I get the following when trying to connect :

$ sqlplus /@hr_on_eta

SQL*Plus: Release 12.2.0.1.0 Production on Sun Apr 3 13:22:52 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

ERROR:
ORA-12578: TNS:wallet open failed
 

I’ve read that it’s possible to circumvent this restriction by renaming the remote machine to the same name as the host server, but I have not been able to replicate this.

Keeping database passwords out of the bash history

Any mkstore commands which include the database password as an argument can be run in such a way to ensure that the password is prompted for and not included on the command line.

For example, to change a password :

There are several other options to the mkstore command that you can list by running :

Checking the contents of the wallet

If you want to list the credentials held in the wallet :

Note that for all of these commands, mkstore will prompt you for the passphrase before it coughs up any information. This means that the database password itself has an additional layer of protection.
It’s not entirely inaccessible though…

From the listCredential command, we know we have one credential stored in the wallet :

mkstore -wrl wallet -listCredential
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
List credential (index: connect_string username)
1: hr_on_eta hr
[atuin@didactylos ~]$ 

If we now use the list option, we can see the individual data items the wallet is holding :

mkstore -wrl wallet -list
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
Oracle Secret Store entries: 
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1

We can retrieve the values for each of these entries using the viewEntry option :

mkstore -wrl wallet -viewEntry oracle.security.client.username1
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
oracle.security.client.username1 = hr
mkstore -wrl wallet -viewEntry oracle.security.client.password1
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
oracle.security.client.password1 = haHayou'llneverguess1
mkstore -wrl wallet -viewEntry oracle.security.client.connect_string1
Oracle Secret Store Tool Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
Copyright (c) 2004, 2017, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:   
oracle.security.client.connect_string1 = hr_on_eta

How secure is the wallet ?

Whilst an Oracle Wallet offers an additional layer of protection against the database login credentials being read, by requiring a passphrase, that does not render it invulnerable.

If you have read permissions on the wallet’s component files, there’s nothing to stop you from copying them onto a remote machine and then cracking the passphrase there, away from the prying eyes of the server’s audit trail.

How vulnerable is the passphrase ? Well, using my “turtly” awesome cracking skills ( open browser, search “cracking Oracle Wallet passphrase”), I can see that there are multiple scary-looking options to choose from.

There are a number of other database-side precautions we might take to harden our application against unauthorised access from the server on which the scripts are stored.
However, there is a more fundamental question to consider…

Do you really need to connect via shell scripts at all ?

Of course there will be times where the answer to this question is an unequivocal “Yes!”

These are often when you’re having to work around some unfathomable (ineffable ?) restriction such as a decision to revoke execute permissions on DBMS_SCHEDULER from the application user.
Yes, I did once work at a site where this was perpetrated as standard on all Oracle databases.

Generally speaking however, I can’t think of too many things that you can accomplish with a shell script connecting to Oracle, that you can’t originate from the database itself.

Consider what a typical ETL process might look like :

1. A file arrives in a landing directory
2. The file is moved to a processing directory
3. The data in the file is loaded into a database table
4. The file is moved to an archive directory

There are several ways to implement this process in such a way that the flow is controlled from within the database and any interaction with scripts on the OS is initiated from within the database using credentials stored in a database object, the contents of which are stored more securely than the database password is using any of the options we’ve looked at here.

For example, the above workflow could be accomplished from within PL/SQL with judicious use of Oracle-supplied packages such as DBMS_SCHEDULER and UTL_FILE.

You can find a simple example of how such a process might be implemented here.

There’s an example of invoking a shell script using DBMS_SCHEDULER here.

You can even invoke a shell script using an external table preprocessor directive.

The key benefit of this approach is that SQL*Plus – and therefore database access from the OS – is taken out of the equation. Database security is handled in the database.

Conclusions

If your application uses shell script to connect to Oracle via SQL*Plus, then the most important security measures you can take are to limit access to both the scripts and the server account that owns them.
For keeping the actual password itself secure, then an Oracle Wallet is probably the best option.
OpenSSL may be a valid option if installing/using the tools to create an Oracle Wallet is problematic. Note that the reasons for that being the case are almost always non-technical !
The option of using the functionality available in PL/SQL to initiate and control interaction with the OS from within the Oracle database itself offers significant security benefits over the shell script approach.

As we’ve seen, OS Authentication is probably best avoided if your servers are running on Linux.

References

This article by Dave McKay on OpenSSL was particularly helpful.

There are a number of posts about setting up Oracle Wallet Manager but I found this one by Antoine Roger to be extremely useful.

There is an old, but still relevant, Introduction to Linux Shell Scripting for DBAs by Casimir Saternos which includes some ideas about when to employ bash scripts when interacting with an Oracle database.

If you’ve got this far, I hope you didn’t find it a turtle waste of time.