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

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?

Using a Certificate Instead of a Password in an Oracle Wallet

• 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.

Testing Setup

$ 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
 $
 

$ 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
 
 $
 

SEPS Connections Using Perl

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;
 }
 

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

$ 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
 $
 

SEPS Connections Using JDBC THIN

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;
   }
 
   }
 
 }
 

$ $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)
 $
 

$ $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)
 $
 

$ $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
 $
 

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;
   }
 
   }
 
 }
 

$ 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
 $
 

SEPS Connections Using ODBC

# yum install -y unixODBC*
 

# 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
 #
 

# 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 =
 

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

$ 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>
 

Conclusion

Additional References