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
[[email protected] 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. [[email protected] 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>
[[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.
This Post Has 12 Comments
Excllent feature, explained neatly & simple. A long time pending security feature enabled in 5 mins.
Expecting more shortcuts(features).
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.
Pingback: Blogroll Report 04/09/2009 – 11/09/2009 « Coskan’s Approach to Oracle
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.
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.
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…
Couple of questions
Is this feature part of Oracle Vault? if not is it available with standard edition?
Appreciate your time.
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.
Pingback: Finding blogging tough..naah.just copy and do find and replace | AskDba.org Weblog
Thank you so much.