Using Oracle Wallets and SEPS with Perl, JDBC, and ODBC

Posted in: Technical Track

A few years ago I wrote a couple of popular blog articles on using Secure External Password Stores (SEPS) and Credential Wallets to manage passwords used by scripts for database connections:

Securing Oracle Monitoring And Backup Scripts
Issues With Oracle Secure External Password Stores

I’ve also presented on this topic at many conferences including Oracle OpenWorld and IOUG COLLABORATE. Some questions I am often asked at these events are:

  1. Exactly how do I make connections using SEPS and Oracle Wallets with Perl, JDBC Thin, or ODBC?
  2. Can we use certificates instead of passwords in the Wallet files?

In this article, I’ll address those questions (in the reverse order).

Using a Certificate Instead of a Password in an Oracle Wallet

Oracle SEPS Wallets can certainly be setup to store a certificate instead of a username and password credential.

I’m not going to go into the setup steps for this in this article as it’s already well documented in the My Oracle Support (MOS) document:

Configuring SSL for Client Authentication and Encryption With Self Signed Certificates On Both Ends Using orapki (Doc ID 401251.1)

Some thoughts on this option/configuration:

  • Using a certificate can possibly be considered even more secure as it means there is no password associated with the database user and hence no other way for connections to be made.  A wallet with the certificate is hence mandatory.
  • We still have the risk of someone (including a bad actor) obtaining a copy of the wallet files and hence using the enclosed certificates but there’s less risk of a lateral attack (meaning using those credentials against a different database than intended) because the same client certificate would also need to be imported on the secondary server and we can still secure the wallet files to the local machine using the “-auto_login_local” option (mentioned in the previous articles).
  • There’s still an issue (bug) that may be encountered with the “-auto_login_local” option specifically with Oracle version 12.1.0.2 (discussed in the previous articles). And if encountered, we can still mitigate that using the work-around described in the other blog article or by using 12.2 binaries to create the wallet files regardless of the database version.
  • The setup is bit more complex and possibly requires a database restart (hence outage) and likely requires a listener restart.

Using Certificates in wallet files for database authentication is certainly a viable option depending on your circumstances.

But for the rest of this article I’ll be testing using credentials stored in the wallet. All examples created using Oracle Linux 7 with Oracle Database 12.1.0.2.  And I try to make the sample scripts as short and simple as possible for clarity.

Testing Setup

I have already configured the Oracle Wallet as per the previous blog articles and have two credentials stored within:

$ mkstore -wrl $ORACLE_BASE/wallets -listCredential
Oracle Secret Store Tool : Version 12.1.0.2
Copyright (c) 2004, 2014, Oracle and/or its affiliates. All rights reserved.
 
Enter wallet password:
List credential (index: connect_string username)
2: ORCL scott
1: 192.168.1.100:1521/ORCL FRANKLIN
$

And I can test the functionality with:

$ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s / as sysdba
 
Connected as: SYS
 
$ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s '/@ORCL'
 
Connected as: SCOTT
 
$ echo "SELECT 'Connected as: '||user FROM DUAL;" | sqlplus -s '/@192.168.1.100:1521/ORCL'
 
Connected as: FRANKLIN
 
$

(Note: I do have “SET HEADING OFF” in $ORACLE_HOME/sqlplus/admin/glogin.sql)

SEPS Connections Using Perl

Making the connections with Perl is almost trivially simple.  Just set the username and password arguments to an empty string in the DBI->connect method.

Sample script “OraConnect.pl“:

use DBI;
 
my $dbh = DBI->connect('dbi:Oracle:ORCL','','',{RaiseError=>1,PrintError=>1}) || die( $DBI::errstr . "\n" );
if ($dbh ne undef) {
   my $sth = $dbh->prepare(qq{SELECT 'Connected as: '||user FROM DUAL});
   $sth->execute();
   print $sth->fetchrow_array()."\n";
   $sth->finish();
   $dbh->disconnect || warn $dbh->errstr;
}

Execution using the Oracle distributed Perl is as simple as:

$ $ORACLE_HOME/perl/bin/perl OraConnect.pl
Connected as: SCOTT
$

Replacing the connect method data_source to use an EZconnect string shows it works equally as well:

$ grep Oracle OraConnect.pl
my $dbh = DBI->connect('dbi:Oracle:192.168.1.100:1521/ORCL','','',{RaiseError=>1,PrintError=>1}) || die( $DBI::errstr . "\n" );
 
$ $ORACLE_HOME/perl/bin/perl OraConnect.pl
Connected as: FRANKLIN
$

So with perl the only change required is to use an empty string for both the username and password.

SEPS Connections Using JDBC THIN

