Oracle DB Secure External Password Store Using Oracle Wallet

Configuring Oracle Wallet for secure DB connection without password

I often encounter scenarios where I need to write a script against a database for which I do not have access to the DB host – in this case I do not want to store the password in plain text in a script file and OS authentication is not an option since I do not have access to the DB host server, so I have configured the Oracle Wallet for Secure DB connection to overcome this challenge and figured I’d share the steps below.

Note:  OS authentication is the preferred method for securely connecting to the DB without passing in a username/password – this option is useful in instances where OS authentication is not available (i.e., from a remote host not on the local DB host).

Pre-requisites:

Oracle client must be installed

ORACLE_HOME must be set to a location with JDK6 installed

Implementation:

cd $ORACLE_HOME/network/admin

edit sqlnet.ora and add following:

WALLET_LOCATION = (SOURCE = (METHOD = FILE) (METHOD_DATA = (DIRECTORY = /u01/app/oracle/instantclient_11_2/network/admin) ) ) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE

Create the wallet (note you will need to have JDK installed and in your PATH)

mkstore -wrl "/u01/app/oracle/instantclient_11_2/network/admin" -create

Edit tnsnames.ora and add an entry specific to the DB and your user (i.e., CD00_RUFFS for a connection to the CD00 DB as the user RUFFS)

ORCL_RUFF = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = host1.domain)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = host2.domain)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = host3.domain)(PORT = 1521)) (ADDRESS = (PROTOCOL = TCP)(HOST = host4.domain)(PORT = 1521)) (LOAD_BALANCE = yes) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ORCL.WORLD) (FAILOVER_MODE = (TYPE = SELECT) (METHOD = BASIC) (RETRIES = 180) (DELAY = 5) ) ) )

Manage Password Store Credentials:

Add new DB Alias, user and password to the wallet (need to \ escape special chars)

mkstore -wrl "/u01/app/oracle/instantclient_11_2/network/admin" -createCredential ORCL_RUFF ruffs myPassword123

Modifyexisting entry (need to \ escape special chars)

mkstore -wrl "/u01/app/oracle/instantclient_11_2/network/admin" -modifyCredential ORCL_RUFF ruffs myPassword123

Delete entry

mkstore -wrl "/u01/app/oracle/instantclient_11_2/network/admin" -deleteCredential ORCL_RUFF

Test the connecting to the DB using your tns entry and wallet user/password

sqlplus /@ORCL_RUFF