Configurar Autentificación SSL

He comprobado que hay bastante información sobre este tipo de configuraciones sin embargo he visto que esta algo confusa y a veces hay demasiada información. Para configurar una autentificación SSL de forma sencilla y rápida es necesario seguir los siguientes pasos. En primer lugar hay que distinguir la configuracion necesaria en el servidor y en el cliente.


1.- Creación de un wallet.

1.1.- Creación del wallet en el servidor.

Hay dos herramientas para crear un wallet, la herramienta gráfica es Oracle Wallet Manager (owm) y en modo línea de comando (orapki).

Para crear el wallet lo vamos a hacer con la herramienta orapki.

a) Creación del wallet en el servidor.

orapki wallet create -wallet . -auto_login -pwd password

b) Generar un certificado para el servidor.

orapki wallet add -wallet . -dn "CN=serverdb" -keysize1024 -self_signed -validity 3650 -pwd password

NOTA: ORACLE recomienda el uso de certificados auto firmados solo para pruebas, nunca para entornos de producción.

c) Exportar el certificado del servidor a un fichero.

orapki wallet export -wallet . -dn "CN=serverdb" -cert serverdb.cer

Una vez creado el wallet debemos comprobar que en el directorio donde vamos a ubicar los wallets se han creado los siguientes ficheros:

ewallet.p12 : Fichero de wallet
cwallet.sso : Fichero de wallet para permitir la auto conexión
serverdb.cer : Fichero del certificado auto firmado para el servidor

1.2.- Creación de un wallet para el cliente.

Es necesario seguir los mismos pasos que para el servidor. Hay que situarse en el directorio donde se van a ubicar los wallets, en este ejemplo C:\Documents and settings\usuario1\ORACLE\WALLETS

a) Creación del wallet en el cliente.

orapki wallet create -wallet . -auto_login -pwd password

b) Generar un certificado auto firmado para el cliente.

orapki wallet add -wallet . -dn "CN=usuario1" -keysize 1024 -self_signed -validity 3650 -pwd password

NOTA: Se debe crear el certificado para el usuario con el que nos conectamos en el cliente, es decir el usuario de Unix o el usuario Windows en cada caso.

c) Exportar el certificado a un fichero.

orapki wallet export -wallet . -dn "CN=usuario1" -cert usuario1.cer

Tendremos los siguientes ficheros en la carpeta de wallets:

ewallet.p12 : Fichero de wallet
cwallet.sso : Fichero de wallet para permitir la auto conexión
usuario1.cer : Fichero del certificado auto firmado para el usuario1 en el cliente

2.- Copiar los certificados.

El siguiente paso es copiar el certificado generado para el cliente al servidor y viceversa. La copia se puede hacer con scp o con copiar y pegar para cada uno de los ficheros.

3.- Importar los certificados en cada uno de los wallets.

Ahora es necesario importar el certificado generado en el cliente en el wallet del servidor, para esto vamos a usar la herramienta gráfica Oracle Wallet Manager.

Arrancamos el owm en el servidor, abrimos el wallet, es necesario introducir la password que dimos en el punto 1 al crear el wallet.

En la pestaña "Operaciones" pinchamos en "Importar Certificado Protegido". Marcamos la opción "Seleccionar un archivo que contenga el certificado", pinchamos en Aceptar, nos muestra una ventana de busqueda para localizar el certificado generado para el cliente y poder seleccionarlo. En este ejemplo es el fichero usuario1.cer, una vez seleccionado aparece en la lista de certificados protegidos.

A continuación repetimos el proceso para importar en el cliente el certificado generado en el servidor. Se debe ejecutar el owm en el cliente e importar el fichero serverdb.cer.

4.- Crear un usuario de base de datos.

En el servidor de base de datos se crea un usuario de la siguiente forma:

SQL> create user usuario1 identified externally as 'CN=usuario1';

SQL> grant create session to usuario1;

