Stop exposing your passwords in your database connection strings!

This blog topic was inspired by Connor McDonald’s excellent YouTube video ‘Rock solid secure database connections’, in which he discusses 3 methods to stop exposing your passwords in your database connection strings:

In the spirit of continuing the conversation, I’ll contribute my $0.02 to his 3 methods. Also, I’ll contribute 2 more to the consideration set. I presented on this subject on Nov 8th, 2018 at the New England Oracle User Group Collaborative at the Gillette Stadium.

The Problem

As shown in the screenshot above – using a clear-text password in our database connection string puts us at risk, at the very least, of inadvertently sharing our sensitive data with users on the same server.

Note:  This problem manifests itself differently in different operating systems. Most Unixes, Solaris, and Windows have this problem by default. The above screenshot is actually from a Linux environment, which does not generally manifest this issue. However, because the oracle user is using the rlwrap utility (which allows command scrolling and searching of command history with Ctrl-R) we end up being able to see the password nonetheless. In short, if you are using clear-text passwords you should be wary of this issue, regardless of what operating system you are using.

Five Options to stop exposing your passwords

These 5 options are /NOLOG, Piping environment variables, Oracle Wallet, Oracle Wallet – auto_login_local, and Oracle Enterprise Manager.

/NOLOG

This dead-simple but effective choice, ‘nolog’ (short for ‘no login’) allows you to open SQL*Plus without connecting to a database. Once in SQL*Plus, the connection is made to a particular database and a session is established if the correct username and password are given.

[oracle@c2test ~]$ sqlplus /nolog @myscript.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Nov 7 21:37:35 2018

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

@ > connect scott/tiger

However, even if it solves the very specific issue of protecting your password from nosy users (see the previous screenshot), it leaves something to be desired. Specifically, it maintains the practice of typing a clear-text password. It means, at a minimum, the user needs to know the password for the database credential with which they are connecting.

Piping Environment Variables

You can protect your password from spying users on the same server by passing the password into an environment variable and piping it into your command.

[oracle@test ~]$ echo $PASSWORD | sqlplus scott @myscript.sql

Again, this method continues to require that, at a minimum, the oracle user have access to the clear-text password.

Oracle Wallet

This is a small notch-up in difficulty but has the clear advantage of removing the necessity of having any clear-text passwords. A quick overview of the setup required:

1: create and populate wallet

[oracle@test]$ mkstore -wrl mywallet/ -create

[oracle@test]$ mkstore -wrl mywallet/ -createCredential SECURESCOTT scott tiger

2: edit/create sqlnet.ora

Add a sqlnet.ora file (if not already there), in the path $ORACLE_HOME/network/admin:

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(
  SOURCE=(METHOD=FILE)
  (METHOD_DATA=(DIRECTORY=[path to your wallet]))
)

3: add an entry to tnsnames.ora

Add an entry to match the name of the credential in your wallet. Something like:

SECURESCOTT =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = orclpdb.localdomain)     )   )

Done

Now you can elegantly connect to your database with the following succinct command:

[oracle@test ~]$ sqlplus /@SECURESCOTT

However, the improvement in security is nuanced:

Oracle Wallet – auto_login_local

A solution that specifically tackles my concerns with the previous wallet is simply to rebuild the wallet with a different command; this time using the orapki utility:

[oracle@test]$ orapki wallet create -wallet mywallet/ -auto_login_local

The “auto_login_local” option makes the wallet exclusively useable to the user who created it (‘oracle’ in this case) and in the location in which it was created. I, therefore, cannot copy this wallet to my mac for local use. For more details, visit the official documentation.

Oracle Enterprise Manager

This final solution may not be as universally applicable as the ones so far but, where applicable, it is the best solution. Put simply, the Oracle Enterprise Manager (OEM) offers a free platform through which you can manage a collection of servers. Cron jobs are obvious candidates for conversion to OEM. Jobs in OEM do not need clear-text passwords but instead, use shareable ‘named credentials’.

I’ve also written 2 supporting blog posts. These can help you assess if this solution is right for you. I hope you find them useful.

1. How to Install Enterprise Manager Cloud Control
2. How to Convert Crontab to Oracle Enterprise Manager

Put simply, if you have (1) several databases to manage and (2) cron jobs that contain hard-coded passwords, it is well worth the 3-4 hours and $0 required to install OEM.

Sign-off

Which of these options do you think is most overlooked? Have I missed or misrepresented anything? Do you think these are effective methods to stop exposing your passwords? Let me know in the comments below!

Have other concerns about security? Contact us!