Skip to Main Content

Breadcrumb

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.

Question and Answer

Connor McDonald

Thanks for the question, Michael.

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

Answered by: Connor McDonald - Last updated: December 05, 2019 - 2:07 pm UTC

Category: SQL Plus - Version: 10.2

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Pending statistics and partition queries

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 we 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.

and you rated our response

  (3 ratings)

Reviews

Multi-Schema Oracle Wallet

November 29, 2019 - 7:18 am UTC

Reviewer: Michael Stong from Denver, Colorado

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

December 02, 2019 - 5:13 pm UTC

Reviewer: Mikhail Velikikh from Dublin, Ireland

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

Followup  

December 05, 2019 - 2:07 pm UTC

Nice suggestion.

Proxy Authentication

December 04, 2019 - 12:19 am UTC

Reviewer: Michael Stong from United States

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

More to Explore

Administration

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