As a DBA (or developer), you often need to make connection to your Oracle database from shell scripts.

That’s not a big deal if you connect locally to the database through the Linux/Unix account that owns the instance with “/ a sysdba”. But what if you need to connect to a remote database or a local database with a specific user?

Here are the solutions I have most often seen:

• Clear password in the shell script
• Clear password in a parameter file loaded from the shell script
• Environnement variable with clear password
• Function which decrypt a encrypted password, stored in a parameter file loaded from the shell script (more secure but easily breakable)

As you might have guessed, none of these solutions is secure!

Fortunately, Oracle provides us a free feature that allows you to get rid of clear passwords from your shell scripts:Secure external password store (also known as “SEPS” or “Oracle Wallets”).

The only prerequisite is to use Oracle 10gR2 or onward.

How it works

Oracle Secure External Password Store rely on a small tool named “mkstore” which allow you to create a “wallet” (that’s why you will often hear talk of this feature as “Oracle Wallet“).

Note: you can also use “orapki” or Oracle Wallet Manager (owm), which is a GUI tool for orapki but, as mentioned by Oracle: “mkstore is more suited to store so called secret store entries like user credentials”. Anyway, both tools can be used interchangeably.

Structure

By default, a wallet is composed of 2 files:

• “ewallet.p12“: a PKCS#12 format file which store your credentials, protected by a password you choose. That’s you wallet!
• “cwallet.sso“: an additional file which allow you to use “auto login” feature. That is, you don’t need to supply the password of your “ewallet.p12” file in order to open it (but you need it to add/delete/modify credentials). In fact, that is an obfuscated copy of the wallet which is used automatically by Oracle each time you connect to your database using SEPS.

Usage

Once your environment is properly configured, you can connect to your database or rman catalog with one simple command without giving any password:

> sqlplus /@NET_SERVICE_NAME

or

> rman target / catalog /@CATALOG_NET_SERVICE_NAME

or, with datapump

> expdp /@NET_SERVICE_NAME

Oracle will automatically search for an entry in your wallet for the net service name you provide. The path of the wallet must be specified in the “sqlnet.ora” file and the net service name must be present in the “tnsnames.ora” file.

If the net service name specified in your connection string matches a net service name declared in your Oracle Wallet, the connection is established (provided that the registered username and password are correct).

 

 

Setting up

As said before, in order to create and use an Oracle Wallet, you will need to add entry in your “sqlnet.ora” and “tnsnames.ora” files. It is a common usage to use the files located in you “$ORACLE_HOME/network/admin” directory when you work directly on your database server.

Personally, I do not recommend doing this! Since SEPS is a client-side feature, it does not make sense to modify server-side configuration files to implement client-side authentication mechanisms. Instead, I prefer to create a dedicated directory on my server and set the environment variable “TNS_ADMIN” on this directory in my shell scripts.

Here are some problems I saw while editing the files directly in “$ORACLE_HOME/network/admin” or “$GRID_HOME/network/admin”:

• External libraries may not work anymore (especially “shapelib” from ESRI)
• Unable to start Oracle CRS (Doc ID 2081230.1)
• Unable to connect locally to the database with “/ as sysdba” when access file permissions on wallet directory are not properly set (or the wallet directory is deleted)

Unfortunately, if you use Oracle ZDLRA and add your protected databases via OEM, you have no choice, but that’s another topic.

So, let’s get started!

Prepare the environment

Directories

I will use 2 directories:

• “/home/oracle/tnsadmin” will hold my “tnsnames.ora” and “sqlnet.ora” files
• “/home/oracle/tnsadmin/wallet” will hold my Oracle Wallet

> mkdir -p /home/oracle/tnsadmin/wallet

sqlnet.ora file

Very important! This tells Oracle to use the wallet and the path to the wallet.

> cd /home/oracle/tnsadmin/
> cat sqlnet.ora

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION=(
  SOURCE=(METHOD=FILE)
  (METHOD_DATA=(DIRECTORY=/home/oracle/tnsadmin/wallet))
)

tnsnames.ora file

 
> cat tnsnames.ora

MYDBAWORLD =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = mydbaworld.local)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME=ORCL)
 )
)

Create the wallet

It’s time to create a strong password and remember it!

> cd /home/oracle/tnsadmin/wallet

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -create
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter password: #your strong password
Enter password again: #your strong password

> ls -lrt
total 8
-rw-rw-rw- 1 oracle oracle   0 Nov 19 11:18 ewallet.p12.lck
-rw------- 1 oracle oracle  75 Nov 19 11:18 ewallet.p12
-rw-rw-rw- 1 oracle oracle   0 Nov 19 11:18 cwallet.sso.lck
-rw------- 1 oracle oracle 120 Nov 19 11:18 cwallet.sso

