Allow /@TNS connection with Secure External Password Store

[maxsatula]

It would be nice to allow /@TNS connection format with an empty username and password and use Secure External Password Store (sometimes called Oracle Wallet) for authentication. This will also address (to some extent) #172.

[pesse]

Can you provide a working example?

[maxsatula]

Not sure what exactly you mean by that, as wallet setup is an oracle feature and is available in Oracle Documentation, but I'll try my best. Please let me know if you asked something different.

# wallet setup for java programs

$ cat ~/oracle/network/admin/ojdbc.properties
oracle.net.wallet_location=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=${HOME}/oracle/wallet)))

# wallet setup for oci programs (just for reference, I know utPLSQL is a java program, so this may be irrelevant)

$ cat ~/oracle/network/admin/sqlnet.ora
WALLET_LOCATION=
 (SOURCE=
  (METHOD=FILE)
  (METHOD_DATA=(DIRECTORY=${HOME}/oracle/wallet)))

SQLNET.WALLET_OVERRIDE=TRUE

$ export TNS_ADMIN=$HOME/oracle/network/admin

# create an entry in $HOME/oracle/network/admin/tnsadmin.ora (not shown)

# now create a wallet which config files from above refer to

$ orapki wallet create -wallet $HOME/oracle/wallet -auto_login_local
Oracle PKI Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:
Operation is successfully completed.

$  mkstore -wrl $HOME/oracle/wallet -createCredential MYDATABASE someusername
Oracle Secret Store Tool Release 21.0.0.0.0 - Production
Version 21.0.0.0.0
Copyright (c) 2004, 2023, 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:

With this config, I expect all oracle programs to work:

#SQL-CL (java application)

$ sql /@MYDATABASE?TNS_ADMIN=$HOME/oracle/network/admin

SQLcl: Release 24.2 Production on Tue Sep 17 10:16:25 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
...

# sqlplus

$ sqlplus /@MYDATABASE

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Sep 17 10:17:31 2024
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Connected to:
...

In the meantime:

❯ utplsql run /@MYDATABASE
########### utPLSQL cli ############
#                                  #
#   utPLSQL-cli 3.1.9.local        #
#   utPLSQL-java-api 3.1.9.local   #
#   Java-Version: 11.0.24          #
#   ORACLE_HOME: null              #
#   NLS_LANG: null                 #
#                                  #
#   Thanks for testing!            #
#                                  #
####################################

java.lang.IllegalArgumentException: Not a valid connectString: '/@MYDATABASE'
        at org.utplsql.cli.ConnectionConfig.<init>(ConnectionConfig.java:19)
        at org.utplsql.cli.DataSourceProvider.getDataSource(DataSourceProvider.java:28)
        at org.utplsql.cli.RunAction.doRun(RunAction.java:68)
        at org.utplsql.cli.RunAction.run(RunAction.java:122)
        at org.utplsql.cli.RunPicocliCommand.run(RunPicocliCommand.java:265)
        at org.utplsql.cli.Cli.runPicocliWithExitCode(Cli.java:47)
        at org.utplsql.cli.Cli.main(Cli.java:17)

While expected behavior is to connect successfully. Empty username/password should not be a syntax error, it is a way to connect with a wallet (a proper name for it is Secure External Password Store, but many people call it a "wallet" for brevity) https://www.oracle.com/technetwork/database/security/twp-db-security-secure-ext-pwd-stor-133399.pdf?ssSourceSiteId=otncn

[pesse]

Thanks. TNS_ADMIN is something I only have seen working with the thick client (OCI). But that's a good hint that it might also work with ojdbc. If sql-cl supports it I might find how it's implemented.

(please note that utPLSQL is not an official Oracle program but a leisure time side-project of some nerds with too much time :P )

[maxsatula]

(please note that utPLSQL is not an official Oracle program but a leisure time side-project of some nerds with too much time :P )

Sure, I'm aware of that, thank you for your efforts! If my language in previous posts sounded like some demanding tone as if you were a paid support, my apologies.

Regarding ojdbc connection, I believe (to be verified) that all you have to do is relax regexp in src/main/java/org/utplsql/cli/ConnectionConfig.java a little bit, and that's it.

As a first step, to allow empty username and password. Allowing ?TNS_ADMIN=... is questionable, maybe setting env variable will be enough, maybe not.

If I have a chance (no guarantee) to install the necessary build tools (like proper java version, maven etc), I'll give it a try.

[maxsatula]

Oh man, it's too tough to setup the build environment for me, I don't want to spend hours just to make a minor change (I'm not a java developer, so I've got nothing installed initially). So, I went ahead and edited regexp directly in the binary .class file. It worked for me, woo-hoo! Replaced a + with * in two places. Was: ^(".+"|[^/]+)/(".+"|[^@]+)@(.*)$ Now: ^(".+"|[^/]*)/(".+"|[^@]*)@(.*)$

@pesse if you are ok with that, I can create a pull request. However, I know it is not very clean, in a perfect world both username and password may be empty, or both non-empty, but not independently. Also, when both of them are empty, I believe a slash can be omitted too. But I cannot change the length of regexp while editing a binary file to test all of that, sorry ;)

[pesse]

you didn't sound demanding, no worries, all fine ;)

Thanks for investigating. I'm just about to return to the project after a long break of nearly 2 years, so give me a bit of time. No need to create a pull request, I'll resolve the issue, you already did all the work :D