Martins Blog

Trying to explain complex things in simple terms

Oracle’s secure external password store

Posted by Martin Bach on November 23, 2009

I have written a large number of nagios checks with various degrees of sophistication over the past, most of them perl scripts. The general problem one faces with these is the database login. Regardless of which way you chose, a password will be stored somewhere and nothing is worse than storing the sys password somewhere in cleartext for checking standby datatabases. I only found two ways acceptable:

  • Using the cryptographic API
  • Using Oracle’s secure password store

I freely admit that I never really attended cryptographic courses at the University and neither was I too keen on this as a solution was needed quickly. So sorry, I won’t go into the perl cryptography here (but I found this link useful: http://www.perl.com/pub/a/2001/07/10/crypto.html).

When I saw that RUEI (Real User Experience Insight) uses exactly this to connect to the database, I thought it’s really worth spending time with. The secure password store is also something very few people seem to know about, but bear in mind it’s very basic. If I remember correctly I tested this with DBD::Oracle 1.21 on Linux and Oracle 10.2.0.3 32bit.

An example is more verbose than 1000 lines of text so without further ado here’s a stub that worked:

#!/u01/app/oracle/product/10.2.0/db_1/perl/bin/perl

use strict;
use warnings;
use DBI;

my $dbh=DBI->connect("dbi:Oracle:secureTNSName");

my $sth = $dbh->prepare("select name from v\$database");
$sth->execute;

my $r;

while ($r = $sth->fetchrow_hashref('NAME_lc')) {
 print "$r->{name}\n";
}

$dbh->disconnect if defined $dbh;

The script uses Oracle’s perl which comes with every database, connects to the secure TNS name (note the lack of passwords!), queries and prints the database name. Before this works without a problem a few prerequisites are necessary.

First I created a new directory for my tnsnames.ora and sqlnet.ora files which didn’t interfere with the settings in $ORACLE_HOME/network/admin.

[oracle@devbox001 securePasswordStore]$ export TNS_ADMIN=`pwd`
[oracle@devbox001 securePasswordStore]$ echo $TNS_ADMIN
/home/oracle/martin/securePasswordStore

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` -create
Enter password:
Enter password again:
[oracle@devbox001 securePasswordStore]$ ls -lrt
total 20
-rw-r--r--    1 oracle   oinstall      163 Jun  5 10:31 tnsnames.ora
-rw-------    1 oracle   oinstall     7912 Jun  5 10:32 ewallet.p12
-rw-------    1 oracle   oinstall     7940 Jun  5 10:32 cwallet.sso

This creates the wallet and secures it with a password. So far the wallet is empty. Let’s add credentials to it:

[oracle@devbox001 securePasswordStore]$ mkstore -wrl `pwd` \
 >  -createCredential secureTNSName martin somePassword
Enter password:
Create credential oracle.security.client.connect_string1

This created a credential for user “martin” with password “somePassword” and an identifier “secureTNSName”. Let’s create the corresponding tns entry, we connect against single instance database “DEV”:

[oracle@devbox001 securePasswordStore]$ vi tnsnames.ora
secureTNSName =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = devbox001)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SERVICE_NAME = DEV)
    )
 )

[oracle@devbox001 securePasswordStore]$ tnsping secureTNSName

TNS Ping Utility for Linux: Version 10.2.0.3.0 - Production on 05-JUN-2009 10:35:07

Copyright (c) 1997, 2006, Oracle.  All rights reserved.

Used parameter files:

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
  (HOST = devbox001)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = DEV)))
OK (0 msec)

The sqlnet.ora file needs some changes as well, for me it was only the wallet location:

[oracle@devbox001 securePasswordStore]$ cat sqlnet.ora

SQLNET.WALLET_OVERRIDE = TRUE
WALLET_LOCATION =
 (SOURCE=
   (METHOD = FILE)
    (METHOD_DATA = (DIRECTORY=/home/oracle/martin/securePasswordStore)
   )
 )

With that we are now ready to connect! Note that the syntax requires you to specify the TNS name directly after the “/”, no spaces allowed.

[oracle@devbox001 securePasswordStore]$ sqlplus /@secureTNSName

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jun 5 10:43:33 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> show user
USER is "MARTIN"
SQL> select name from v$database;

NAME
---------
DEV

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Metalink references:

  • 340559.1 – Using The Secure External Password Store
  • 403744.1 – How to Use an External Password Store With The JDBC Driver

Sometimes it requires a training course to get to know these features, I picked this one up when I did the security training which really is more a course for developers but never mind.

8 Responses to “Oracle’s secure external password store”

  1. Don Seiler said

    Great example. Should be noted that, from what I can tell, this is only available as part of the Advanced Security option, which is separately licensed and only available in Enterprise Edition.

    • Martin said

      Hi Don,

      thanks for passing by! I was hoping that it’s a non cost option so I checked :)
      As per Metalink note 465460.1 “Differences Between Enterprise, Standard and Personal Editions on Oracle 11.1”, Secure External Password Store is available in SE and EE and most crucially it doesn’t list the “extra cost option”. But I am by no means a licensing expert…

      Martin

  2. Don Seiler said

    Martin, that is great news! I’ll try to get a definitive answer on this just so there are no surprises.

  3. Neil Campbell said

    Hi Guys,

    I was just reading through this Oracle document on Secure External Password Store and in the introduction it clearly says

    “This paper provides step-by-step instructions on how to use the Oracle Database Secure External Password Store feature, which was first available with Oracle Database 10g Release 2. This feature does not require the Oracle Advanced Security Option.

    So I believe there is no licensing issue!

    Cheers
    Neil

  4. Chris said

    I’ve run into a problem where perl DBI/DBD works correctly with regular usernames and passwords but will not work with Oracle Wallet.

    SQLPlus does work correctly with Oracle Wallet.

    I am using a $HOME/.sqlnet.ora file, and I don’t know if that matters.

    • Martin said

      I have only ever used it the way described in the article by setting a TNS_ADMIN environment variable. Have you tried this?

  5. Hi, When you have read access you can copied the wallet to different machines, which can represent a security risk. In release 11g Release 2, you can prevent this to secure the wallet to the local machine. This prevents the auto login functionality of the wallet from working if it is copied to another machine. To do this create a local wallet using the “orapki” command, instead of the “mkstore” command. Example:

    1) orapki wallet create -wallet “C:\oracle\product\11.2.0\joordsdb_1\NETWORK\ADMIN” -pwd “mypassword” -auto_login_local.

    2) C:\>mkstore -wrl “C:\oracle\product\10.2.0\joordsdb_1\NETWORK\ADMIN” -createCredential db10g scott tiger

    • Sam said

      One caveat, orapki will not only tie with the local machine, it will tie it with the setup user too. So say you setup orapki using user abc with group xyz. Now if you add user qrs to the group xyz, and try to access the wallet as qrs, you wouldn’t be able to. This can be worked out by having multiple sqlnet.ora and tnsnames.ora entries and pointing the TNS_ADMIN to the corresponding environment specific entry. It is a pain, in multi user environment, but it is better than having to embed the password in the clear text.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: