SQLcl stores connections - how and where?

Oracles SQLcl has a lot of nice features. One is to store connections (connection_string, username and  password - if you choose to). But - as normal - there is little information how and where it is stored. 

First I create a new connection in my brand new 23.2 SQLcl: 

sql -nolog


SQLcl: Release 23.2 Production on Sun Jul 16 17:53:37 2023

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

No Connection
SQL> connect berx/berx@//127.0.0.1/freepdb1
Connected.
SQL> conn -save berxfree -savepwd
Name: berxfree
Connect String: //127.0.0.1/freepdb1
User: berx
Password: ******
SQL> connmgr list
berxfree
SQL> connmgr show berxfree
Name: berxfree
Connect String: //127.0.0.1/freepdb1
User: berx
Password: ******
SQL>

I found the connection definition in a subdirectory of  ~/.dbtools/connections/

[oracle@localhost connections]$ pwd
/home/oracle/.dbtools/connections
[oracle@localhost connections]$ ls -la
total 0
drwx------. 3 oracle oinstall 22 Jul 16 17:54 .
drwx------. 3 oracle oinstall 25 Jul 15 16:34 ..
drwx------. 2 oracle oinstall 51 Jul 16 17:54 berxfree
[oracle@localhost connections]$

And in this directory I have 2 files: cwallet.sso dbtools.properties

First let's check the dbtools.properties:

#
#Sun Jul 16 17:54:23 UTC 2023
connectionString=//127.0.0.1/freepdb1
name=berxfree
type=ORACLE_DATABASE
userName=berx

also the cwallet.sso has some valuable content:

[oracle@localhost berxfree]$ mkstore -wrl . -list
Oracle Secret Store Tool Release 23.2.0.0.0 - Production
Version 23.2.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
[oracle@localhost berxfree]$ mkstore -wrl . -viewEntry oracle.security.client.connect_string1
Oracle Secret Store Tool Release 23.2.0.0.0 - Production
Version 23.2.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

oracle.security.client.connect_string1 = //127.0.0.1/freepdb1
[oracle@localhost berxfree]$ mkstore -wrl . -viewEntry oracle.security.client.password1
Oracle Secret Store Tool Release 23.2.0.0.0 - Production
Version 23.2.0.0.0
Copyright (c) 2004, 2023, Oracle and/or its affiliates. All rights reserved.

oracle.security.client.password1 = berx

