I have previously written about the use of SQLcl and using an Oracle wallet (aka secure external password store). In my previous article I used the JDBC oci driver. This is perfectly fine, but as it’s based on Oracle’s Call Interface, it requires client libraries to be present. This time I wanted to use the thin driver. A cursory search didn’t reveal any recent information about the topic so I decided to write this short post.
Update 241126: updated for Oracle Database 23ai Free.
Creating the Wallet
The wallet is created exactly the same way as described in an earlier article of mine. Scroll down to “Preparing the External Password Store” and have a look at the steps required. You probably don’t have an Oracle (instant) client installation on the sqlcl-host, so you need to prepare the wallet on a machine with one present. I’m using the create_wallet.sh shell script, which provides all the right ingredients and execute it on a Linux system with the database client installed.
Note that it still seems to be best to ensure all identifiers supplied to the script are in lower case. Even if your db_name is in upper case like mine (FREEPDB1). Here’s an example of the wallet creation:
bash-4.4$ bash create_wallet.sh $HOME/export
About to create a new secure external password store in /home/oracle/export
Enter the wallet password:
Enter the service name to connect to (in lower case, w/o db_domain): freepdb1
Enter a db_domain (leave blank if there is none):
Enter the user to connect to: martin
Enter the user's password:
Enter the hostname/scan of your RAC/single instance database: localhost
Enter the (SCAN) listener port (defaults to 1521): 1522
/usr/bin/mkdir: created directory '/home/oracle/export'
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter password:
Enter password again:
Oracle Secret Store Tool Release 23.0.0.0.0 - Production
Version 23.0.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Your secret/Password is missing in the command line
Enter your secret/Password:
Re-enter your secret/Password:
Enter wallet password:
creation of the tns directory complete, now transfer it to the application client
Once the wallet is created and tested, transfer it to the sqlcl-host in a secure manner.
If you plan to create the wallet manually without the above script, make sure not only to create sqlnet.ora, tnsnames.ora, but also ojdbc.properties!
Deploying SQLcl
SQLcl – among other things – allows you to connect to an Oracle database without a client installation. This is quite useful in many cases. I downloaded SQLcl 24.3.1 from Oracle’s website and deployed it to ~/sqlcl. My sqlcl-host’s name is oraclient and I’m logged in as martin.
Testing the Wallet
I dropped the wallet and associated configuration files created earlier into ~/tns on my sqlcl-host. There is no need to set any environment variables at all. For reference, the following files were present in the tns directory:
martin@oraclient:~$ ls -l ~/tns
total 20
-rw------- 1 martin martin 651 Nov 26 09:24 cwallet.sso
-rw------- 1 martin martin 0 Nov 26 09:24 cwallet.sso.lck
-rw------- 1 martin martin 606 Nov 26 09:24 ewallet.p12
-rw------- 1 martin martin 0 Nov 26 09:24 ewallet.p12.lck
-rw-r--r-- 1 martin martin 88 Nov 26 09:24 ojdbc.properties
-rw-r--r-- 1 martin martin 145 Nov 26 09:24 sqlnet.ora
-rw-r--r-- 1 martin martin 181 Nov 26 09:24 tnsnames.ora
With the files in place you connect to the database as follows:
martin@oraclient:~$ ./sqlcl/bin/sql /nolog
SQLcl: Release 24.3 Production on Tue Nov 26 09:27:48 2024
Copyright (c) 1982, 2024, Oracle. All rights reserved.
SQL> conn /@jdbc:oracle:thin:/@freepdb1?TNS_ADMIN=/home/martin/tns
Connected.
SQL> show con_name
CON_NAME
------------------------------
FREEPDB1
SQL> show user
USER is "MARTIN"
SQL> select * from PRODUCT_COMPONENT_VERSION;
PRODUCT VERSION VERSION_FULL STATUS
____________________________ _____________ _______________ ___________________________________
Oracle Database 23ai Free 23.0.0.0.0 23.5.0.24.07 Develop, Learn, and Run for Free
Note the leading forward slashes in the connection string, they are absolutely mandatory. The beauty of EZConnect Plus syntax is that I don’t need to specify TNS_ADMIN as an environment variable anymore. EZConnect Plus can do many more things, feel free to browse the white paper I linked to.
Have fun!
Martins Blog 