To test connections using the JDBC THIN driver and software distributed with the Oracle software is also pretty easy but first I need to create a small block of JAVA source code.

Again, the idea is the same: not to specify any username or password in the source code.  And to specify a SEPS formatted connection string such as “/@192.168.1.100:1521/ORCL”.

Simple JAVA source code file “OraConnect.java“:

import java.sql.*;
 
public class OraConnect {
 
   public static void main(String[] argv) {
 
      try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
      } catch (ClassNotFoundException e) {
         System.out.println("Can't find the Oracle JDBC Driver");
         e.printStackTrace();
         return;
      }
 
      try {
         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:/@192.168.1.100:1521/ORCL");
 
         Statement stmt = conn.createStatement();
         ResultSet rset = stmt.executeQuery("SELECT 'Connected as '||user FROM DUAL");
         while (rset.next())
            System.out.println(rset.getString(1));
         rset.close();
         stmt.close();
 
      } catch (SQLException e) {
         System.out.println("Connection Failed");
         e.printStackTrace();
         return;
      }
 
   }
 
}

If we compile and run as normal, an error is generated:

$ $ORACLE_HOME/jdk/bin/javac OraConnect.java
$ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:. OraConnect
Connection Failed
java.sql.SQLException: PKI classes not found. To use 'connect /' functionality, oraclepki.jar must be in the classpath: java.lang.NoClassDefFoundError: oracle/security/pki/OracleWallet
at oracle.jdbc.driver.PhysicalConnection.getSecretStoreCredentials(PhysicalConnection.java:1445)
at oracle.jdbc.driver.PhysicalConnection.parseUrl(PhysicalConnection.java:1210)
at oracle.jdbc.driver.PhysicalConnection.readConnectionProperties(PhysicalConnection.java:961)
at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:620)
at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:398)
at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:31)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
at java.sql.DriverManager.getConnection(DriverManager.java:582)
at java.sql.DriverManager.getConnection(DriverManager.java:154)
at OraConnect.main(OraConnect.java:20)
$

So we must specify the PKI classes.  Adding $ORACLE_HOME/jlib/oraclepki.jar and trying again gives another error:

$ $ORACLE_HOME/jdk/bin/javac OraConnect.java
$ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:. OraConnect
Connection Failed
java.sql.SQLException: ORA-01017: invalid username/password; logon denied
 
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:450)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:392)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:385)
        at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1018)
        at oracle.jdbc.driver.T4CTTIoauthenticate.processError(T4CTTIoauthenticate.java:497)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:522)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:257)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:433)
        at oracle.jdbc.driver.T4CTTIoauthenticate.doOAUTH(T4CTTIoauthenticate.java:950)
        at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:639)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
        at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
        at java.sql.DriverManager.getConnection(DriverManager.java:582)
        at java.sql.DriverManager.getConnection(DriverManager.java:207)
        at OraConnect.main(OraConnect.java:16)
$

The ORA-01017 error is common when dealing with SEPS and Wallets and sometimes can be a bit misleading. When using OCI (such as standard SQLPlus or RMAN connections), the “ORA-12578: TNS:wallet open failed” error is usually returned when the wallet files cannot be found or read.

However, the JDBC driver is working a little bit differently meaning that in this case it’s not finding the wallet file and instead is trying to connect to the database with the null username and password (as provided in the JAVA code) and therefore is getting the ORA-01017 error returned from the database. (Database auditing can be used to confirm this.)

Therefore we need to add the “-Doracle.net.wallet_location” JAVA command line argument and try one more time:

$ $ORACLE_HOME/jdk/bin/javac OraConnect.java
$ $ORACLE_HOME/jdk/jre/bin/java -cp $ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:. -Doracle.net.wallet_location=$ORACLE_BASE/wallets OraConnect
Connected as FRANKLIN
$

As we can see, now it works.  But we had to change the java command line arguments.

If we don’t want to modify the java call, an alternative is to add the wallet location as a “property” within the JAVA source code.  Hence the “OraConnect.java” script becomes:

import java.sql.*;
import java.util.Properties;
 
public class OraConnect {
 
   public static void main(String[] argv) {
 
      try {
         Class.forName("oracle.jdbc.driver.OracleDriver");
      } catch (ClassNotFoundException e) {
         System.out.println("Can't find the Oracle JDBC Driver");
         e.printStackTrace();
         return;
      }
 
      try {
         Properties props = new Properties();
         props.setProperty("oracle.net.wallet_location","(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=/u01/app/oracle/wallets)))");
 
         Connection conn = DriverManager.getConnection("jdbc:oracle:thin:/@192.168.1.100:1521/ORCL",props);
 
         Statement stmt = conn.createStatement();
         ResultSet rset = stmt.executeQuery("SELECT 'Connected as '||user FROM DUAL");
         while (rset.next())
            System.out.println(rset.getString(1));
         rset.close();
         stmt.close();
 
      } catch (SQLException e) {
         System.out.println("Connection Failed");
         e.printStackTrace();
         return;
      }
 
   }
 
}

