Network encryption hands-on with Java, Python and SQL*Plus

Posted on , updated on by Yannick Jaquier
Oracle, Python

Table of contents

Preamble

Few years after my blog post on Oracle network encryption we have finally decided to implement it, whenever possible, on our Sarbanes-Oxley (SOX) perimeter at least. For those databases, connecting using network encryption will not be an option so it simplify the database part configuration to reject any connection that is not secure.

This blog post is about the amount of burden you might have moving to network encryption to encrypt communication between your databases and your clients (users and/or applications).

Testing has been done using:

  • A 19c (19.10) pluggable database (pdb1) running on a RedHat 7.8 physical server.
  • A 19c (19.3) 64 bits Windows client installed on my Windows 10 laptop.
  • OpenJDK version “1.8.0_282”. The Windows binaries have been found on RedHat web site as Microsoft is starting at Java 11.
  • Python 3.7.9 on Windows 64 bits and cx-Oracle 8.1.0.

Database server configuration for network encryption

Upfront nothing is configured and connection to your database server can be unsecure if requested. It is always a good idea to test everything from a simple Oracle client even if in real life your application will not use a client (Java or else instead):

PS C:\> tnsping //server01.domain.com:1531/pdb1
 
TNS Ping Utility for 64-bit Windows: Version 19.0.0.0.0 - Production on 15-APR-2021 10:38:01
 
Copyright (c) 1997, 2019, Oracle.  All rights reserved.
 
Used parameter files:
C:\app\client\product\19.0.0\client_1\network\admin\sqlnet.ora
 
Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=pdb1))(ADDRESS=(PROTOCOL=tcp)(HOST=10.75.43.64)(PORT=1531)))
OK (110 msec)
PS C:\> sqlplus yjaquier@//server01.domain.com:1531/pdb1
 
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 15 10:38:12 2021
Version 19.3.0.0.0
 
Copyright (c) 1982, 2019, Oracle.  All rights reserved.
 
Enter password:
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.10.0.0.0
 
SQL> select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat);
 
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production

Remark:
The query is coming from Administering Oracle Database Classic Cloud Service official Oracle documentation. Here no encryption or crypto activated, the displayed text is simply saying that everything is ready to be used if required…

It is now time to play with sqlnet.ora parameters to activate network encryption:

  • SQLNET.ENCRYPTION_SERVER
  • SQLNET.ENCRYPTION_CLIENT

Possible values of both parameters are:

  • accepted to enable the security service if required or requested by the other side.
  • rejected to disable the security service, even if required by the other side.
  • requested to enable the security service if the other side allows it.
  • required to enable the security service and disallow the connection if the other side is not enabled for the security service.

For my requirement the only acceptable value for SQLNET.ENCRYPTION_SERVER is required… On a side note, except for testing purpose, I am wondering the added value of rejected value. Why would you intentionally reject a secure connection if it is possible ??!!

So in sqlnet.ora of my database server I set:

SQLNET.ENCRYPTION_SERVER=required

Then from my SQL*Plus client if I set nothing I get:

SQL> select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat);
 
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production
Encryption service for Linux: Version 19.0.0.0.0 - Production
AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production

This because SQLNET.ENCRYPTION_CLIENT default value is accepted. We see then encryption algorithm is AES256, this is because SQLNET.ENCRYPTION_TYPES_CLIENT and SQLNET.ENCRYPTION_TYPES_SERVER contains by default all encryption algorithms i.e.:

  • 3des112 for triple DES with a two-key (112-bit) option
  • 3des168 for triple DES with a three-key (168-bit) option
  • aes128 for AES (128-bit key size)
  • aes192 for AES (192-bit key size)
  • aes256 for AES (256-bit key size)
  • des for standard DES (56-bit key size)
  • des40 for DES (40-bit key size)
  • rc4_40 for RSA RC4 (40-bit key size)
  • rc4_56 for RSA RC4 (56-bit key size)
  • rc4_128 for RSA RC4 (128-bit key size)
  • rc4_256 for RSA RC4 (256-bit key size)

If I explicitly set SQLNET.ENCRYPTION_CLIENT=rejected I get:

ERROR:
ORA-12660: Encryption OR crypto-checksumming parameters incompatible

If you want your client to connect to your database server using a chosen algorithm you can set in your database server sqlnet.ora file:

SQLNET.ENCRYPTION_TYPES_SERVER=3des168

And you get when connecting:

SQL> SELECT network_service_banner FROM v$session_connect_info WHERE sid IN (SELECT DISTINCT sid FROM v$mystat);
 
NETWORK_SERVICE_BANNER
--------------------------------------------------------------------------------
TCP/IP NT Protocol Adapter FOR Linux: Version 19.0.0.0.0 - Production
Encryption service FOR Linux: Version 19.0.0.0.0 - Production
3DES168 Encryption service adapter FOR Linux: Version 19.0.0.0.0 - Production
Crypto-checksumming service FOR Linux: Version 19.0.0.0.0 - Production

