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

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.

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:47 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.SQL> connect sys/oracle@//localhost/PDB1 as sysdba
Connected.
SQL> grant create session to demo identified by MyDemoP455w0rd;
Grant succeeded.SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
mkdir -p /tmp/walletmkstore -wrl /tmp/wallet -create <<END
MyWall3tP455w0rd
MyWall3tP455w0rd
END
mkstore -wrl /tmp/wallet -createCredential PDB1_DEMO DEMO <<END
MyDemoP455w0rd
MyDemoP455w0rd
MyWall3tP455w0rd
END
echo "
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/tmp/wallet)))
SQLNET.WALLET_OVERRIDE=TRUE
" >> /tmp/wallet/sqlnet.ora
echo "
PDB1_DEMO=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
" >> /tmp/wallet/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
Copyright (c) 1982, 2018, Oracle. All rights reserved.SQL> connect /@PDB1_DEMO
Connected.
SQL> show user
USER is "DEMO"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
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
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:50 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.SQL> connect /@//localhost/PDB1
Connected.
SQL> show user
USER is "DEMO"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
PDB1_DEMO,PDB1_SCOTT=(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

19c dummy parameter

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

mkstore -wrl /tmp/wallet \
-createCredential //localhost/PDB1?MyUserTag=DEMO DEMO <<END
MyDemoP455w0rd
MyDemoP455w0rd
MyWall3tP455w0rd
END
$ tnsping //localhost/PDB1?MyUserTag=DEMOTNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 04-APR-2019 19:41:49Copyright (c) 1997, 2018, Oracle.  All rights reserved.Used parameter files:Used HOSTNAME adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=PDB1))(MyUserTag=DEMO)(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
OK (0 msec)
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 4 19:19:51 2019
Version 19.2.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.SQL> connect /@//localhost/PDB1?MyUserTag=demo
Connected.
SQL> show user
USER is "DEMO"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.2.0.0.0
$ 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.
Enter wallet password:
List credential (index: connect_string username)
3: //localhost/PDB1?MyUserTag=demo DEMO
2: //localhost/PDB1 DEMO
1: PDB1_DEMO DEMO

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member

Developer Advocate at Yugabyte, Open Source distributed SQL database. Incidentally Oracle ACE Director, Oracle Certified Master, AWS Data Hero, OakTable member