And by specifying the JAVA classes via the CLASSPATH variable, no extra command line arguments are required:

$ export CLASSPATH=$ORACLE_HOME/jdbc/lib/ojdbc6.jar:$ORACLE_HOME/jlib/oraclepki.jar:.
$ $ORACLE_HOME/jdk/bin/javac OraConnect.java
$ $ORACLE_HOME/jdk/jre/bin/java OraConnect
Connected as FRANKLIN
$

As you may have noticed with these examples, JDBC THIN connections work best with EZconnect connection strings (or fully expanded OracleNet connection strings).  To use a TNS service name we must also use the “-Doracle.net.tns_admin” command line option or include the “oracle.net.tns_admin” property in the source code.

SEPS Connections Using ODBC

To test ODBC connections from Linux, I first had to first perform some prerequisite setup by root.  Specifically installing unixODBC from the ol7_latest repo:

# yum install -y unixODBC*

Verifying the location of the ODBC Driver Manager configuration files:

# odbcinst -j
unixODBC 2.3.1
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/oracle/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8
#

I then need to add the Oracle driver information to /etc/odbcinst.ini, providing the full path to the libsqora.so file from the $ORACLE_HOME.  After adding we see:

# tail -7 /etc/odbcinst.ini
[OracleODBC]
Description     = Oracle ODBC Driver
Driver          = /u01/app/oracle/product/12.1.0/dbhome_1/lib/libsqora.so.12.1
Setup           =
FileUsage       =
CPTimeout       =
CPReuse         =

Now that the prerequisite work is done, testing as the “oracle” user again, I can setup some local DSNs in ~/odbc.ini.

I’ve added two almost identical DSNs in my ~/odbc.ini file. The first one I’ve called “[ORCL_TNS]” and I specify “ServerName = ORCL” (referencing my TNS service name).

The second DSN is called “[ORCL_EZ]” and it’s the same except it uses “ServerName = 192.168.1.100:1521/ORCL” to force the EZconnect connection.

In both cases the “UserID” DSN property is not set and I reference the Oracle database driver created previously via “Driver = OracleODBCDriver”.

Hence the entries are simply:

$ cat ~/.odbc.ini
[ORCL_TNS]
Driver = OracleODBCDriver
ServerName = ORCL
 
[ORCL_EZ]
Driver = OracleODBCDriver
ServerName = 192.168.1.100:1521/ORCL
$

Testing both entries :

$ echo "SELECT user FROM DUAL" | isql ORCL_TNS
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT user FROM DUAL
+-------------------------------+
| USER                          |
+-------------------------------+
| SCOTT                         |
+-------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL> 
 
$ echo "SELECT user FROM DUAL" | isql ORCL_EZ
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT user FROM DUAL
+-------------------------------+
| USER                          |
+-------------------------------+
| FRANKLIN                      |
+-------------------------------+
SQLRowCount returns -1
1 rows fetched
SQL>

Worked perfectly and was pretty simple to set up!

Conclusion

The above examples illustrate how simple it is to using SEPS and Oracle Wallets with various additional languages/protocols. (The previous articles covered making similar connections using the Oracle Call Interface [OCI] and tools like SQLPlus and RMAN). The only catch is that JDBC THIN connections are best suited with EZconnect connection strings (instead of TNS service entries) when using SEPS.

Interestingly this discussion brings up an important point or risk: are your database passwords possibly exposed in your source code? Is that matter made worse due to the scope and visibility of that source code in your source control management system? Or subject to more risks of accidental exposure (such as in a repo clone). In which case, this is yet another strong justification for using SEPS in the first place as it means that your source code no longer is a security exposure due to hardcoded passwords!

Additional References

Using a Secure External Password Store with the JDBC Thin Driver (Doc ID 1441745.1)
How to Configure the Unix ODBC Driver Manager (64-bit) on Linux x86-64 (Doc ID 742548.1)
How To Configure Oracle External Password Store (Wallet) To Work With Oracle ODBC (Doc ID 1430666.1)

email

Interested in working with Simon? Schedule a tech call.

About the Author

Simon describes himself as a technology enthusiast who is passionate about collecting and sharing interesting database tips. If you want to see his eyes light up, let him teach you something new. Based out of Calgary, Alberta, Simon is known for his contributions to various online Oracle communities, and being very thorough in his work. A self-proclaimed stereotypical Canadian, Simon can be found watching hockey with his family in his spare time.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *