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.

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.

Tags: 10g11gdatabaseoracle