AskDba.org Weblog

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

<span style="font-family: verdana,geneva;">mkstore -wrl &lt;wallet_location&gt; -createCredential &lt;db_connect_string&gt; &lt;username&gt; &lt;password&gt;</span>

  $mkstore -wrl /u02/app/oracle/product/11.2.0/dbhome_1/network/admin/wallet
-<strong>createCredential</strong> 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
 -<strong>listCredential</strong>
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/
 -<strong>modifyCredential</strong> 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.