How to encrypt with the Oracle JDBC driver

Frank4DD, @2017

Introduction

JDBC is a set of classes and interfaces written in Java that allows Java programs to access a database. This how-to explains how to use encryption with Oracle's JDBC thin driver. Database connection encryption becomes increasingly important to protect database query transmissions over long distance, insecure channels, and to add another layer of protection.

Oracle offers two methods for database connection encryption: Native Network Encryption and SSL/TLS over TCP/IP. This guide refers to Oracle Native Network Encryption. Native network encryption allows to secure database connections without the configuration overhead of SSL/TLS which requires certificate management, and the need to create and listen on separate, dedicated ports. All changes are done in the "sqlnet.ora" file on the client and server.

Database server side setup information

While this guide is focussed on the client side, some key information below is needed from the database server side.

The server side configuration parameters in "sqlnet.ora" are as follows:

oracle@lts140464:~$ cat /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# sqlnet.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
SQLNET.ENCRYPTION_SERVER=REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER=(AES256)

The database server can also be considered a client if it is making local client calls, we may want to include the client settings for local connections as well.

SQLNET.ENCRYPTION_CLIENT=REQUESTED
SQLNET.ENCRYPTION_TYPES_CLIENT=(AES256)

The possible values for the SQLNET.ENCRYPTION_SERVER and SQLNET.ENCRYPTION_CLIENT parameters are as follows.

ACCEPTED: The client or server will allow both encrypted and non-encrypted connections. (default)
REJECTED: The client or server will refuse encrypted traffic.
REQUESTED: The client or server will request encrypted traffic if it is possible, but will accept non-encrypted traffic if encryption is not possible.
REQUIRED: The client or server will only accept encrypted traffic.

The combination of the client and server settings will determine if encryption is used, not used or if the connection is rejected. The SQLNET.ENCRYPTION_TYPES_SERVER and _CLIENT parameters accept a comma-separated list of encryption algorithms. If no encryption type is set, all available encryption algorithms are considered. Available algorithms may depend on the database version and change over time as algorithms get retired per progress in encryption technology. Below is the algorithm table from Oracle 11G R2:

Algorithm Name	Legal Value	Algorithm Name	Legal Value	Algorithm Name	Legal Value
RC4 256-bit key	RC4_256	AES 128-bit key	AES128	RC4 128-bit	RC4_128
3-key 3DES	3DES168	RC4 56-bit	RC4_56	2-key 3DES	3DES112
RC4 40-bit	RC4_40	DES 56-bit key	DES	AES 256-bit	AES256
DES 40-bit key	DES40	AES 192-bit	AES192

JDBC Java code for encrypted Oracle database access

The following example code JdbcEncOracle.java can be used to quickly access and test the JDBC connection.

vi JdbcEncOracle.java
//JdbcEncOracle.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import java.util.Properties;
import oracle.net.ns.*;
import oracle.net.ano.*;
import oracle.jdbc.*;
import oracle.jdbc.pool.*;
import oracle.jdbc.pool.OracleDataSource;

class JdbcEncOracle {
  public static void main (String args[]) {
      try {
        Class.forName("oracle.jdbc.driver.OracleDriver");
       }
        catch (ClassNotFoundException e) {
            System.err.println(e);
            System.exit(-1);
        }
       try {
           Properties props = new Properties();
           props.put("oracle.net.encryption_client", "REQUIRED");
           props.put("oracle.net.encryption_types_client", "( AES256 )");

           OracleDataSource ods = new OracleDataSource();
           ods.setConnectionProperties(props);
           ods.setURL("jdbc:oracle:thin:system/test@localhost:1521:ORCL");

           // open connection to database
           Connection connection = ods.getConnection();

           // build query
           String query = "SELECT * From DUAL";

           // execute query
           Statement statement = connection.createStatement();
           ResultSet rs = statement.executeQuery(query);

           // show query results
           while ( rs.next() )
               System.out.println("Oracle Query: " + rs.getString(1));
           connection.close();
        }
        catch (java.sql.SQLException e) {
            System.err.println(e);
            System.exit(-1);
        }
    }
}

Compile and run the test program

fm@susie:~> javac JdbcEncOracle.java 

fm@susie:~> java JdbcEncOracle
Oracle Query: X

Should this test fail, typical reasons are:

Wrong database user, or database user did not get the roles "connect" and "resource".
The Oracle listener is not running, therefore SQLnet connections fail.

Sometimes, its a question of versions. At the time of writing the following versions were available:

oracle@lts140464:~$ sqlplus

SQL*Plus: Release 12.1.0.2.0 Production on Fri Dec 8 19:26:58 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter user-name:

And at the client side:

fm@susie:~> java -cp ojdbc7.jar oracle.jdbc.OracleDriver -version
Oracle 12.1.0.2.0 JDBC 4.1 compiled with JDK7 on Tue_Apr_26_11:15:59_PDT_2016
#Default Connection Properties Resource
#Fri Dec 08 19:23:57 JST 2017

fm@susie:~> java -version
java version "1.7.0_151"
OpenJDK Runtime Environment (IcedTea 2.6.11) (7u151-2.6.11-2ubuntu0.14.04.1)
OpenJDK 64-bit Server VM (build 24.151-b01, mixed mode)

Database How-To's

How to use native network encryption with the Oracle JDBC driver

Introduction

Database server side setup information

JDBC Java code for encrypted Oracle database access

Compile and run the test program

See also: