Using Oracle Wallet to Execute Shell script/cron without hard coded Oracle database password

You have been asked to schedule a shell script which need to connect to a  particular user and perform some action? How do you pass the password to script without hardcoding it in script. If password is written in a script, isn’t it a security threat?

Well with 10gR2 , Oracle Wallet provides you with facility to store database credentials in client side Oracle Wallet. Once stored, you can connect to database using sqlplus /@connect_string

Let’s see how it works.

Create a Oracle Wallet
Syntax – mkstore -wrl -create

$mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet
Enter wallet password:

Two files are created.

$ls -ltr
total 8
-rw------- 1 oracle oinstall 3880 Sep  8 22:48 ewallet.p12
-rw------- 1 oracle oinstall 3957 Sep  8 22:48 cwallet.sso

If you schedule cron through oracle user, keep the privileges as such. Please note that if a user has a read permission on these files, it can login to database.So it’s like your House Key which you would like to keep safely with you 🙂

Next step is to add database credential to the wallet. Before this, create a tnsnames entry you will use to access the database

    (ADDRESS = (PROTOCOL = TCP)(HOST = db11g)(PORT = 1521))
      (SERVICE_NAME = test11r2)

Add user credential to Oracle Wallet. Syntax is

mkstore -wrl wallet_location -createCredential db_connect_string username password</span>
  $mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet
-createCredential amit_test11r2 amit amit
  Enter wallet password:

To confirm, if the credential has been added , use listCredential option

  $mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/owm/wallets/oracle
Oracle Secret Store Tool : Version - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:             

List credential (index: connect_string username)
1: amit_test11r2 amit

Now add following entries in client sqlnet.ora file

   (METHOD_DATA =      (DIRECTORY = /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet)    )  )

Ensure that auto-login is enabled for wallet.

Start Oracle Wallet manager
To enable auto login:

1. Select Wallet from the menu bar.
2.Select Auto Login. A message at the bottom of the window indicates that auto login is enabled.

Wallet Auto Login

Now let’s try connecting to database

[[email protected] admin]$ sqlplus /@amit_test11r2

SQL*Plus: Release Production on Tue Sep 8 23:34:37 2009

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

SQL> show user

We have been able to login without specifying a password. In case you change password for Database User, you will have to modify credentials .If you don’t, your DB login will fail with ORA-1017.

SQL> alter user amit identified by amitbansal;

User altered.

[[email protected] admin]$ sqlplus /@amit_test11r2

SQL*Plus: Release Production on Tue Sep 8 23:35:34 2009

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

ORA-01017: invalid username/password; logon denied

To modify credential you need to use modifyCredential option. Syntax for command is

mkstore -wrl <wallet_location> -modifyCredential <dbase_alias> <username> <password>

[[email protected] wallet]$ mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet/
 -modifyCredential amit_test11r2 amit amitbansal
Oracle Secret Store Tool : Version - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:             

Modify credential
Modify 1

To delete credentials use deleteCredential option with tnsalias

 $mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet/
 -deleteCredential amit_test11r2
Oracle Secret Store Tool : Version - Production
Copyright (c) 2004, 2009, Oracle and/or its affiliates. All rights reserved.

Enter wallet password:             

Delete credential
Delete 1

You can add more users to these wallet , but you will have to use a separate TNSALIAS for it. Note that TNSALIAS is a unique identifier for each user to connect to database.

Amit Bansal

Experienced professional with 16 years of expertise in database technologies. In-depth knowledge of designing and implementation of Disaster Recovery / HA solutions, Database Migrations , performance tuning and creating technical solutions. Skills: Oracle,MySQL, PostgreSQL, Aurora, AWS, Redshift, Hadoop (Cloudera) , Elasticsearch, Python

This Post Has 12 Comments

  1. Gokul

    Excllent feature, explained neatly & simple. A long time pending security feature enabled in 5 mins.

    Gooooood work.

    Expecting more shortcuts(features).


    1. Amit

      Thanks Gokul..Yeah this is pretty useful feature and can be used in shell scripts, rman catalog connection and exp/imp for connecting to databases.


  2. Debi Lorraine

    I am intrigued…and puzzled how this is secure. Although you say the credentials are stored in a "client side" wallet, I assume this must be done on the same host as the database and relies on OS authentication of the user, or else what's to stop anyone from spoofing the user from any host? But then why wouldn't you just use "connect /"? I feel like I'm missing something.

    1. Amit

      No, it is not mandatory for database and client server to be on same machine. This setup can be done on any client machine. Secondly this is different from OS authentication as you can grant read access to other user or copy the wallet files to other user's directory and change the permissions. In case client is using DB home , it would be helpful if you use TNS_ADMIN variable for the user to have separate sqlnet.ora file/tnsnames.ora file.

      Do let me know if I have missed any point or you need further clarification.

  3. Debi Lorraine

    Thanks for the response. I think I am still missing something. What is to stop another user from using this connect string?

    1. Amit

      Anyone who has read permission on files ewallet.p12 and cwallet.sso can connect to the database using connect string. So if you wish to stop a particular user, ensure that the user does not have read permission on it.


      1. terkel

        I still dont get it, so if someone get the ewallet.p12 cwallet.sso files and the connect string (simply by reading/copy them from a Live USB system) he is able to connect to the database?! It seems not very secure to me…

  4. Niki

    Couple of questions

    Is this feature part of Oracle Vault? if not is it available with standard edition?

    Appreciate your time.

    1. Amit


      This is not part of oracle database vault.

      I didnt find any reference stating that we need enterprise edition to use this feature, so I assume it should be available. Best thing would be to try it.


  5. Atusa

    Fantastic Feature.
    Thank you so much.

Leave a Reply

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