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
 -create
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

AMIT_TEST11R2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db11g)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (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
 -listCredential
Oracle Secret Store Tool : Version 11.2.0.1.0 - 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

WALLET_LOCATION =
  (SOURCE =    (METHOD = FILE)
   (METHOD_DATA =      (DIRECTORY = /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet)    )  )
SQLNET.WALLET_OVERRIDE = TRUE

Ensure that auto-login is enabled for wallet.

Start Oracle Wallet manager
$owm
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

[oracle@db11g admin]$ sqlplus /@amit_test11r2

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

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

SQL> show user
USER is "AMIT"

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.

[oracle@db11g admin]$ sqlplus /@amit_test11r2

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

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

ERROR:
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>

[oracle@db11g 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 11.2.0.1.0 - 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 11.2.0.1.0 - 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.

11 thoughts on “Using Oracle Wallet to Execute Shell script/cron without hard coded Oracle database password

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

    Gooooood work.

    Expecting more shortcuts(features).

    Gokul…….

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

      Regards
      Amit

  2. Pingback: Blogroll Report 04/09/2009 – 11/09/2009 « Coskan’s Approach to Oracle

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

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

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

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

      Regards
      Amit

      • 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…

  5. Couple of questions

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

    Appreciate your time.

    • Niki,

      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.

      Cheers
      Amit

  6. Pingback: Finding blogging tough..naah.just copy and do find and replace | AskDba.org Weblog

Leave a Reply