Removing passwords from Oracle scripts: Wallets and Proxy Users


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:

Create the wallet, use an arbitrary complex password to protect it:

Immagine that you’ve a user created with a very complex password:

Then you need to insert these credentials, including the connect string, into the wallet.

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:

If everything’s alright, you should be able to connect to the database PROD as the batch user, without specifying any username or password.

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:

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:

 

See how it’s easy? But don’t forget to keep your wallet secure using unix/windows permissions!

The following two tabs change content below.

Ludovico

Principal Product Manager at Oracle
Ludovico is a member of the Oracle Database High Availability (HA), Scalability & Maximum Availability Architecture (MAA) Product Management team in Oracle. He focuses on Oracle Data Guard, Flashback technologies, and Cloud MAA.

10 thoughts on “Removing passwords from Oracle scripts: Wallets and Proxy Users

  1. Pingback: Smart way to run commands on many *nix machines from (not only) Windows client – Change is the only constant…

  2. 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!

  3. 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…

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

  5. Great post! I often use bash scripts scheduled by cron instead using oracle jobs, wallets will be very useful for that! Thanks

  6. Pingback: Removing passwords from Oracle scripts: Wallets and Proxy Users - Ludovico Caldara - Blogs - triBLOG

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.