Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Michael.

Asked: November 28, 2019 - 4:45 pm UTC

Last updated: July 15, 2021 - 3:54 am UTC

Version: 10.2

Viewed 10K+ times! This question is

You Asked

Dear AskTom,

I have a shell script that connects as several different users to the same database.

From Oracle:
You can store multiple credentials for multiple databases in one client wallet. You cannot store multiple credentials (for logging in to multiple schemas) for the same database in the same wallet. If you have multiple login credentials for the same database, then they must be stored in separate wallets.

If I understand correctly, I want multiple wallets owned by a single unix user because the shell script is run by just one user.

How do I setup sqlnet.ora (I think each wallet has it's own folder) and how do I tell sqlplus which wallet to use at the command line? That is, how do I specify the user I want to connect as so the wallet with that users credentials is chosen?

and Connor said...

You have a couple of choices here.

1) Use multiple tnsnames.ora entries. For example, you could do:

mkstore -wrl c:\oracle\wallet -createCredential MYDB scott tiger
mkstore -wrl c:\oracle\wallet -createCredential MYDB1 joe smith
mkstore -wrl c:\oracle\wallet -createCredential MYDB2 susy jones
mkstore -wrl c:\oracle\wallet -createCredential MYDB3 jane doe

and then have a tns entry for mydb, mydb1, ... all pointing to the same database

MYDB = (DESCRIPTION = ...HOST = localhost)(PORT = 1521))...(SERVICE_NAME = thedb)
MYDB1 = (DESCRIPTION = ...HOST = localhost)(PORT = 1521))...(SERVICE_NAME = thedb)
MYDB2 = (DESCRIPTION = ...HOST = localhost)(PORT = 1521))...(SERVICE_NAME = thedb)
MYDB3 = (DESCRIPTION = ...HOST = localhost)(PORT = 1521))...(SERVICE_NAME = thedb)

2) Use multiple wallets. What this means is that each OS user than needs their own sqlnet.ora file. You can control this by setting TNS_ADMIN in their environment to control where those users look for the sqlnet.ora/tnsnames.ora file.

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Multi-Schema Oracle Wallet

Michael Stong, November 29, 2019 - 7:18 am UTC

I figured out the multiple tnsnames entries approach on my own and was very happy to see it as part of your answer.

Thanks very much,

mjs

Proxy authentication

Mikhail Velikikh, December 02, 2019 - 5:13 pm UTC

Another option that I use on an every day basis is Proxy Authentication.
Provided your TNS alias is MYDB and different usernames are Scott, Joe, and Susy, we could:

