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
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