#1 - Crear un directorio para el wallet especifico para el SSL
Oracle recomienda usar wallet separados en caso de que se este usando el SSL mas el TDE.
mkdir /u01/app/oracle/product/11.2.0.3/dbhome/network/wallet_ssl
En caso de ambientes RAC es necesario recrear el directorio en cada nodo en caso de que no se cuente con un sistema para compartir el archivo. (como OCFS2 por ejemplo)
#2 - Editar los archivos listener.ora y sqlnet.ora para a#adirle los parametros WALLET_LOCATION and SSL_CLIENT_AUTHENTICATION y SSL_CIPHER_SUITES
WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/product/11.2.0.3/dbhome/network/wallet_ssl)
)
)
SSL_CLIENT_AUTHENTICATION = FALSE
SSL_CIPHER_SUITES = (SSL_DH_anon_WITH_3DES_EDE_CBC_SHA,SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA,SSL_RSA_EXPORT_WITH_RC4_40_MD5,SSL_RSA_EXPORT_WITH_DES40_CBC_SHA,SSL_RSA_WITH_3DES_EDE_CBC_SHA,SSL_RSA_WITH_RC4_128_SHA,SSL_RSA_WITH_RC4_128_MD5,SSL_RSA_WITH_DES_CBC_SHA)
El valor por defecto del parametro SSL_CLIENT_AUTHENTICATION es TRUE. Este valor esta en FALSE por que queremos que sea el motor quien haga la autenticacion del usuario.
El valor potr defecto del parametro SSL_CIPHER_SUITES is SSL_RSA_WITH_RC4_128_MD5. Este parametro contiene una lista de cipher suites ya que la idea es permitir conexiones de clientes
con autenticacion y de manera anonima.
#3 - Editar el archivo sqlnet.ora a#adirle el parametro ENCRYPTION_WALLET_LOCATION and SSL_VERSION
ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/tde)
)
)
La variable de ambiente $ORACLE_UNQNAME es usada para indicarle al TDE que base de datos va a usar esta caracteristica y aque wallet tiene que acceder.
#4 - Creacion del wallet para el SSL
NOTA IMPORTANTE: todos estos comando se ejecutan del lado del servidor.
Para este punto hay un par de consideraciones:
#4.1 - Que el certificado pueda ser auto firmado (self_signed).
Server
======
cd /u01/app/oracle/product/11.2.0.3/dbhome/network/wallet_ssl
- orapki wallet create -wallet . -auto_login -pwd Welcome1
- orapki wallet remove -trusted_cert_all -wallet . -pwd Welcome1
orapki wallet add -wallet . -dn "
For the creation of the certificate name we have this options:
CN - Common Name of the certificate owner
OU - Organizational Unit of the certificate owner
O - Organization to which the certificate owner belongs
L - Locality name of the certificate owner
S - State or province of the certificate owner
C - Country of the certificate owne
example: CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US
- orapki wallet add -wallet . -dn "CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -keysize 4096 -self_signedvalidity 365 -pwd Welcome1
- orapki wallet export -wallet . -dn "CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -cert dbserver_ca.cert
- orapki wallet display -wallet . -summary -pwd Welcome1
example:
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US
Trusted Certificates:
Subject: CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US Importante
#4.2 - Que el certificado NO pueda ser auto firmado. En este caso es necesario crear un CSR (Certificate Service Request) para que sea firmado por un tercero.
Server
======
cd /u01/app/oracle/product/11.2.0.3/dbhome/network/wallet_ssl
- orapki wallet create -wallet . -auto_login -pwd Welcome1
- orapki wallet remove -trusted_cert_all -wallet . -pwd Welcome1
orapki wallet add -wallet . -dn "
For the creation of the certificate name we have this options:
CN - Common Name of the certificate owner
OU - Organizational Unit of the certificate owner
O - Organization to which the certificate owner belongs
L - Locality name of the certificate owner
S - State or province of the certificate owner
C - Country of the certificate owne
example: CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US
- orapki wallet add -wallet . -dn "CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -keysize 4096 -validity 365 -pwd Welcome1
- orapki wallet export -wallet . -dn "CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -request dbserver_ca.creq -pwd Welcome1
Una vez creado el CA lo importamos a nuestro wallet
- orapki wallet add -wallet . -trusted_cert -cert dbserver_ca.cert -pwd Welcome1
- orapki wallet add -wallet . -user_cert -cert dbserver_ca.cert -pwd Welcome1
A manera de ejericio se puede ver en este otro de mis articulos como firmar un certificado con OpemSSL
- orapki wallet display -wallet . -summary -pwd Welcome1
example:
Oracle PKI Tool : Version 11.2.0.3.0 - Production
Copyright (c) 2004, 2011, Oracle and/or its affiliates. All rights reserved.
Requested Certificates:
User Certificates:
Subject: CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US
Trusted Certificates:
Subject: CN=CODO,O=CODO,L=Salford,ST=Greater Manchester,C=GB Importante
Este caso se observa en el wallet la (o las firmas) de la autoridad (CA).
#5 - Creacion del wallet para el TDE
export $ORACLE_UNQNAME=dbtest
cd /u01/app/oracle/admin/$ORACLE_UNQNAME/wallet/tde
sqlplus / as sysdba
SQL> ALTER SYSTEM SET ENCRYPTION KEY AUTHENTICATED BY "Passwor1";
Esto crea el archivo ewallet.p12, nuestro wallet para el TDE.
Creacion del auto login para el wallet (cwallet.sso)
- orapki wallet create -wallet . -auto_login -pwd Passwor1
#6 - Preparacion del cliente.
NOTA IMPORTANTE: todos estos comando se ejecutan del lado del cliente.
Nuevamente, para este punto hay un par de consideraciones:
#6.1 - Que el cliente tenga instalado el software de Oracle.
Si el cliente que va a usar la BD tiene el software de Oracle solo hay que crear el wallet y intercambiar los certificados:
Client
======
- orapki wallet create -wallet . -auto_login -pwd Welcome2
- orapki wallet add -wallet . -dn "CN=cliente,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -keysize 512 -self_signed -validity 365 -pwd Welcome2
- orapki wallet export -wallet . -dn "CN=cliente,OU=databse,O=DHS,L=StPaul,ST=MN,C=US" -cert client_ca.cert
- orapki wallet add -wallet . -trusted_cert -cert dbserver_ca.cert -pwd Welcome2
Del lado del servidor se debe importar el certificado del cliente a find e que se pueda establecer la relacion de confianza.
Server
======
- orapki wallet add -wallet . -trusted_cert -cert client_ca.cert -pwd Welcome1
#6.2 - Que el cliente tenga NO instalado el software de Oracle y la instalacion no se pueda realizar.
Para este caso se utiliza un contenedor diferente al wallet de Oracle, se una el Java KeyStore (JKS).
Server
======
- keytool -genkey -alias server -dname "CN=juanudo,OU=databse,O=UDO,L=StPaul,ST=MN,C=US" -storetype JKS -keystore server.jks -keyalg RSA -storepass Welcome1
- keytool -export -file server_ca.cert -keystore server.jks -storepass Welcome1 -alias server
- keytool -list -keystore server.jks -storepass 'Welcome1' -v
Client
======
- keytool -genkey -alias client -dname "CN=cliente,OU=databse,O=DHS,L=StPaul,ST=MN,C=US" -storetype JKS -keystore client.jks -keyalg RSA -storepass Welcome2
- keytool -export -file client_ca.cert -keystore client.jks -storepass Welcome2 -alias client
- keytool -list -keystore server.jks -storepass 'Welcome2' -v
Se intercambian los certificados y se importan respectivamente:
- keytool -import -file client_ca.cert -keystore server.jks -storepass Welcome1 -alias client
- keytool -import -file server_ca.cert -keystore client.jks -storepass Welcome2 -alias server
#7 - Conexion via SQL*NET y prueba de configuraciones SSL y TDE.
A#adimos al tnsnames.ora nuestra cadena de conexiones TCPS.
dbtest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2484))
(CONNECT_DATA =
(SERVICE_NAME = dbtest)
)
)
tnsping dbtest
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 26-AUG-2013 14:37:50
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0.3/dbhome/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCPS)(HOST = localhost)(PORT = 2484)) (CONNECT_DATA = (SERVICE_NAME = dbtest)))
OK (30 msec)
sqlplus system@dbtest
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 26 14:39:36 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Enter password:
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
Con esto garantizamos que la configracion para el uso del SSL quedo correctamente configurada y que podemos conectarnos via SQL*NET.
Para probar la configuracion del TDE solo debemos create un tablespce que use algun tipo de algoritmo:
sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon Aug 26 14:47:24 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
SQL> create tablespace test EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO DEFAULT STORAGE(ENCRYPT) ENCRYPTION USING 'AES256';
Tablespace created.
SQL> drop tablespace test;
Tablespace dropped.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options
#8 - Conexion via JDBC.
Si necesitamos probar la conexion via JDBC aqui esta un ejemplo:
import java.sql.*;
import java.security.Security;
import java.util.Properties;
public class TestSSL {
public static void main(String[] argv) throws Exception {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
Properties prop = new Properties();
Security.addProvider(new oracle.security.pki.OraclePKIProvider());
prop.setProperty("oracle.net.authentication_services","(TCPS)");
//prop.setProperty("oracle.net.wallet_location", "(SOURCE=(METHOD=file)(METHOD_DATA=(DIRECTORY=C:\\Users\\Downloads\\ssl-jdbc-demos)))");
String url = "jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=localhost)(PORT=2484))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=dbtest)))";
//CASE #0: JUST CONNECTION
prop.setProperty("user", "system");
prop.setProperty("password", "oracle");
CASE #1: USE SSL FOR ENCRYPTION ONLY
/*
prop.setProperty("oracle.net.ssl_client_authentication", "false");
prop.setProperty("user", "system");
prop.setProperty("password", "oracle");
prop.setProperty("oracle.net.ssl_cipher_suites","(SSL_DH_anon_WITH_3DES_EDE_CBC_SHA, SSL_DH_anon_WITH_RC4_128_MD5,SSL_DH_anon_WITH_DES_CBC_SHA)");
*/
//CASE #2: USE SSL FOR ENCRYPTION AND SERVER AUTHENTICATION
/*
prop.setProperty("javax.net.ssl.trustStore","C:\\Users\\Downloads\\ssl-jdbc-demos\\ewallet.p12");
prop.setProperty("javax.net.ssl.trustStoreType","PKCS12");
prop.setProperty("javax.net.ssl.trustStorePassword","Welcome2");
*/
//CASE #3: USE SSL FOR ENCRYPTION AND AUTHENTICATION OF BOTH TIERS
/*
prop.setProperty("javax.net.ssl.trustStore","C:\\Users\\Downloads\\ssl-jdbc-demos\\ewallet.p12");
prop.setProperty("javax.net.ssl.trustStoreType","PKCS12");
prop.setProperty("javax.net.ssl.trustStorePassword","Welcome2");
*/
/*
//---------------------------------- JKS --------------------------------------------------------------------
prop.setProperty("javax.net.ssl.keyStore","C:\\Users\\Downloads\\ssl-jdbc-demos\\ewallet.jks");
prop.setProperty("javax.net.ssl.keyStoreType","JKS");
prop.setProperty("javax.net.ssl.keyStorePassword","Welcome2");
prop.setProperty("javax.net.ssl.trustStore","C:\\Users\\Downloads\\ssl-jdbc-demos\\ewallet.jks");
prop.setProperty("javax.net.ssl.trustStoreType","JKS");
prop.setProperty("javax.net.ssl.trustStorePassword","Welcome2");
//-----------------------------------------------------------------------------------------------------------
*/
//CASE #4: USE SSL AS AN AUTHENTICATION SERVICE IN THE DATABASE
/*
prop.setProperty("javax.net.ssl.trustStore","C:\\java\\cwallet.sso");
prop.setProperty("javax.net.ssl.trustStoreType","SSO");
prop.setProperty("javax.net.ssl.Store","C:\\java\\cwallet.sso");
prop.setProperty("javax.net.ssl.StoreType","SSO");
*/
Connection conn = DriverManager.getConnection(url,prop);
DatabaseMetaData dbmd = conn.getMetaData();
// Print out all loaded JDBC drivers.
java.util.Enumeration e = java.sql.DriverManager.getDrivers();
while (e.hasMoreElements()) {
Object driverAsObject = e.nextElement();
System.out.println("JDBC Driver=" + driverAsObject);
}
System.out.println("===== Driver info =====");
System.out.println("DriverName: " + dbmd.getDriverName() );
System.out.println("DriverVersion: " + dbmd.getDriverVersion() );
System.out.println("DriverMajorVersion: " + dbmd.getDriverMajorVersion() );
System.out.println("DriverMinorVersion: " + dbmd.getDriverMinorVersion() );
Statement stmt = conn.createStatement();
ResultSet rset = stmt.executeQuery("select 'Hello Thin driver SSL "+ "tester '||USER||'!' result from dual");
while (rset.next())
System.out.println(rset.getString(1));
rset.close();
stmt.close();
conn.close();
}
}
Todos los casos representados en el programa (excepto el 0 y el ejemplo con JKS) provienen de un paper de nombre SSL With Oracle JDBC Thin Driver de Jean de Lavarene