1. authorize those users to connect through scott (let's make him a connection user):
alter user joe grant connect through scott;
alter user susy grant connect through scott;


2. create one entry for scott:

mkstore -wrl c:\oracle\wallet -createCredential MYDB scott tiger

3. connect using that entry utilizing proxy authentication:

connect [joe]/@mydb
connect [susy]/@mydb

Auditing can be set up for those proxy connections, so that usual connections as Joe and Susy would not be audited, but when Proxy Authentication is utilized auditing kicks in. It may come in handy when those Joe and Susy some application users of a high-load/chatty application.
Chris Saxon
December 05, 2019 - 2:07 pm UTC

Nice suggestion.

Proxy Authentication

Michael Stong, December 04, 2019 - 12:19 am UTC

Thanks very much for the proxy authentication advice. I'll look into it :-)

On Windows 10 Oracle 19c Client

Graham, April 24, 2021 - 6:13 pm UTC

I'm on Windows 10 with Oracle 19c Client software installed and can't get 1. to work.

I need to store credentials for multiple users on multiple databases (a large Oracle conversion project with a load of stuff going on at once).

I started out simple: store two sets of credentials for two different databases db1 and db2 (which if I'm understanding the post is a situation that should just work, and what 1. seems to seek to emulate to get around the ops problem).

What I get is 

mkstore -wrl <path_to_wallet> -createCredential db1 user1
[password stuff] 
...
sqlplus /nolog
SQL> connect /@db1
connected


... all good so far...

mkstore -wrl <path_to_wallet> -createCredential db2 user2
[password stuff] 
...
sqlplus /nolog
SQL> connect /@db2
ORA-12578: TNS:wallet open failed
SQL> connect /@db1
ORA-12578: TNS:wallet open failed


... wallet "broken" by second set of credentials...

Then 
mkstore -wrl <path_to_wallet> -deleteCredential db1
...
sqlplus /nolog
SQL> connect /@db2
connected


... wallet works again with first set of credentials deleted, showing second set of credentials are fine and I think also that I am not doing anything silly anywhere? (Paranoia had me do this several times with both db1 user1 and db2 user2 as the starting point but the problem persisted.)

Glad if you or others could verify the problem?

If 1. is really not possible, we are left with approach 2 which seems restrictive, as I expect (but certainly don't know yet) we will find most tools only read TNS_ADMIN at start-up, meaning connecting to multiple dbs as multiple users using wallet credentials during a single session won't be possible.
Connor McDonald
April 27, 2021 - 6:23 am UTC

You might need to log a call with Support on that one. I can't reproduce

Microsoft Windows [Version 10.0.19042.928]
(c) Microsoft Corporation. All rights reserved.

C:\>mkstore -create -wrl c:\oracle\tmp
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Enter password:
Enter password again:

C:\>mkstore -wrl c:\oracle\tmp -createCredential MYDB scott tiger
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

C:\>mkstore -wrl c:\oracle\tmp -createCredential MYDB2 connor connor
Oracle Secret Store Tool Release 19.0.0.0.0 - Production
Version 19.4.0.0.0
Copyright (c) 2004, 2020, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:

C:\>set TNS_ADMIN=c:\oracle\tmp

C:\>sqlplus /@mydb

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 27 14:20:36 2021
Version 19.10.0.0.0

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

Last Successful login time: Thu Apr 01 2021 14:40:42 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show user
USER is "SCOTT"
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

C:\>sqlplus /@mydb2

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Apr 27 14:20:44 2021
Version 19.10.0.0.0

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

Last Successful login time: Tue Apr 27 2021 14:03:38 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0

SQL> show user
USER is "CONNOR"


My sqlnet.ora and tnsnames below

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=C:\oracle\tmp)))


and

MYDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1519))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

MYDB2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1519))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )




A reader, July 14, 2021 - 1:17 pm UTC

There's another way also: Most of the SQLNET.ORA wallet parameters can be in your TNSNAMES entry too.

This article goes into more detail. (I'm the poster there too, somehow all my threads don't link together, even over at support.oracle.com)

https://community.oracle.com/tech/apps-infra/discussion/4301296/one-client-needing-multiple-wallets-and-or-multiple-sqlnet-ora-files-solved-workaround

Shorter version (copy pasted) below:

So, what you might have had in your SQLNET.ORA might be...

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = C:/Oracle/Wallet)
)
)
SSL_VERSION = 1.2
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_GCM_SHA384)


The workaround/alternative when you need more than one Wallet and/or SQLNET.ORA, is that you can (when possible) NOT put the commands in your SQLNET.ORA file (so it would be largely empty), but instead put them in your TNSNAMES.ORA file as "dynamic parameters"::

NameOfTNSEntry =
(DESCRIPTION=
(ADDRESS_LIST =
(ADDRESS=(PROTOCOL=tcps)(HOST=xyz.somewhere.c0m)(PORT=12345678))
)
(CONNECT_DATA=(SERVICE_NAME=NameOfService))
(SECURITY =
(MY_WALLET_DIRECTORY = C:\Oracle_Wallets\Specific_Wallet_Folder)
(SSL_VERSION = 1.2)
(SSL_CLIENT_AUTHENTICATION = FALSE)
)
)


In my particular case I also needed the SSL_RSA_WITH_AES_GCM_SHA384 setting for SSL_CIPHER_SUITES, but that specific value wasn't available (per the documentation) as a dynamic parameter, so in my case, I had to keep a one-line SQLNET.ORA containing just that line. (that setting can include multiple comma separated SSL_CIPHER_SUITE values, btw.) Your situation/needs may vary.
Connor McDonald
July 15, 2021 - 3:54 am UTC

Thanks for this, although my testing suggests this is fine for encryption wallets but not for credential wallets.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database