I recently posted about Oracle Password Repository (OPR).
I did get a comment from Andreas Piesk about something similar in Oracle, the wallet.
Let’s do a quick test.
First, I create a .sqlnet.ora in my home directory (I do not want to mess up the system-wide sqlnet.ora).
$ cat /home/lsc/.sqlnet.ora
SQLNET.WALLET_OVERRIDE=TRUE
WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY=/home/lsc)))
I now create the wallet
$ mkstore -create -wrl /home/lsc
Enter password:
Enter password again:
and the credentials
$ mkstore -wrl /home/lsc -createCredential LSC01 scott tiger
Enter password:
Create credential oracle.security.client.connect_string1
now I try to login
$ sqlplus /@LSC01
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:23:35 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> sho user
USER is "SCOTT"
This sounds to work very nicely. Let’s see if this is more secure than OPR :
$ mkstore -wrl /home/lsc -list
Enter password:
Oracle Secret Store entries:
oracle.security.client.connect_string1
oracle.security.client.password1
oracle.security.client.username1
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.connect_string1
Enter password:
oracle.security.client.connect_string1 = LSC01
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.username1
Enter password:
oracle.security.client.username1 = scott
$ mkstore -wrl /home/lsc -viewEntry oracle.security.client.password1
Enter password:
oracle.security.client.password1 = tiger
Definitely! The password is not reveal, unless you know the password of the wallet. Remember in OPR, the application had direct access to the password. Here it is not the case, if you do not know the password of the wallet, you may login, but you cannot find out what the password is. I like this very much.
of course do not forget to protect your wallet
Ok, what I did not achieve until yet is logging in externally when using wallet_override :
$ mv /home/lsc/.sqlnet.ora /home/lsc/.sqlnet.ora.disable
$ sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:35:56 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> sho user
USER is "OPS$LSC"
$ mv /home/lsc/.sqlnet.ora.disable /home/lsc/.sqlnet.ora
$ sqlplus /
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 10 11:37:13 2006
Copyright (c) 1982, 2005, Oracle. All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
I will update this if I find out how to login externally too.
Hi Laurent,
Pretty cool tip. I like the fact that I don’t have to install any third party tools for this task.
I tried this out on my system and it was very easy to setup, just like you said.
I’m a regular reader of your postings and since I’m quite new to the Oracle DBA world, I find them very interesting.
Thanks
Padraig
thanks!
the wallet can be located in a file like above or also in Oracle Internet Directory, which is an Oracle LDAP server
I have some unix script which connects to two schema’s in the same database. I can use oracle wallet to connect to database using “/” to connect as first user, but how to connect second user in the same database using oracle wallet. One option is to use TNS alias but that will cause too many aliases for same database.Is it possible to use username to get password and then connect to database using oracle wallet similar to “sqlplus scott/$(opr -r LSC01 SCOTT)@LSC01”
Thanks
Suresh
Sorry if I’m mistaken, but I believe you determine which user is being used based on the credential alias.
maybe you could use separate wallet, and specify the location of sqlnet.ora with tns_admin
You cannot connect to Oracle using an external password in conjunction with the secret store. That would defeat the purpose. When using an external password (OS Authentication), your account is already authenticated to Oracle because the operating system has authenticated you. There would be no reason to keep an encrypted set of of credentials for you.
The sqlnet.ora parameter SQLNET.WALLET_OVERRIDE=TRUE is laterally telling Oracle client to use the wallet manager instead of OS Authentication.
If you want to connect to the same database with different accounts, then you would need separate entries in the tnsnames.ora file; however, you should be setting up services for this purpose and creating a new tnsnames.ora entry for each service. Connecting to the SID of the database is a “LESS THAN SIGN” 8i methodology and should not be practiced anymore.
Also, if you are using multiple accounts, each account should have its own wallet anyway. Therefore, they could share a common tnsnames.ora file, but use different credentials based on the entries in the wallet.
yes, it prevents os authentication. also true that you can use different service names.
I edited your comment, I hope this problem with wordpress is fixed by itself… thanks for reporting
Update, I’ve recenly discovered how to use the secure password store with JDBC! You need the most recent JDBC Driver, but it’s all in the URL from there.
Connection conn = DriverManager.getConnection (”jdbc:oracle:oci8:/@database_to_connect_to”);
No need to mess with Certificates!!!
Hi, require how to use oracle wallet in hibernate.
tried giving the below with no success.
any help will be much appreciated