19c EZCONNECT and Wallet (Easy Connect and External Password File)

Franck Pachot
Apr 4, 2019 · 3 min read

I like EZCONNECT because it is simple when we know the host:port, and I like External Password Files because I hate to see passwords in clear text. But the combination of the two was not easy before 19c.

Image for post
Image for post

Of course, you can add a wallet entry for an EZCONNECT connection string, like ‘//localhost/PDB1’ but in the wallet, you need a different connection string for each user because it associates a user and password to a service name. And you have multiple users connecting to a service.

Here is an example. I have a user DEMO with password MyDemoP455w0rd:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:47 2019
Version 19.2.0.0.0

I create a wallet:

mkdir -p /tmp/wallet

I add an entry for service name PDB1_DEMO connecting to PDB1 with user DEMO:

mkstore -wrl /tmp/wallet -createCredential PDB1_DEMO DEMO <<END
MyDemoP455w0rd
MyDemoP455w0rd
MyWall3tP455w0rd
END

I define sqlnet.ora to use it and tnsname.ora for this PDB1_DEMO entry:

echo "
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/tmp/wallet)))
SQLNET.WALLET_OVERRIDE=TRUE
" >> /tmp/wallet/sqlnet.ora

I can connect passwordless when running sqlplus with TNS_ADMIN=/tmp/wallet where I have the sqlnet.ora and tnsnames.ora:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:49 2019
Version 19.2.0.0.0

Eazy Connect

I add a new entry for the EZCONNECT string:

mkstore -wrl /tmp/wallet -createCredential //localhost/PDB1 DEMO <<END
MyDemoP455w0rd
MyDemoP455w0rd
MyWall3tP455w0rd
END

I can connect with it:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:50 2019
Version 19.2.0.0.0

But what do you do when you need to connect with different users? With a tnsnames.ora you can have multiple entries for each one, like:

PDB1_DEMO,PDB1_SCOTT=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

and then define a credential for each one. But that is not possible with EZCONNECT. Or you have to define a different server for each user — which may not be a bad idea by the way.

19c dummy parameter

Oracle 19c extends the EZCONNECT syntax as I described recently in:

With this syntax, I can add parameters. And then, why not some dummy parameters to differentiate multiple entries connecting to the same database but with different users? Here is an example:

mkstore -wrl /tmp/wallet \
-createCredential //localhost/PDB1?MyUserTag=DEMO DEMO <<END
MyDemoP455w0rd
MyDemoP455w0rd
MyWall3tP455w0rd
END

This just adds a parameter that will be ignored, but helps me to differentiate multiple entries:

$ tnsping //localhost/PDB1?MyUserTag=DEMO

Here is my connection to DEMO using the credentials in the wallet:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:51 2019
Version 19.2.0.0.0

I need an sqlnet.ora and a wallet, but no tnsnames.ora

Here are all the entries that I can use:

$ mkstore -wrl /tmp/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
Copyright (c) 2004, 2018, Oracle and/or its affiliates. All rights reserved.

I do not use it for applications. The host name is not a problem as I can have a DNS alias for each application, but I don’t want the listener port hardcoded there. Better a centralized tnsnames. ora or LDAP.

However, for the administration scripts like RMAN backups or duplicates, or Data Guard broker, a simple passwordless EZCONNECT is easier.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store