Oracle Wallets and You … No, Not You, Behind You. Yeah, You.

Oracle databases are a complex system of interconnected parts. The toolset is comprehensive and robust. And so is the documentation. Possibly too robust, even. I have spent the last couple of days researching how to do One Specific Thing, while the documentation has delighted in telling me All the Things That Can Be Done. And so, having finally extracted the bits I needed, I would like to share with you, on the off chance you have the exact same problem I did. (Or one very, very similar to it, but just different enough from all the other things your Google search has turned up and that you’re considering disavowing all technology and retiring to a far-off monastery.)

The Problem

There’s a script I need to run automatically. It’s a SQL script that I will use to query some data on my Oracle 11.2.0 server. My TNSNames.ora file already contains the correct connection data so that I can refer to this database using its TNS name. The SQLPlus utility is installed and ready to run on my client machine. I have a user in the database that has all the correct permissions to execute this script. However, I want to set up my cron job such that the password is not stored in plain text.

The Technology

If you have the Oracle client suite installed, then you have Oracle Wallet Manager (and some associated command line tools) installed, which is a way to make this work. (There are other solutions, too, so it’s not the way, just a way.) We’ll use an Oracle Wallet to store the credentials for our user so that every connection made to the database automatically connects as that user (unless otherwise specified, of course).

Creating the Wallet

Launch Oracle Wallet Manager on the computer that will be executing the script. (This is important, for reasons we’ll get in to in a moment.) Create a new standard wallet. Choose a password. Remember it, as it will be used from now on to make changes to this wallet.

Now, save your wallet and note the file location. Close Wallet Manager, and let’s not speak of it again. Launch a command prompt, cd your way to the wallet (it will make the next steps much easier), and let’s add the user’s database credentials to the wallet. This user is “username”, his password is “password” and the database has a TNS name of “DB_TNS_NAME”.

mkstore -wrl . -createCredential DB_TNS_NAME username password

The flag -wrl specifies the location of the wallet. That’s why you cd’ed all the way here, so you can just use that fancy “.”.

You will be prompted for the wallet password. You do remember the wallet password, don’t you? Good. To keep from having to do that every time you use the wallet, let’s set the wallet to automatically login, but only on the local server (to prevent malicious copying of the wallet to another server and doing awful things to your precious database – this is the important reason I mentioned earlier).

orapki wallet create -wallet . -auto_login_local

One last thing. As with all things Oracle, if you don’t add some code to a file somewhere, it’s like you never did anything at all. Point your favorite text editor to the sqlnet.ora file (probably somewhere like C:\oracle\product\11.2.0\client_1\network\admin). Get the absolute path to your wallet (here, we’ll use C:\oracle\product\11.2.0\client_1\BIN\owm\wallets\MyWallet) and add these lines to sqlnet.ora.

WALLET_LOCATION = ( SOURCE = ( METHOD = FILE )   ( METHOD_DATA =   ( DIRECTORY = C:\oracle\product\11.2.0\client_1\BIN\owm\wallets\MyWallet ) ) )   SQLNET.WALLET_OVERRIDE = TRUE

This tells your Oracle client that you’re using a wallet and where it can find it. Make sure to get all of those parentheses; they’re important.

Use It

Now, everything should be configured, and you should be able to connect to your database without specifying the username and password.

sqlplus /@DB_TNS_NAME

This should start a SQL session connected to the database as username. For the love of all that is good and holy, make sure you use the right “/”. Forward slash? Back slash? I don’t know which is which, but if it’s not the right one (the one I typed right there), you’re going to spend two extra hours trying to figure out how to debug Oracle connections before you finally try the obvious thing and switch the slash and then you’re going to want to punch something. So don’t do that.

Now you can use that clean, legal-department-approved sqlplus command in your cron job, your Jenkins build, or anywhere else command line utilities can be run.

You Might Also Like

• How to Run Protractor End to End Tests in Microsoft Edge (Chromium)
• A Minor Shift in Mindset that Improved My SQL
• Why Consider a Data Warehouse?