NOTA: Para versiones 10.1 o anteriores el usuario se crea con :

SQL> create user usuario1 identified globally as 'CN=usuario1';

en caso contrario nos puede dar el error ORA-01017: invalid username/password; logon denied.

5.- Configuración de ficheros de Oracle.

5.1.- Configuración de ficheros del servidor.

a) Fichero LISTENER.ora

Se añade un nuevo servicio de Listener con protocolo TCPS y puerto 2484.

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = Servidor)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCPS)(HOST = Servidor)(PORT = 2484))
)
)

#listener uses the same wallet that database uses

wallet_location =
(SOURCE=
(METHOD=File)
(METHOD_DATA= (DIRECTORY="C:\Documents and Settings\Servidor\ORACLE\WALLETS") )
)

#This parameter should be false as listener is not going to authenticate the clients.
# It is the server process that authenticates the clients.

SSL_CLIENT_AUTHENTICATION = FALSE

b) Fichero SQLNET.ora

SQLNET.AUTHENTICATION_SERVICES= (NTS,BEQ,TCPS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

# Specify the server wallet location

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = "C:\Documents and Settings\Servidor\ORACLE\WALLETS")
)
)

SSL_CLIENT_AUTHENTICATION=TRUE

5.2.- Ficheros de configuración en el cliente.

a) Fichero TNSNAMES.ora

Se añade un nuevo servicio en el fichero TNSNAMES.ora

serverdbSSL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCPS)(HOST = Servidor)(PORT = 2484))
)
(CONNECT_DATA =
(SERVICE_NAME = serverdb)
)
(SECURITY=
(SSL_SERVER_CERT_DN="CN=serverdb")
)

b) Fichero SQLNET.ora

SQLNET.AUTHENTICATION_SERVICES= (TCPS,NTS,BEQ)

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT, ONAMES, HOSTNAME)

#Specify the client wallet location

WALLET_LOCATION =
(SOURCE =
(METHOD = FILE)
(METHOD_DATA =
(DIRECTORY = "C:\Documents and ettings\usuario1\ORACLE\WALLETS")
)
)

#Set this parameter ON so that SSL_SERVER_CERT_DN in tnsnames.ora is effective

SSL_SERVER_DN_MATCH=TRUE

SSL_CLIENT_AUTHENTICATION=FALSE

6.- Pruebas de funcionamiento.

Por último, vamos a comprobar que la configuración funciona correctamente.

Desde el cliente nos conectamos al servidor.

SQL> connect system/password@serverdbSSL
Connected

SQL> show user
USER es "SYSTEM"

Para comprobar el protocolo que estamos usando:

SQL> select sys_context('USERENV','NETWORK_PROTOCOL') protocol from dual;

PROTOCOL
-------------

tcps

Para conectarse con el usuario que ha creado el certificado no es necesario usar password.

SQL> connect /@serverdbSSL
Connected

SQL> show user
USER es "usuario1"


LINKS

Step by Step Guide To Configure SSL Authentication [ID 736510.1]

Oracle Authentication with Certificates. Gracias a Fabrizio Magni

Instalación de Ruby-Oci8 con Oracle

Para instalar ruby-oci8 en un servidor Linux con Oracle 10.2.0.4 es necesario seguir los siguientes pasos:

1.- Debe estar instalado previamente en el servidor el paquete de ruby y las gemas que se necesiten.

Para comprobar la instalación de ruby:

# ruby -v
ruby 1.8.6 (2008-03-03 patchlevel 114) [x86_64-linux]

Para comprobar la instalacion de las gemas:

# gem -v
1.3.0

2.- Instalación del adaptador de Oracle:

gem install activerecord-oracle-adapter --source http://gems.rubyonrails.org

3.- Variables de entorno de Oracle
Antes de instalar el driver de ruby-oci8 es recomendable poner las siguientes variables de entorno:

export ORACLE_SID=ORCL
export PATH=$PATH:/home/oracle/product/10.2/db_1/bin
export ORACLE_HOME=/home/oracle/product/10.2/db_1
export TNS_ADMIN=/home/oracle/product/10.2/db_1/network/admin
export LD_LIBRARY_PATH=/home/oracle/product/10.2/db_1/lib
export NLS_LANG=american_america.AL32UTF8

4.- Instalación de ruby-oci8.

Bajar la última versión ruby-oci8-1.0.3.tar.gz desde: http://rubyforge.org
una vez descomprimido el fichero es necesario ejecutar:

# ruby setup.rb config

# make

# make install

5.- Configuración del fichero tnsnames.ora.

Añadir una entrada en el fichero tnsnames para el servicio ORCL.

ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.10)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORCL)
)
)

6.- Comprobar que ruby-oci8 funciona correctamente.

Para comprobar el funcionamiento de ruby-oci8 se puede ejecutar el siguiente comando:

ruby -roci8 -e "OCI8.new('scott', 'tiger', 'ORCL').exec('select * from locales') do |r| puts r.join(' | '); end"

Si todo ha ido bien mostrará los datos de la tabla indicada.

7.- Modificar el fichero database.yml para configurar la conexión de rails a la base de datos.

   production:
     adapter: oracle
     database: ORCL
     username: scott
     password: tiger
     host: 192.168.1.10
     encoding: utf8

   development:
     adapter: oracle
     database: ORCL
     username: scott
     password: tiger
     host: 192.168.1.10
     encoding: utf8

8.- Arrancar el WEBrick de rails para comprobar que conecta bien.

Para probar la conexión se arranca el WEBrick de rails:

#RAILS_ENV=production ruby /home/rails/script/server

Es posible que al arrancarlo nos de el siguiente error:

