If you have a wallet which already contains an alias for a database, you cannot add another one pointing to the same alias. If you do, mkstore will give you the following error:
Secret Store error occurred: oracle.security.pki.OracleSecretStoreException: Credential already exists
This is actually
documented:
Each user account must have its own unique connection string; you cannot create one connection string for multiple users.
So what to do, then, if you find yourself in a situation where you need two different users to access the database in a passwordless manner using a wallet?
Workaround:
Use multiple aliases in tnsnames.ora pointing to the same database service.
List the current contents of the wallet:
mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
1: mydb1_scott scott
So user scott can log into the database passwordless using his own password which is securly stored in the wallet.
The tnsnames.ora knows which service to connect to whenever "mydb01" is called:
mydb1_scott =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb1.oric.no)
(INSTANCE_NAME = cdb)
)
)
Let's add another connect_identifier in the tnsnames.ora file:
mydb1_jim =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = server1.oric.no)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = mydb1.skead.no)
(INSTANCE_NAME = cdb)
)
)
Let's test it, using a random user, for example, system:
sqlplus system@mydb1_jim
Enter password:
SQL>show user
USER is "SYSTEM"
So the connect identifier is working, it points towards a valid service being serviced by the listener on the server.
Let's add another entry to the wallet for user jim, so that he can connect without supplying his password:
mkstore -wrl $TNS_ADMIN/wallet -createCredential mydb1_jim jim
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.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: <-- password for user jim
Re-enter your secret/Password: <-- reenter password for user jim
Enter wallet password: <-- wallet password
Jim can now login using his own password stored in the wallet:
sqlplus /@mydb1_jim
SQL>show user
USER is "JIM"
The contents of the wallet is now:
mkstore -wrl $TNS_ADMIN/wallet -listCredential
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2024, Oracle and/or its affiliates. All rights reserved.
Enter wallet password:
List credential (index: connect_string username)
2: mydb1_jim jim
1: mydb1_scott scott
