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.
