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 [email protected]_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
[[email protected] admin]$ sqlplus [email protected]_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. [[email protected] admin]$ sqlplus [email protected]_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>
[[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 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.