Establecer comunicación SSL entre Tomcat y Oracle 12.1.0 (usando docker)

El siguiente procedimiento está destinado a la configuración de una conexión segura entre Tomcat y Oracle 12.1.0 de forma que podamos garantizar la seguridad e integridad de las comunicaciones entre Tomcat y Oracle empleando canales seguros y cifrados en ambos extremos.

En primer lugar, debe configurarse correctamente el canal Oracle SSL en el listener. En este ejemplo usaremos el puerto #2484, pero se puede usar el que sea. sqlnet.ora debe configurarse con el protocolo de cifrado adecuado (SSL_CIPHER_SUITES) puesto que JAVA no acepta algunos protocolos por defecto de Oracle.

SQL_NET.AUTHENTICATION_SERVICES debe incluir el protocolo TCPS

Preparación

ORACLE_ID=$(docker run -d -p 1522:1521 -p 2484 -v /opt/oracle/data:/u01/app/oracle sath89/oracle-12c)
docker exec -it $ORACLE_ID bash
su - oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/EE/
export PATH=$PATH:$ORACLE_HOME/bin
cd $ORACLE_HOME
orapki wallet create -wallet /u01/app/oracle/wallet -auto_login -pwd Passw0rd
orapki wallet add -wallet /u01/app/oracle/wallet -dn CN=$(uname -n) -keysize 2048 -self_signed -validity 3650 -pwd Passw0rd
orapki wallet display -wallet /u01/app/oracle/wallet
cd $ORACLE_HOME/network/admin

Oracle

1.- Configuramos listener.ora de una manera apropiada para permitir conexiones ssl (TCPS)

listener.ora

WALLET_LOCATION =
  (SOURCE =
    (METHOD = FILE)
    (METHOD_DATA =
      (DIRECTORY = /u01/app/oracle/wallet)
    )
  )
 
SSL_CLIENT_AUTHENTICATION = FALSE
 
 
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCPS)(HOST = )(PORT = 2484))
    )
  )
ADR_BASE_LISTENER = /u01/app/oracle

2.- También el tnsnames.ora

tnsnames.ora

TEST =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = )(PORT = 1521))
  (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ))
 )
 
TESTSSSL =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCPS)(HOST = )(PORT = 2484)  )
   (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ))
   )

3.- A continuación el sqlnet.ora

sqlnet.ora

WALLET_LOCATION =
   (SOURCE =
     (METHOD = FILE)
     (METHOD_DATA =
       (DIRECTORY = /u01/app/oracle/wallet)
     )
   )
 
SQLNET.AUTHENTICATION_SERVICES = (TCPS,NTS,BEQ)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_RSA_WITH_AES_256_CBC_SHA)
 
SQLNET.WALLET_OVERRIDE = TRUE

4.- Una vez configurados estos 3 archivos, se debe reiniciar el listener. (lsnrctl stop ; lsnrctl start, y esperamos unos 30 segundos)

5.- Verificamos que responde correctamente

root@72795752816f:/u01/app/oracle/product/12.1.0/xe/network/admin# lsnrctl status
LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 23-JUN-2017 17:24:28
Copyright (c) 1991, 2014, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=72795752816f)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                21-JUN-2017 14:15:25
Uptime                    2 days 3 hr. 9 min. 2 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/xe/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/72795752816f/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=72795752816f)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=72795752816f)(PORT=2484)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=72795752816f)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "xe.oracle.docker" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
Service "xeXDB.oracle.docker" has 1 instance(s).
  Instance "xe", status READY, has 1 handler(s) for this service...
The command completed successfully

6.- Verificamos que podamos conectarnos con monedero (wallet) en modo simple y también en modo SSL

6.1.- Test tnsping

root@72795752816f:/u01/app/oracle/product/12.1.0/xe/network/admin# tnsping TEST
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-JUN-2017 17:14:54
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/xe/network/admin/sqlnet.ora
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 72795752816f)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xe.oracle.docker)))
OK (0 msec)
 
root@72795752816f:/u01/app/oracle/product/12.1.0/xe/network/admin# tnsping TESTSSL
TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 23-JUN-2017 17:14:56
Copyright (c) 1997, 2014, Oracle.  All rights reserved.
Used parameter files:
/u01/app/oracle/product/12.1.0/xe/network/admin/sqlnet.ora
 
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS) (HOST = 72795752816f) (PORT = 2484)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = xe.oracle.docker)))
OK (20 msec)

6.2.- Conectamos en plano

root@72795752816f:/u01/app/oracle/product/12.1.0/xe/network/admin# sqlplus system/oracle@TEST
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 23 17:17:54 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Fri Jun 23 2017 17:12:14 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
 
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
 
 
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcp

6.3.- Conectamos en modo SSL

root@72795752816f:/u01/app/oracle/product/12.1.0/xe/network/admin# sqlplus /@TESTSSL
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jun 23 17:17:54 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Fri Jun 23 2017 17:12:14 +00:00
Connected to:
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production
 
