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.

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 NameLegal Value Algorithm NameLegal Value Algorithm NameLegal Value
RC4 256-bit keyRC4_256 AES 128-bit keyAES128 RC4 128-bitRC4_128
3-key 3DES3DES168 RC4 56-bitRC4_56 2-key 3DES3DES112
RC4 40-bitRC4_40 DES 56-bit keyDES AES 256-bitAES256
DES 40-bit keyDES40 AES 192-bitAES192 

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:

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)

See also: How to install and use Oracle JDBC drivers