Securing Oracle scripts with a wallet

It’s that time of year when I try to clean up some of my bad habits from the year before. This time, it’s plain-text passwords in SQL scripts. Here’s a way to make them a little more secure. You can move all your passwords into a secure wallet file stored on your PC. This is not the most secure option available (and you should keep the wallet in a private location) but it is better than nothing. There is a more secure option which ties the wallet to your machine and username, but this complicates matters if you want to use the same wallet from different machines.

You will need to know the location of your SQLNET.ORA and TNSNAMES.ORA files which are used by SQL*Plus (usually defined by the TNS_ADMIN environment variable).

Create a folder to hold your wallet

mkdir D:\SQL\auth

Add the following into your SQLNET.ORA file to point to the Oracle Wallet

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
  (DIRECTORY = D:\SQL\auth)
  )
)

SQLNET.WALLET_OVERRIDE = TRUE

Create a wallet – you will be prompted for a password to secure the wallet

orapki wallet create -wallet D:\SQL\auth -auto_login

Add your database credentials to the wallet, using an alias from your TNSNAMES file. You will be prompted for your database password, which is stored securely in the wallet.

mkstore -wrl D:\SQL\auth -createCredential TNS_ALIAS USERNAME

Now, when using SQL*Plus, you can connect using the wallet without having a password in your scripts

sqlplus /@TNS_ALIAS
connect /@TNS_ALIAS

If you need to connect via a proxy

sqlplus [PROXY]/@TNS_ALIAS
connect [PROXY]/@TNS_ALIAS

To list the contents of the wallet

mkstore -wrl D:\SQL\auth -listCredential

To modify the contents of the wallet, e.g. if your password is changed

mkstore -wrl D:\SQL\auth -modifyCredential TNS_ALIAS USERNAME PASSWORD

To delete an entry from the wallet

mkstore -wrl D:\SQL\auth -deleteCredential TNS_ALIAS

More reading

https://docs.oracle.com/database/121/DBSEG/authentication.htm#DBSEG99815

https://blog.pythian.com/securing-oracle-monitoring-backup-scripts/

https://blog.pythian.com/oracle-secure-external-password-stores/

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s