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.

Este procedimiento requiere de conocimientos avanzados de Tomcat así como de Oracle

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