Activating network encryption with Java

As written above your application will surely not connect to your database server using SQL*Plus. SQL*Plus is quite handy when you need to test that network encryption is working but most probably your application is using Java. So how does it works in Java ? Let try it…

Same as plenty of blog posts I have written on this web site I will be using Eclipse that is free and is a nice Java editor with syntax completion to help you.

First download the JDBC driver that suit your environment. My database and client are in 19c so I have taken 19c JDBC driver and as I’m still using OpenJDK 8 (one day I will have to upgrade myself !!) I have chosen finally to use ojdbc8.jar that is certified with JDK 8.

Choose the JDBC driver that is the exact version of your Oracle client or you will get below error message when using JDBC OCI driver:

Exception in thread "main" java.lang.Error: Incompatible version of libocijdbc[Jdbc:1910000, Jdbc-OCI:193000
	at oracle.jdbc.driver.T2CConnection$1.run(T2CConnection.java:4309)
	at java.security.AccessController.doPrivileged(Native Method)
	at oracle.jdbc.driver.T2CConnection.loadNativeLibrary(T2CConnection.java:4302)
	at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:487)
	at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:807)
	at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:66)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:770)
	at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:572)
	at java.sql.DriverManager.getConnection(DriverManager.java:664)
	at java.sql.DriverManager.getConnection(DriverManager.java:208)
	at network_encryption.network_encryption.main(network_encryption.java:30)

Add JDBC jar file (ojdbc8.jar) in your Eclipse project with “Add Eternal JAR”:

network_encryption01
network_encryption01

From client perspective Oracle JDBC is made of two different drivers:

  • Thin driver: The JDBC Thin driver is a pure Java, Type IV driver that can be used in applications
  • Oracle Call Interface (OCI) driver: It is used on the client-side with an Oracle client installation. It can be used only with applications.

Statement of Oracle is pretty clear:

In general, unless you need OCI-specific features, such as support for non-TCP/IP networks, use the JDBC Thin driver.

Oracle documentation is providing this clear table and I well recall to have used JDBC OCI driver when testing Transparent Application Failover (TAF):

network_encryption02
network_encryption02

Oracle JDBC Thin Driver

The small java code I have written is:

  /**
  * 
  */
 /**
  * @author Yannick Jaquier
  *
  */
 package network_encryption;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Properties;
 import oracle.jdbc.OracleConnection;
 //import oracle.jdbc.pool.OracleDataSource;
 
 public class network_encryption {
   public static void main(String[] args) throws Exception {
     Connection connection1 = null;
     String query1 = "select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat)";
     String connect_string = "//server01.domain.com:1531/pdb1";
     ResultSet resultset1 = null;
     Properties props = new Properties();
     //OracleDataSource ods = new OracleDataSource();
     OracleConnection oracleconnection1 = null;
 
     try {
       props.setProperty("user","yjaquier");
       props.setProperty("password","secure_password");
       props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL, "ACCEPTED");
       props.setProperty(OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES, "3des168");
       connection1 = DriverManager.getConnection("jdbc:oracle:thin:@" + connect_string, props);
       oracleconnection1 = (OracleConnection)connection1;
     }
     catch (SQLException e) {
       System.out.println("Connection Failed! Check output console");
       e.printStackTrace();
       System.exit(1);
     }
     System.out.println("Connected to Oracle database...");
 
     if (oracleconnection1!=null) {
       try {
         resultset1 = oracleconnection1.createStatement().executeQuery(query1);
         while (resultset1.next()) {
           System.out.println("Banner: "+resultset1.getString(1));
         }
         System.out.println("Used Encryption Algorithm: "+oracleconnection1.getEncryptionAlgorithmName());
       }
       catch (SQLException e) {
         System.out.println("Query has failed...");
         e.printStackTrace();
         System.exit(1);
       }
     }
     resultset1.close();
     connection1.close(); 
   }
 }

The console output is clear:

network_encryption03
network_encryption03

If for example I set OracleConnection.CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_LEVEL to REJECTED I get below expected feedback (ORA-12660):

network_encryption04
network_encryption04

Oracle JDBC OCI Driver

The Java code for JDBC OCI driver is almost the same except that you have much less available parameters (CONNECTION_PROPERTY_THIN_NET_ENCRYPTION_TYPES) and functions (getEncryptionAlgorithmName). SO the idea is to link your applicative code with an instant client (or thick client if you like) and set getEncryptionAlgorithmName system variable to be able to play with your local sqlnet.ora.

