You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




10 August 2006

sqlnet.wallet_override=true

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.

8 Comments:

Blogger Padraig said...

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

11/8/06 23:00  
Blogger Laurent Schneider said...

thanks!
the wallet can be located in a file like above or also in Oracle Internet Directory, which is an Oracle LDAP server

12/8/06 18:58  
Anonymous Anonymous said...

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

25/8/06 16:24  
Anonymous Anonymous said...

Sorry if I’m mistaken, but I believe you determine which user is being used based on the credential alias.

18/10/06 01:59  
Blogger Laurent Schneider said...

maybe you could use separate wallet, and specify the location of sqlnet.ora with tns_admin

18/10/06 08:26  
Anonymous Anonymous said...

Hi, great tip.

You mentioned "I will update this if I find out how to login externally too." - did you ever figure out if it's possible to connect to separate databases as the same user, one with the wallet, one using a genuine external connection? The only way I could do this was using a separate TNS_ADMIN/sqlnet.ora.

Many thanks

Bruce

21/1/08 22:56  
Blogger Laurent Schneider said...

I think a separate tnsadmin is the only way

22/1/08 06:51  
Anonymous Anonymous said...

Actually a separate tnsadmin isn't the only way. Create multiple tns service_name aliases. For example:

ORCL,ORCL_USER1,ORCL_USER2=
(DESCRIPTION = ...

Then each users connects using their own tns service_name and the userid as stored in the Oracle wallet:

sqlplus /@ORCL_USER1
sqlplus /@ORCL_USER2

13/10/16 17:00  

Post a Comment

<< Home