SQL> SELECT SYS_CONTEXT('USERENV', 'network_protocol') FROM DUAL;
 
SYS_CONTEXT('USERENV','NETWORK_PROTOCOL')
--------------------------------------------------------------------------------
tcps

Tomcat

1.- Incluimos los certificados SSL en Oracle

[root@e62e32a0e821 tmp]# echo "" | openssl s_client -connect :2484 -showcerts 2>/dev/null | openssl x509 -out /tmp/certfile.txt

2.- Descargamos el fichero Java Cryptography Extension (JCE) Unlimited Strength Jurisdiction Policy (Obligatorio si se usa Oracle v12 … quizás también con las últimas versiones de Oracle v11) de e instalamos

echo "Hacemos un backup de los ficheros actuales"
cp US_export_policy.jar $JAVA_HOME/lib/security/US_export_policy.jar
cp local_policy.jar $JAVA_HOME/lib/security/local_policy.jar
cp UnlimitedJCEPolicyJDK8/* $JAVA_HOME/lib/security

3.- Importamos en el trustrore de JAVA. Aquí podemos hacerlo de 2 maneras:

1.- Importando en el JAVA_HOME default trustStore

[root@e62e32a0e821 tmp]# cd $JAVA_HOME/lib/secutiry ; keytool -import -alias ca -file /tmp/certfile.txt -keystore cacerts -storepass changeit

2.-Importando en un nuevo trustStore

[root@e62e32a0e821 wallet]# cd $TOMCAT_WALLET_DIRECTORY ;
 
 
[root@e62e32a0e821 wallet]# keytool -import -file /tmp/certfile.txt -alias oraclev12  -keystore truststore.jks
Enter keystore password: 
Re-enter new password:
Owner: CN=72795752816f
Issuer: CN=72795752816f
Serial number: 0
Valid from: Tue Jun 20 10:08:09 UTC 2017 until: Fri Jun 18 10:08:09 UTC 2027
Certificate fingerprints:
     MD5:  DC:B8:01:5C:38:7B:DC:47:50:96:DC:21:D5:F5:48:60
     SHA1: D9:38:20:22:0C:25:9F:03:17:18:CA:12:16:0D:79:BB:24:5F:4D:05
     SHA256: 89:70:88:BA:83:A3:27:44:F8:A5:64:F9:BF:2C:FE:8C:01:80:29:12:45:94:35:75:EE:C6:D4:77:20:A1:D4:00
     Signature algorithm name: MD5withRSA
     Version: 1
Trust this certificate? [no]:  yes
Certificate was added to keystore

4.- Generamos un keystore

[root@e62e32a0e821 wallet]# keytool -keystore clientkeystore.jks -genkey -alias client
Enter keystore password: 
Re-enter new password:
What is your first and last name?
  [Unknown]: 
What is the name of your organizational unit?
  [Unknown]: 
What is the name of your organization?
  [Unknown]: 
What is the name of your City or Locality?
  [Unknown]: 
What is the name of your State or Province?
  [Unknown]: 
What is the two-letter country code for this unit?
  [Unknown]: 
Is CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown correct?
  [no]:  yes
 
 
Enter key password for 
    (RETURN if same as keystore password):

5.- Verificamos los datos del keystore

[root@e62e32a0e821 wallet]# keytool -list -v -keystore clientkeystore.jks
Enter keystore password: 
 
 
Keystore type: JKS
Keystore provider: SUN
 
 
Your keystore contains 1 entry
 
Alias name: client
Creation date: Jun 23, 2017
Entry type: PrivateKeyEntry
Certificate chain length: 1
Certificate[1]:
Owner: CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown
Issuer: CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown
Serial number: 602f2c40
Valid from: Fri Jun 23 16:28:51 UTC 2017 until: Thu Sep 21 16:28:51 UTC 2017
Certificate fingerprints:
     MD5:  19:F6:2E:65:00:76:97:14:85:8B:6F:30:3B:98:1F:80
     SHA1: 2E:99:B9:20:54:2F:26:A3:68:AB:C3:27:4E:50:20:EC:D8:68:15:AB
     SHA256: 71:01:B2:12:53:8E:CC:7A:05:CD:69:4F:23:CD:C7:0B:C1:86:F0:68:71:21:A2:0B:43:9E:1F:3D:9C:02:B6:9C
     Signature algorithm name: SHA1withDSA
     Version: 3
 
Extensions:
 
#1: ObjectId: 2.5.29.14 Criticality=false
SubjectKeyIdentifier [
KeyIdentifier [
0000: DA 20 DD 2D 8B 23 89 D1   A6 F7 B5 18 A1 54 FA AE  . .-.#.......T..
0010: 7D 60 44 42                                        .`DB
]
]
*******************************************
*******************************************

6.- Verificamos que tnsnames.ora de Tomcat contiene el canal TCPS

tnsnames.ora

TESTSSSL =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCPS)(HOST = )(PORT = 2484))
  (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ))
  )

7.- Agregamos los siguientes parámetros a Tomcat’s setenv.sh

setenv.sh

###############################
# DB CONNECTION CONFIGURATION #
###############################
# Oracle DB (JNDI)
CATALINA_OPTS+=" -Dspring.profiles.active=database-jndi "
CATALINA_OPTS+=" -Doracle.net.tns_admin=/tomcat/wallet "
CATALINA_OPTS+=" -Djavax.net.ssl.keyStore=/tomcat/wallet/keystore.jks "
CATALINA_OPTS+=" -Djavax.net.ssl.keyStoreType=JKS "
CATALINA_OPTS+=" -Djavax.net.ssl.keyStorePassword=Passw0rd  "
CATALINA_OPTS+=" -Djavax.net.ssl.trustStore=/tomcat/wallet/truststore.jks  "    # If we have used point #2.2 (If we have used JAVA default trustStore, comment this line out)
CATALINA_OPTS+=" -Djavax.net.ssl.trustStorePassword=Passw0rd  "                 # If we have used point #2.2 (If we have used JAVA default trustStore, comment this line out)
CATALINA_OPTS+=" -Doracle.net.authentication_services=TCPS   "
CATALINA_OPTS+=" -Doracle.net.ssl_cipher_suites=TLS_RSA_WITH_AES_256_CBC_SHA "  # Cipher protocol MUST match with the Oracle server

8.- El context.xml de Tomcat debería ser similar a

Troubleshooting

Podemos usar groovy http://groovy-lang.org/ si no queremos detener e iniciar Tomcat para probar los parámetros que vamos a pasar a Tomcat

1.- Generar un archivo test.groovy.

import java.security.Security;
import java.sql.*;
import java.io.FileInputStream;
import java.io.Console;
import java.util.Properties;
import java.util.Scanner;
 
import oracle.jdbc.*;
this.getClass().classLoader.rootLoader.addURL(new File("./lib/ojdbc8.jar").toURL())
 
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCPS)(HOST=<nombre del servidor Oracle/IP>)(PORT=2484))(CONNECT_DATA=(SERVICE_NAME=)))";
 
System.out.println("set properties");
Properties props = new Properties();
props.setProperty("user", "");
props.setProperty("password", "");
System.out.println("get connection");
Connection con = DriverManager.getConnection(url, props);
System.out.println("got a connection");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select sysdate from dual");
while (rs.next()) {
    System.out.println("result = "+rs.getString(1));
}
rs.close();
stmt.close();
con.close();

2.- Probamos el fichero groovy

[root@e62e32a0e821 bin]# /tomcat/groovy-2.4.11/bin/groovy -Djavax.net.debug=all  \
            -Djavax.net.ssl.keyStore=/tomcat/wallet/keystore.jks   
            -Djavax.net.ssl.keyStoreType=JKS \
            -Djavax.net.ssl.keyStorePassword=Passw0rd \
            -Djavax.net.ssl.trustStore=/tomcat/wallet/truststore.jks \
            -Djavax.net.ssl.trustStorePassword=Passw0rd \
            -Doracle.net.authentication_services=TCPS \ 
            -Dhttps.protocols=TLSv1.2 \
            -Dhttps.cipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA \
            /tomcat/test.groovy
 
set properties
get connection
***
found key for : tomcat
chain [0] = [
[
  Version: V3
  Subject: CN=Unknown, OU=Unknown, O=Unknown, L=Unknown, ST=Unknown, C=Unknown
  Signature Algorithm: SHA256withRSA, OID = 1.2.840.113549.1.1.11
 
 
.... (some time later) ...
 
 
[Raw write]: length = 37
0000: 15 03 01 00 20 43 38 DA   D4 24 6C C1 E7 8C A5 B9  .... C8..$l.....
0010: 4D 1C D2 20 07 B6 EF 5C   C8 F9 E6 59 29 0A 9A 92  M.. ...\...Y)...
0020: 45 6A 41 59 94                                     EjAY.
main, called closeSocket(true)

3.- Podemos eliminar el -Djavax.net.debug=all y probar de nuevo y veremos el resultado de la consulta en el archivo groovy:

(

ResultSet rs = stmt.executeQuery("select sysdate from dual");

)

[root@e62e32a0e821 bin]# /tomcat/groovy-2.4.11/bin/groovy \
            -Djavax.net.ssl.keyStore=/tomcat/wallet/keystore.jks   
            -Djavax.net.ssl.keyStoreType=JKS \
            -Djavax.net.ssl.keyStorePassword=Passw0rd \
            -Djavax.net.ssl.trustStore=/tomcat/wallet/truststore.jks \
            -Djavax.net.ssl.trustStorePassword=Passw0rd \
            -Doracle.net.authentication_services=TCPS \ 
            -Dhttps.protocols=TLSv1.2 \
            -Dhttps.cipherSuites=TLS_RSA_WITH_AES_128_CBC_SHA \
            /tomcat/test.groovy
 
set properties
get connection
got a connection
result = 2017-06-23 17:12:14.0