The Java code is almost the same:

  /**
  * 
  */
 /**
  * @author Yannick Jaquier
  *
  */
 package network_encryption;
 
 import java.sql.Connection;
 import java.sql.DriverManager;
 import java.sql.ResultSet;
 import java.sql.SQLException;
 import java.util.Properties;
 import oracle.jdbc.OracleConnection;
 //import oracle.jdbc.pool.OracleDataSource;
 
 public class network_encryption {
   public static void main(String[] args) throws Exception {
     Connection connection1 = null;
     String query1 = "select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat)";
     String connect_string = "//server01.domain.com:1531/pdb1";
     ResultSet resultset1 = null;
     Properties props = new Properties();
     //OracleDataSource ods = new OracleDataSource();
     OracleConnection oracleconnection1 = null;
 
     try {
       props.setProperty("user","yjaquier");
       props.setProperty("password","secure_password");
       System.setProperty("oracle.net.tns_admin","C:\\app\\client\\product\\19.0.0\\client_1\\network\\admin");
       connection1 = DriverManager.getConnection("jdbc:oracle:oci:@" + connect_string, props);
       oracleconnection1 = (OracleConnection)connection1;
     }
     catch (SQLException e) {
       System.out.println("Connection Failed! Check output console");
       e.printStackTrace();
       System.exit(1);
     }
     System.out.println("Connected to Oracle database...");
 
     if (oracleconnection1!=null) {
       try {
         resultset1 = oracleconnection1.createStatement().executeQuery(query1);
         while (resultset1.next()) {
           System.out.println("Banner: "+resultset1.getString(1));
         }
       }
       catch (SQLException e) {
         System.out.println("Query has failed...");
         e.printStackTrace();
         System.exit(1);
       }
     }
     resultset1.close();
     connection1.close(); 
   }
 }

If in my sqlnet.ora I set:

SQLNET.ENCRYPTION_CLIENT=accepted
SQLNET.ENCRYPTION_TYPES_CLIENT=(3des168)

I get:

network_encryption05
network_encryption05

But if I set:

SQLNET.ENCRYPTION_CLIENT=rejected
SQLNET.ENCRYPTION_TYPES_CLIENT=(3des168)

I get:

network_encryption06
network_encryption06

Activating network encryption with Python

The de-facto package to connect to an Oracle database in Python is cx_Oracle ! I am not detailing how to configure this in a Python virtual environment as Internet is full of tutoriels on this already…

The cx_Oracle Python package is relying on the local client installation so you end up using the sqlnet.ora file that we have seen with SQL*Plus client.

The small Python code (network_encrytion.py) I have written is:

import cx_Oracle
import config
 
connection = None
query1 = "select network_service_banner from v$session_connect_info where sid in (select distinct sid from v$mystat)"
try:
  connection = cx_Oracle.connect(
    config.username,
    config.password,
    config.dsn,
    encoding=config.encoding)
 
  # show the version of the Oracle Database
  print(connection.version)
 
  # Fetch and display rows of banner query
  with connection.cursor() as cursor:
    cursor.execute(query1)
    rows = cursor.fetchall()
    if rows:
      for row in rows:
        print(row)
 
except cx_Oracle.Error as error:
  print(error)
finally:
  # release the connection
  if connection:
    connection.close()

You also need to put is same directory below config.py file:

username = 'yjaquier'
password = 'secure_password'
dsn = 'server01.domain.com:1531/pdb1'
encoding = 'UTF-8'

If in my sqlnet.ora file set SQLNET.ENCRYPTION_CLIENT=rejected I (obviously) get:

PS C:\Yannick\Python> python .\network_encryption.py
ORA-12660: Encryption or crypto-checksumming parameters incompatible

If I set nothing in sqlnet.ora file I get:

PS C:\Yannick\Python> python .\network_encryption.py
19.10.0.0.0
('TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production',)
('Encryption service for Linux: Version 19.0.0.0.0 - Production',)
('AES256 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production',)
('Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production',)

I can force the encryption algorithm with SQLNET.ENCRYPTION_TYPES_CLIENT=(3des168) and get:

PS C:\Yannick\Python> python .\network_encryption.py
19.10.0.0.0
('TCP/IP NT Protocol Adapter for Linux: Version 19.0.0.0.0 - Production',)
('Encryption service for Linux: Version 19.0.0.0.0 - Production',)
('3DES168 Encryption service adapter for Linux: Version 19.0.0.0.0 - Production',)
('Crypto-checksumming service for Linux: Version 19.0.0.0.0 - Production',)

Conclusion

All in all as the default value for SQLNET.ENCRYPTION_CLIENT is accepted if you configure your database server to only accept encrypted connection then it should be transparent from application side. At least it is for Java, Python and traditional SQL scripts…

If you really don’t want to touch your application code and choose your preferred encryption algorithm (in case default one, AES256, does not fit with you) you can even imagine limiting the available encryption algorithms from database server side with SQLNET.ENCRYPTION_TYPES_SERVER.

References

About Post Author

Yannick Jaquier

administrator

Find more about me on social media.

See author's posts

Share the knowledge!

Related Posts:

Leave a Reply

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

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>