Very often I encounter customers that include Oracle account passwords in their scripts in order to connect to the database.
For DBAs, sometimes things are
easier when they run scripts locally using the oracle account, since the “connect / as sysdba” usually do the job, even with some security concerns. But what if we need other users or we need to connect remotely?
Since longtime Oracle supplies secure wallets and the proxy authentication. Let’s see what they are and how to use them.
Secure Wallet
Secure wallets are managed through the tool mkstore. They allow to store a username and password in a secure wallet accessible only by its owner. The wallet is then accessed by the Oracle Client to connect to a remote database, meaning that you DON’T HAVE to specify any username and password!
Let’s see how to implement this the quick way:
Create a directory that will contain your wallet:
|
1 |
$ mkdir .wlt |
Create the wallet, use an arbitrary complex password to protect it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ mkstore -wrl /home/ludovico/.wlt -create Oracle Secret Store Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. Enter password: Enter password again: $ ls -lia .wlt total 16 1973369 drwxrwxr-x 2 ludovico ludovico 4096 Feb 2 22:33 . 1973368 drwx------ 3 ludovico ludovico 4096 Feb 2 22:33 .. 1973377 -rw------- 1 ludovico ludovico 2901 Feb 2 22:33 cwallet.sso 1973376 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 cwallet.sso.lck 1973375 -rw------- 1 ludovico ludovico 2856 Feb 2 22:33 ewallet.p12 1973373 -rw------- 1 ludovico ludovico 0 Feb 2 22:33 ewallet.p12.lck |
Immagine that you’ve a user created with a very complex password:
|
1 2 3 |
SQL > create user batch identified by sDGVA_NNZBNsdRiW2h9mSyzbqBYJo; User created. |
Then you need to insert these credentials, including the connect string, into the wallet.
|
1 |
$ mkstore -wrl /home/ludovico/.wlt -createCredential PROD batch |
|
1 2 3 4 5 6 7 8 9 10 11 |
Oracle Secret Store Tool : Version 12.1.0.1 Copyright (c) 2004, 2012, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: Create credential oracle.security.client.connect_string1 |
Keep in mind that you can have multiple credentials in the wallet for different remote descriptors (connect strings), but if you want many credentials for the very same connect string you need to create different wallets in different directories.
Now you need to tell your Oracle Client to use that wallet by using the wallet_location parameter in your sqlnet.ora, so you need to have a private TNS_ADMIN:
|
1 2 3 4 5 6 7 8 9 10 |
$ ls -l $TNS_ADMIN total 8 -rw-r--r-- 1 ludovico ludovico 169 Feb 2 22:47 sqlnet.ora -rw-r----- 1 ludovico ludovico 1751 Feb 2 22:45 tnsnames.ora $ cat $TNS_ADMIN/sqlnet.ora WALLET_LOCATION= (SOURCE= (METHOD=file) (METHOD_DATA= (DIRECTORY=/home/ludovico/.wlt))) SQLNET.WALLET_OVERRIDE = TRUE SSL_CLIENT_AUTHENTICATION = FALSE SSL_VERSION = 0 |
If everything’s alright, you should be able to connect to the database PROD as the batch user, without specifying any username or password.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$ sqlplus /@PROD SQL*Plus: Release 12.1.0.1.0 Production on Sun Feb 2 22:50:49 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> show user USER is "BATCH" |
Attention: when mkstore modifies the wallet, only the clients with the same or above versions will be able to access the wallet, so if you access your wallet with a 11g client you shouldn’t modify the wallet with a 12c version of mkstore. This is not documented by Oracle, but you can infer it from different “not a bug” entries on MOS 🙂
Proxy Users
You can push things a little farther, and hook your wallet with a proxy user, in order to connect to arbitrary users. That’s it, a proxy user is entitled to connect to the database on behalf of other users. In this example, we’ll see how, through the batch account, we can connect as OE, SH or HR:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
SYSTEM@PROD > alter user OE grant connect through BATCH; User altered. SYSTEM@PROD > alter user HR grant connect through BATCH; User altered. SYSTEM@PROD > alter user SH grant connect through BATCH; User altered. SYSTEM@PROD > select proxy, client from dba_proxies; PROXY CLIENT ---------- -------------------- BATCH HR BATCH SH BATCH OE |
Now I can specify with which user I want to work on the DB, connect to it through the batch account, without specifying the password thanks to the wallet:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
$ sqlplus [SH]@PROD Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL > show user USER is "SH" SQL > connect [HR]@PROD Connected. SQL > show user USER is "HR" SQL> connect [OE]/@PROD Connected. SQL> show user USER is "OE" OE@PROD > select sys_context('USERENV','PROXY_USER') from dual; SYS_CONTEXT('USERENV','PROXY_USER') ---------------------------------------------------------------- BATCH |
See how it’s easy? But don’t forget to keep your wallet secure using unix/windows permissions!
Latest posts by Ludovico (see all)
- New views in Oracle Data Guard 23c - January 3, 2024
- New in Data Guard 21c and 23c: Automatic preparation of the primary - December 22, 2023
- Does FLASHBACK QUERY work across incarnations or after a Data Guard failover? - December 13, 2023
Pingback: Smart way to run commands on many *nix machines from (not only) Windows client – Change is the only constant…
I stumbled across this old thread trying to find a solution for using wallet and proxy with sqlldr… from playing around it seems it just needs an extra “/” in there before the “@”… e.g.:
sqlldr [test1]/@testdb control=sqlldr.ctl
Hope that helps somebody else who stumbles this way!
Great! Thanks for the update
Thats okk,. Thanks anyway . 🙂
Thanks for your reply, it helped me.
What about sqlldr? It also needs db password to connect and i want sqlldr to get that password from oracle wallet.
When i used “sqlldr [test1]@testdb
control = /home/infa_sgo/smarttrial_ODR_setup/sqlldr-add-new.ctl”
it asked password..But I want to handle everything through shell script. Kindly suggest how to handle this one.
Thanks in advance…
Mhhh… I have no solution for sqlldr, I didn’t try it yet with proxy users, sorry.
If i have multiple users for same database (TNS Entry) then how would sqlplus responds.?
Example:- Database is testdb and the users are test1 and test2 and are created in different wallets as tns name has to be unique per wallet. But how would ‘sqlplus /@testdb’ would respond, which user to will it connect?
How to handle this multiple user for same database scenario?
Hi Ajay, sadly, you can have just one wallet with one user/pwd definition per connection string per wallet. That’s why it is more convenient to work with proxy users when you use wallets.
Alternatives: either define different connection strings per user OR have different local TNS_ADMIN directories to work with different environments.
HTH
Great post! I often use bash scripts scheduled by cron instead using oracle jobs, wallets will be very useful for that! Thanks
Pingback: Removing passwords from Oracle scripts: Wallets and Proxy Users - Ludovico Caldara - Blogs - triBLOG