Your empty wallet is created. Look at the syntax:

• “-wrl“: This argument is required and indicates the path of your wallet. It will be needed each time you want to query or modify your wallet.
• “-create“: this is the basic syntax (and the only one documented by Oracle) for creating a wallet. In fact, you have another “create” command available (but not documented): “-createALO“. If you use this one instead of “-create”, your wallet will not be protected by password and you will obtain only one file “cwallet.sso”. This solution is not secure. Some people say that if you create a wallet with this option, you can not copy it and use it on another host because Oracle will find that the host name is not the same as the one it’s on was created. That’s not true, you can try it yourself.

Add an entry to your wallet

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -createCredential MYDBAWORLD MYSCHEMA MY_SCHEMA_PASSWORD
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password: # your wallet password
Create credential oracle.security.client.connect_string1

Fast and easy!
You simply need to replace “MYDBAWORLD” with your net service name declared in the file “tnsnames.ora”, “MYSCHEMA” with the schema you want to connect to and “MY_SCHEMA_PASSWORD” with the password of your schema.

Verify your connection

Setup your environment:

> export TNS_ADMIN=/home/oracle/tnsadmin

And connect to your database using your freshly created wallet:

> sqlplus /@MYDBAWORLD

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 19 11:56:46 2018

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

Last Successful login time: Mon Nov 19 2018 11:39:19 +01:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

SQL> show user
USER is "MYSCHEMA"

As promised: no password needed!

You can ask yourself one question: what if I have to connect to the same database with 2 different users using the same wallet?

In this case, you must add a new entry in your “tnsnames.ora” with a different net service name that points to the same database and add a new entry in your wallet for that alias.

As I said before, you can use the same configuration to connect to a RMAN catalog without password prompt. The database you want to connect to does not need to be on the local system. You only need to adapt your tnsnames.ora file and your wallet according to the name of your server and your service name/sid.

Play with wallets

Now that you understand the basics of Oracle Wallets, it’s time to discover the other available commands.

View credentials stored in your wallet

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
List credential (index: connect_string username)
1: MYDBAWORLD MYSCHEMA

Update credentials stored in your wallet

If you want to update the credentials for your net service name “MYDBAWORLD”:

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -modifyCredential MYDBAWORLD MYSCHEMA MY_NEW_PASSWORD
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Modify credential
Modify 1

Delete credentials stored in your wallet

If you want to delete the credentials for your net service name “MYDBAWORLD”:

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -deleteCredential MYDBAWORLD
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
Delete credential
Delete 1

View passwords stored in your wallet

Yes, you can do that with Oracle Wallet! That’s why you need to choose a strong password for your wallet, because you can show passwords stored in it in plain text. It requires 2 steps, but that’s not complicated.

First step, list the detailed content of your wallet:

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -list
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, 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

Each credential added to your Oracle Wallet has one generated ID (1 in my example). For each credential, you will have :

• oracle.security.client.connect_string[id]
• oracle.security.client.password[id]
• oracle.security.client.username[id]

As you might guess, we will focus on “oracle.security.client.password[id]”.

> $ORACLE_HOME/bin/mkstore -wrl /home/oracle/tnsadmin/wallet -viewEntry oracle.security.client.password1
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:
oracle.security.client.password1 = MY_SCHEMA_PASSWORD

Security

Prevent other users from using the wallet

The only way to prevent other user on the same server to use your wallet is to set proper unix file permissions on your wallet directory.

Prevent users from copying and using the wallet

It is possible to prevent users from copying your wallet on another server and using it to connect to the database. This time, we will use the “orapki” utility.

> $ORACLE_HOME/bin/orapki wallet create -wallet /home/oracle/tnsadmin/wallet -auto_login_local
Oracle PKI Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

Do not be afraid of the “create” argument. It will not replace your wallet but only the file “cwallet.sso” used for automatic login. You will not lose your stored credentials.

If you try to copy the wallet on another server and connect through it to your Oracle database, you will get the following error message:

> sqlplus /@MYDBAWORLD

SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 19 15:38:09 2018

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

ERROR:
ORA-12578: TNS:wallet open failed

Actually, you can use “mkstore” with the “-createLSSO” option to achieve this, but this is not documented by Oracle.

This solution is not perfect! Because the mechanism only rely on the host name of the server on which the wallet has been created, you can copy the wallet, change the host name of the target server and use the wallet freely…

I hope this post has been useful. Stay tuned for more DBA stuff!