I started to store user credentials in wallet files for higher security in SQL scripts. If you do so you should also secure the access to your wallet files on the operating system level. This is especially true for auto-login wallets from the Oracle Cloud. To increase the security a bit further you can restrict the auto-login to the local user who created the wallet. To achieve this you need to create an own wallet with
orapki instead of
orapki wallet create -wallet "path/to/my/wallet" -pwd myPassword -auto_login_local
You can then add new alias/user/password entries with mkstore (you need to add each alias to your tnsnames.ora):
mkstore -wrl "path/to/my/wallet" -createCredential myAlias myUser myPassword
If you use only the instant client and cannot find mkstore and orapki, then have a look at my previous post on how to simulate these tools.
I assume you have set an environment variable called TNS_ADMIN pointing to your TNS directory. In this directory, you have your wallet files and also your existing tnsnames.ora and sqlnet.ora to successfully connect to your local Oracle instances.
Now you need to extract the downloaded cloud wallet - for security reasons it makes sense to create a temporary subdirectory in your TNS directory and extract the cloud wallet there. You need to migrate the entries from
sqlnet.ora into your own TNS files and align the path to your wallet directory. Additionally, you should also copy over the file
ojdbc.properties if you do not have it already and you plan to use SQLcl or SQL Developer with your wallet.
My resulting sqlnet.ora:
WALLET_LOCATION = (SOURCE = (METHOD = file) (METHOD_DATA = (DIRECTORY = "C:\\path\\to\\my\\wallet"))) SSL_SERVER_DN_MATCH = yes sqlnet.wallet_override = true
My resulting tnsnames.ora:
myCloudDbName_high, myAlias = (description = (... myCloudDbName_medium = (description = (... myCloudDbName_low = (description = (... myCloudDbName_tp = (description = (... myCloudDbName_tpurgent = (description = (... myLocalDbName, myOtherAlias = (description = (... ...
That was the easy part, but how you migrate the needed private key and all certificates?
It took me some time to figure this out because most people seem to use the cloud wallet as it is. This is understandable since SQL Developer can direct use the zipped version of the wallet. But my target is not to manage the passwords in multiple places - the databases, multiple wallets, SQL Developer and possibly other tools. I want to manage the passwords only in the databases and in one wallet and use this wallet with all the tools - in my case SQL*Plus, SQLcl and SQL Developer.
First I tried to export the private key and all certificates from the cloud wallet and then import it into my wallet. I was not successful with this mostly because of the private key and unknown alias names - maybe I was too tired from all the try and error things on how to achieve this. I came finally up with the idea to use the cloud wallet Java KeyStore files and convert them into my wallet with the orapki method
Migrate private key and certificate from cloud wallet:
orapki wallet jks_to_pkcs12 -wallet "path/to/my/wallet" -pwd myPassword -keystore "path/to/cloud/wallet/keystore.jks" -jkspwd myCloudWalletPassword
Migrate trusted certificates from cloud wallet:
orapki wallet jks_to_pkcs12 -wallet "path/to/my/wallet" -pwd myPassword -keystore "path/to/cloud/wallet/truststore.jks" -jkspwd myCloudWalletPassword
Finally, list the wallet content to see the migrated entries:
orapki wallet display -wallet . -pwd myPassword or orapki wallet display -wallet . -pwd myPassword -complete
The final file list in my TNS directory (without the Java KeyStore files from the cloud wallet):
cwallet.sso cwallet.sso.lck ewallet.p12 ewallet.p12.lck ojdbc.properties sqlnet.ora tnsnames.ora
I am now able to connect to my cloud database with the desired tools without providing user/password details (tested version):
- SQL*Plus (19.6):
- SQLcl (20.2):
- SQL Developer (20.2): authentication type “OS”, connection type “Custom JDBC” with URL
If you forgot which aliases you have configured in your wallet:
mkstore -wrl "path/to/my/wallet" -listCredential
Hope this helps someone else.
Happy developing and scripting