/usr/lib64/ruby/site_ruby/1.8/rubygems.rb:147:in `activate': can't activate activerecord (>= 1.15.5.7843, runtime), 

already activate activerecord-2.1.0 (Gem::Exception)

En este caso es necesario modificar el fichero gemspec del oracle-adapter.

En el ejemplo, el fichero es el siguiente:

/usr/lib64/ruby/gems/1.8/specifications/activerecord-oracle-adapter-1.0.0.9250.gemspec

Hay que comentar las últimas líneas del fichero, quedará así:

# -*- encoding: utf-8 -*-

Gem::Specification.new do |s|
s.name = %q{activerecord-oracle-adapter}
s.version = "1.0.0.9250"

s.required_rubygems_version = Gem::Requirement.new(">= 0") if s.respond_to? :required_rubygems_version=
s.authors = ["Michael Schoen"]
s.date = %q{2008-06-04}
s.email = %q{schoenm@earthlink.net}
s.files = ["lib/active_record/connection_adapters/oracle_adapter.rb"]
s.homepage = %q{http://ruby-oci8.rubyforge.org/}
s.require_paths = ["lib"]
s.rubyforge_project = %q{activerecord}
s.rubygems_version = %q{1.3.0}
s.summary = %q{Oracle adapter for Active Record}

# if s.respond_to? :specification_version then
# current_version = Gem::Specification::CURRENT_SPECIFICATION_VERSION
# s.specification_version = 2
#
# if Gem::Version.new(Gem::RubyGemsVersion) >= Gem::Version.new('1.2.0') then
# s.add_runtime_dependency(%q, [">= 1.15.5.7843"])
# else
# s.add_dependency(%q, [">= 1.15.5.7843"])
# end
# else
# s.add_dependency(%q, [">= 1.15.5.7843"])
# end
end

Después de esto el WEBrick arrancará sin errores.

Links.

RubyForge
Jesse Hu's Weblog
Ruby-Oci8

Borrar alertas obsoletas en OEM

En algunas ocasiones el OEM mantiene una serie de alertas obsoletas en el aparatado Alerts de la página principal de Database Control.

Además esto provoca una perdida de rendimiento en las consultas que Oracle hace internamente sobre las tablas MGMT.

Para eliminar estas alertas obsoletas es necesario ejecutar el paquete EM_SEVERITY.DELETE_CURRENT_SEVERITY.

Se pueden seguir estos pasos:

1.- Identificar las alertas que se quieren borrar con:

select target_guid , metric_guid , key_value from MGMT_CURRENT_SEVERITY;

2.- Conectado con el usuario SYSMAN de la base de datos ejecutar el siguiente procedimiento:

exec EM_SEVERITY.DELETE_CURRENT_SEVERITY (target_guid , metric_guid , key_value);

Por ejemplo:

exec EM_SEVERITY.delete_current_severity

('0DEB8E6980695B7548CF98871084AD10',

'F95BA0D95585002889E1ABF92B2DA7C3',

'SYS');

Se pueden obtener todas las alertas obsoletas ya preparadas para ser borradas con esta consulta:

CONN SYSMAN

SELECT 'EXEC EM_SEVERITY.DELETE_CURRENT_SEVERITY

('''||TARGET_GUID||''','''||METRIC_GUID||''',''' ||KEY_VALUE||''');' MCS

FROM MGMT_CURRENT_SEVERITY;


Con esto se eliminan las alertas que ya han caducado y además se mejora el rendimiento en los accesos a las tablas de sysman MGMT.

Monitorización de Sesiones y Servicios en un entorno RAC

El comando por defecto para monitorizar los servicios de un entorno RAC es crs_stat -t, en metalink hay un script para formatear el resultado de una forma más vistosa y además se puede añadir una consulta para que muestre también el número de sesiones que hay en cada nodo del RAC y así comprobar si las sesiones se están balanceando de forma adecuada.

El script es muy sencillo y sería algo así:

#!/bin/ksh
# Monitorizacion de sesiones y servicios
#
##############################################
#
clear
# Variables de entorno
ORACLE_BASE=/opt/oracle/u01/app/oracle
ORACLE_HOME=/opt/oracle/u01/app/oracle/product/10.2/db_1
export ORACLE_BASE ORACLE_HOME

export ORACLE_SID=ORCL1

/opt/oracle/u01/app/oracle/product/10.2/db_1/bin/sqlplus -s "/ as sysdba" <<>
select inst_id , count(*) as sessions from gv\$session group by inst_id;
END


/opt/oracle/u01/app/oracle/scripts/crsstat.sh


El script crststat.sh se puede conseguir en Metalink buscando la nota, Note:259301.1

El resultado sería el siguiente:

   INST_ID   SESSIONS
---------- ----------
         1         82
         2         79

HA Resource                      Target     State
-----------                      ------     -----
ora.ORCL.ORCL1.inst              ONLINE     ONLINE on nodo1
ora.ORCL.ORCL2.inst              ONLINE     ONLINE on nodo2
ora.ORCL.ORCLS.ORCL1.srv         ONLINE     ONLINE on nodo1
ora.ORCL.ORCLS.ORCL2.srv         ONLINE     ONLINE on nodo2
ora.ORCL.ORCLS.cs                ONLINE     ONLINE on nodo1
ora.ORCL.db                      ONLINE     ONLINE on nodo2
ora.nodo1.ASM1.asm               ONLINE     ONLINE on nodo1
ora.nodo1.LISTENER_NODO1.lsnr    ONLINE     ONLINE on nodo1
ora.nodo1.gsd                    ONLINE     ONLINE on nodo1
ora.nodo1.ons                    ONLINE     ONLINE on nodo1
ora.nodo1.vip                    ONLINE     ONLINE on nodo1
ora.nodo2.ASM2.asm               ONLINE     ONLINE on nodo2
ora.nodo2.LISTENER_NODO2.lsnr    ONLINE     ONLINE on nodo2
ora.nodo2.gsd                    ONLINE     ONLINE on nodo2
ora.nodo2.ons                    ONLINE     ONLINE on nodo2
ora.nodo2.vip                    ONLINE     ONLINE on nodo2

Subconsulta con varias filas y un operador "like".

A veces en una consulta es necesario comprobar que un campo de una tabla contenga una cadena que queremos obtener de una o varias filas resultado de ejecutar una subconsulta.

Para resolver esta situación se puede usar una consulta de este tipo:

select a.fecha ,a.texto , b.cadena
from tabla_A a , tabla_B b
where upper(a.texto) like '%'||b.cadena||'%'
and b.cadena in (select cadena from tabla_B where id='1001')
and a.fecha>'01/01/08'
order by a.fecha;

Esta consulta muestra el campo texto que contenga las cadenas obtenidas en la subconsulta y que cumplan las condiciones indicadas.

Conexiones Oracle a traves de túneles SSH

El problema aparece cuando queremos conectar dos servidores Oracle sobre Linux en dos redes distintas y que no están accesibles desde internet. El único nexo que tenemos es un tercer servidor de gestión que tiene acceso a los dos servidores, pero no hay conexión directa entre los dos servidores de bases de datos.

Una solución para no abrir puertos y no romper las reglas de seguridad es abrir una serie de túneles con ssh para establecer una conexión segura entre los dos servidores.

El esquema siguiente nos muestra la arquitectura que tenemos:

En este caso es necesario abrir dos túneles, uno desde el Servidor B al Servidor de Gestión y otro desde el Servidor de Gestión al Servidor A. Con esto hemos establecido una conexión directa y segura entre el Servidor A y el Servidor B.

Los pasos para crear los túneles son los siguientes:

1.- Crear tunel del Servidor B al de Gestión.

Para crear este tunel hay que ejecutar el siguiente comando conectado al Servidor B:

ssh -R 1521:localhost:1590 usuario@servidor_de_gestion

2.- Crear tunel entre el Servidor de Gestión y el Servidor A.

Conectado en el Servidor de Gestión, se crea con:

ssh -R 1521:localhost:1590 usuario@servidor_a

3.- Configuracion del fichero TNSNAMES.ora

La configuración del fichero TNSNAMES.ora en el Servidor A debe ser como esta:

ORCLB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)
(HOST = localhost)
(Port = 1521)
)
(CONNECT_DATA =
(SERVICE_NAME = ORCLB)
)
)

4.- Crear database link.

Por último, se puede crear un database link para acceder desde la base de datos del Servidor A a la base de datos del Servidor B.

create public database link ORCLB
connect to scott identified by tiger
using '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(Port = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCLB)))';

Es necesario poner la cadena de conexión completa puesto que si se pone el nombre del servicio definido en el fichero TNSNAMES.ora da un error:

TNS-12154 TNS:could not resolve service name.

Con esto ya se puede acceder desde el Servidor A al B utilizando un database link. El problema es que debemos mantener las conexiones ssh abiertas mientras queramos usar el database link, es decir, solo estará disponible mientras estén abietos los túneles definidos.

Links.
Túneles SSH (Gracias a Tolgalen)
Secure Remote Logins

.

Configurar componentes de la base de datos

En una instalación con Oracle Standard Edition 10.2.0.4., cuando se creo la base de datos no se configuró ninguno de los componentes estandar que se pueden instalar. Estos componentes son:
- Oracle JVM
- Oracle XML DB
- Oracle Intermedia

Oracle recomienda que siempre se instalen estos componentes en la base de datos.

Para el desarrollo de una nueva aplicacion que usa geoposicionamiento ha sido necesario instalar Oracle Locator. El Oracle Locator se instala automáticamente al instalar el Oracle Intermedia, pero para instalar Intermedia es necesario tener instalado previamente Oracle JVM y XML DB.

Para instalar estos componentes se puede hacer desde la herramienta gráfica dbca o bien hacerlo manualmente siguiendo estos pasos:

1.- Instalación de Oracle JVM.

Para comprobar si Oracle JVM esta instalado se debe ejecutar la siguiente consulta:

select comp_id,comp_name,status,version from dba_registry;

Esta consulta muestra los siguientes componentes:

COMP_ID COMP_NAME STATUS VERSION
------- -------------------------------- ------ ----------
OWM Oracle Workspace Manager VALID 10.2.0.4.3
EM Oracle Enterprise Manager VALID 10.2.0.4.0
CATALOG Oracle Database Catalog Views VALID 10.2.0.4.0
CATPROC Oracle Database Packages and Types VALID 10.2.0.4.0
RAC Oracle Real Application Clusters VALID 10.2.0.4.0

No aparece ninguna referencia a JAVAVM.

Para instalar el componente es necesario ejecutar:

spool full_jvminst.log;
set echo on
connect / as sysdba
startup mount
alter system set "_system_trig_enabled" = false scope=memory;
alter database open;
select obj#, name from obj$
where type#=28 or type#=29 or type#=30 or namespace=32;
@?/javavm/install/initjvm.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/initxml.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/xdk/admin/xmlja.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catjava.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
@?/rdbms/admin/catexf.sql
select count(*), object_type from all_objects
where object_type like '%JAVA%' group by object_type;
shutdown immediate
set echo off
spool off
exit

Para compilar los objetos que hayan quedado inválidos:

@?/rdbms/admin/utlrp.sql

A continuación se puede comprobar los objetos java creados:

select owner, count(*) from all_objects
where object_type like '%JAVA%'
group by owner;

2.- Instalación de Oracle XML DB.

Conectado como sysdba hay que ejecutar:

$ORACLE_HOME/rdbms/admin/catqm.sql

es necesario pasar tres parámetros:

- password para el usuario XDB
- tablespace por defecto del usuario XDB
- tablespace temporal del usuario XDB

por ejemplo:

$ORACLE_HOME/rdbms/admin/catqm.sql XDB SYSAUX TEMP

Para comprobar que la instalación ha sido correcta:

select comp_name, status, version from DBA_REGISTRY
where comp_name= 'Oracle XML Database' ;

3.- Instalación de Oracle Intermedia.

Conectado como sysdba, se ejecuta:

@?/ord/admin/ordinst.sql SYSAUX SYSAUX

Para crear el usuario MDSYS y asignar los permisos adecuados.

A continuación se crean los paquetes y tipos:

@?/ord/im/admin/iminst.sql

4.- Comprobación de componentes instalados en la base de datos.

Para comprobar que todos los componentes están instalados correctamente:

select comp_id,comp_name,status,version from dba_registry;

COMP_ID COMP_NAME STATUS VERSION
-------------- --------------------------- ------ ------------
ORDIM Oracle interMedia VALID 10.2.0.4.
0XDB Oracle XML Database VALID 10.2.0.4.0
EXF Oracle Expression Filter VALID 10.2.0.4.0
OWM Oracle Workspace Manager VALID 10.2.0.4.3
EM Oracle Enterprise Manager VALID 10.2.0.4.0
CATALOG Oracle Database Catalog Views VALID 10.2.0.4.0
CATPROC Oracle Database Packages and Types VALID 10.2.0.4.0
RAC Oracle Real Application Clusters VALID 10.2.0.4.0
JAVAVM JServer JAVA Virtual Machine VALID 10.2.0.4.0
XML Oracle XDK VALID 10.2.0.4.0
CATJAVA Oracle Database Java Packages VALID 10.2.0.4.0

Al estar instalado el Oracle Intermedia ya se pueden usar los paquetes de Oracle Locator.

Notas de Metalink.

Note:472937.1 Information On Installed Components and Schemas
Note:276554.1 How to Reload the JVM in 10.1.0.X and 10.2.0.X
Note:243554.1 How to Deinstall and Reinstall XML Database (XDB)

Oracle® interMedia User's Guide - Installing and Upgrading Oracle interMedia