It should be obvious by now the wallet is auto open - so no password is needed (and my credentials password isn't very secure). 

Btw: if you omit the -savepwd parameter, there will be still a cwallet.sso - but it's simply empty. All the other connection parameters are in dbtools.properties already. 

Of course, sometimes I have to use Windows. There is nothing like ~/.dbtools. The directory is more visible in %HOMEDRIVE%%HOMEPATH%\DBTools. This is especially valuable as I can copy the berxfree subdirectory from my linux sandbox to the Windows laptop into %HOMEDRIVE%%HOMEPATH%\DBTools\connections and just use it - simple like that.  
 
That's nearly all to share here - just in case you want to remove a stored connection, delete it's directory and all is gone!

GoldenGates credentialstore content

 Oracle GoldenGate is a software which enables logical data extract and replication among different databases. It's not limited to Oracle but also supports several other databases and even streaming platforms like Kafka. 

To extract, but also apply data from or to a database, credentials (and of course grants) are required in the targets. To avoid having credentials as clear text in some scripts, GoldenGate uses a credential store. Technically it's a well known wallet in the directory $GG_HOME/dircrd. To use these credentials, in the commandline tool ggsci commands like

GGSCI (myhost) 1> ALTER CREDENTIALSTORE ADD USER usr5 PASSWORD pwd5 ALIAS usr5@SRV5 domain dom1

Credential store altered.

and

GGSCI (myhost) 2> info credentialstore 

Reading from credential store:

Default domain: OracleGoldenGate

  Alias: ogg@dg
  Userid: ogg@dg

  Alias: c##ogg@dgpdb1
  Userid: c##ogg@dgpdb1

  Alias: c##ogg@dgc
  Userid: c##ogg@dgc


Other domains: 

  dom1

To view other domains, use INFO CREDENTIALSTORE DOMAIN <domain>

can be used.

To see what's really stored in the wallet - in case I need to extract some data from there, a well known mkstore (from my oracle binary installation) can be used: 

  oracle@myhost:/u00/app/oracle/product/gg21_ora/dircrd/ [DGC] mkstore -wrl . -list
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved.

Oracle Secret Store entries: 
dom1@#3#@16908a4d-ali1
OracleGoldenGate@#3#@55928a5d-c##ogg@dgc
OracleGoldenGate@#3#@c478f3a2-c##ogg@dgpdb1
OracleGoldenGate@#3#@fabe8b1-ogg@dg
  

And to get the content of an element:

oracle@myhost:/u00/app/oracle/product/gg21_ora/dircrd/ [DGC] mkstore -wrl . -viewEntry OracleGoldenGate@#3#@fabe8b1-ogg@dg
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2022, Oracle and/or its affiliates. All rights reserved.

OracleGoldenGate@#3#@fabe8b1-ogg@dg = {pwd_cred_type}@#4#@NEVER_EXPIRE@#5#@ogg@dg_tns_alias@#111#@ogg_pwd

The first detail to notice here: no password is required to read the wallet. This makes it easy to have a centralized wallet and distribute it to all GoldenGate instances which require these credentials. I did not find any method to enforce a wallets password in GoldenGate. 

Another detail which catches my eyes are some syntax and keywords used in the wallets entries:

OracleGoldenGate@#3#@fabe8b1-ogg@dg = {pwd_cred_type}@#4#@NEVER_EXPIRE@#5#@ogg@dg_tns_alias@#111#@ogg_pwd

At first, there are some delimiters within the text: @#3#@. The meaning of the numbers isn't documented anywhere, but they can be deduced by the syntax of the line. 

OracleGoldenGate matches the domain, ogg@dg is the alias, ogg@dg_tns_alias the username and of course ogg_pwd is the password. All relevant bits of information can be retrieved from the wallet without significant problem. 

Unfortunately, all the keywords are not defined anywhere, but at least I could find them in the binary libggnnzitp.so:

  $  strings libggnnzitp.so | grep -B 20  NEVER_EXPIRE
ldap username not set.
No key in bootstrap wallet with key name=
...for map name=
No map in bootstrap wallet with name=
ldap rootname not set.
ldap farmname not set.
ldap url not set.
bootstrap wallet entry key not set.
bootstrap wallet entry map (alias) not set.
bootstrap wallet path not set.
@#3#@
{pwd_cred_type}
@#5#@
@#111#@
nzcsfCCS_CloseCredStore
Error closing Wallet store
Error closing LDAP store
Error freeing cred store list
No Wallet specified to close cred store
@#4#@
NEVER_EXPIRE

All these bits should be a good starting point for everyone who wants to dig deeper in GoldenGates credential management.

access to CHM raw data - without manipulating the -MGMTDB

In Version 12.1 Oracle introduced the Grid Infrastructure Management Repository (GIMR) called ‑MGMTDB.
This self managed pluggable database is a required component of Grid Infrastructure and should never require direct interactions. (there can be some interactions when you want to migrate to different diskgroups, but also those activities are covered within wrapper scripts provided by Oracle).
Every interaction with the data stored in this DB is done through applications - oclumon might be the best know. (If you prefer a graphical interface, you can check chmosg instead). Just as these are interfaces for the data stored on CHM user, Oracle does not provide any information how to connect to the database directly and query the data.
Of course I can always do a bequeath connection to the CDB, do a
alter session set container = <cluster_name>;

and query the tables directly. But sometimes I prefer to do queries from my PC, using sqldeveloper. So I was searching for CHMs password.

The probably most complicated way is to identify where the password could be stored. After some internet research I was sure it would be worth to check for a wallet stored in OCR. Even the docu does not show it, crsctl query wallet can show some details about a wallet called MGMTDB.

crsctl query wallet -type MGMTDB -all
CRS-10252: Aliases present in the wallet 'MGMTDB' are:
CHM
PCMRADMIN

So I know there is a wallet and it contains 2 credentials, but crsctl does not provide their passwords - at least I did not find out how.

So I have to go the longer way:

First get the proper entry from OCR:

ocrdump -keyname SYSTEM.WALLET.MGMTDB -xml /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.xml

Then some transformation to get from

<name>SYSTEM.WALLET.MGMTDB</name>
<value_type>BYTESTREAM (16)</value_type>
<value>&lt;![CDATA[a1f84e370000000600000021.............
</value>

to a wallet which can be used by mkstore:

echo "cat //OCRDUMP/KEY/VALUE/text()" | \
xmllint --nocdata --shell /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.xml |  \
head -n -1 | tail -n -1 | \
xxd -r -p > /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin

Not that complicated at all. Now it's mkstore to query the details:

mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin -list
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle Secret Store entries:
CHM
PCMRADMIN

It's quite similar to the previous result from crsctl query wallet - so we are nearly there!

mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin \
-viewEntry CHM
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 CHM = cRlu7yvFd7gZoYmqEl2Ye6jx143Iji

mkstore -wrl /tmp/ocrdump.SYSTEM.WALLET.MGMTDB.bin \
-viewEntry PCMRADMIN
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.

 PCMRADMIN = mfqO8gPGFfzQZWPgyZhwO0pZk8zgSu

Here I have the passwords I'm looking for!

To connect to the -MGMTDB I need the listener information:

lsnrctl status MGMTLSNR

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 08-FEB-2016 10:52:00

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=MGMTLSNR)))
STATUS of the LISTENER
------------------------
Alias                     MGMTLSNR
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                24-JAN-2016 11:21:26
Uptime                    14 days 23 hr. 30 min. 33 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /some_path/grid/grid_12102/network/admin/listener.ora
Listener Log File         /some_path/logs/grid/diag/tnslsnr/av3l958t/mgmtlsnr/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=MGMTLSNR)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=1.2.3.4)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=5.6.7.8)(PORT=1521)))
Services Summary...
Service "-MGMTDBXDB" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "_mgmtdb" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
Service "crs908" has 1 instance(s).
  Instance "-MGMTDB", status READY, has 1 handler(s) for this service...
The command completed successfully

With all those bits and pieces I am able to connect to CHM@-MGMTDB:

CHM/cRlu7yvFd7gZoYmqEl2Ye6jx143Iji@1.2.3.4:1521/crs908 

It's not very practical, especially as the pasword can change for several reasons without notification, but still for any deeper data analysis it's handy to access the tables directly.