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 188.8.131.52.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
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 184.108.40.206.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 220.127.116.11.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 18.104.22.168.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 22.214.171.124.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.