Un Maracucho en Madrid

Integración de Oracle con Fuentes de Datos ODBC

2012-08-06T11:59:00.002+02:00

En este artículo se explica como integrar una fuente de datos ODBC con Oracle, es decir, si tenemos una base de datos MySQL, Firebird, o cualquiera sobre la que podamos crear una fuente de datos ODBC, poder consultarla desde dentro de una conexión a una base de datos Oracle, pudiendo incluso hacer "joins" entre las tablas de ambas bases de datos.

Para vincular las bases de datos me basé un poco en lo especificado en:

Se asume que el Oracle ha sido instalado en la carpeta: C:\Oracle\ con el usuario Administrator, de esta forma la mayoría de los ficheros se encontrarán en alguna subcarpeta de: C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1\

A nivel general los pasos son:

Modificar ficheros de configuración de Oracle

Archivo de inicialización
listener.ora
tnsnames.ora

Reiniciar LISTENER y probar conexión
Crear vínculo y SQL de prueba
Vistas Materializadas

Modificar ficheros de configuración de Oracle

En los siguientes pasos si hay problemas con crear o modificar archivos, dar permiso de escritura desde Windows, sobre los ficheros a los "Usuarios Autenticados".

Archivo de inicialización

Debemos crear dentro de la carpeta: C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1\hs\admin\

Un fichero de texto con extensión ".ora", para este ejemplo lo llamaremos: initODBC.ora

El contenido será:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = FUENTEODBC # <-- es el nombre del DSN o fuente ODBC que hemos creado previamente en Windows
HS_FDS_TRACE_LEVEL = OFF

#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

Listener

Aquí configuramos el "escuchador" de Oracle para que atienda las peticiones a la fuente de datos que queremos conectar.

Se edita el fichero:

C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\listener.ora

Y agregamos después de:

SID_LIST_LISTENER =
(SID_LIST =

Esto:

    (SID_DESC =
      (SID_NAME = FUENTEODBC) # <-- es el nombre del DSN o fuente ODBC que hemos creado previamente en Windows)
      (ORACLE_HOME = C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1)
      (PROGRAM = dg4odbc)
      )

tnsnames.ora

Aquí se configura el servicio por el cual se proporcionará la conexión a la fuente de datos:

Se edita el fichero:

C:\Oracle\bd\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN\tnsnames.ora

Agregamos al final:

FUENTEODBC_connection= # <-- es el nombre que le queramos dar al servicio de conexión)
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = IP_DEL_EQUIPO)(PORT = 1521)) # <-- Aquí debemos colocar la dirección IP del equipo

    (CONNECT_DATA= (SID=FUENTEODBC)) # <-- es el nombre del DSN o fuente ODBC que hemos creado previamente en Windows)

(HS=OK)
)

Cabe destacar que en estos ficheros si queremos que el servidor Oracle solo acepte peticiones desde el mismo equipo, donde aparece dirección IP, podemos colocar localhost. Y para que acepte peticiones desde otros equipos, no puede aparecer localhost por ningún lado, debe aparecer exclusivamente la IP del equipo, o podemos poner el nombre de host que tengamos configurados en el equipo y en los dns.

A continuación se debe reiniciar el Listener, como se explica en la siguiente sección, pero si no logramos conectarnos desde otros equipos, puede ser necesario reiniciar por completo el servidor.

Reiniciar LISTENER y probar conexión

Ejecutar en la consola del sistema operativo (CMD): LSNRCTL stop y luego LSNRCTL start

Luego para comprobar que quedo bien: TNSPING FUENTEODBC_connection

Crear vínculo y SQL de prueba

Ejecutamos el siguiente comando SQL en el Oracle para establecer el vínculo:

CREATE PUBLIC DATABASE LINK FUENTEODBCLink CONNECT TO USUARIO_DE_FUENTEODBC IDENTIFIED BY "PASSWORD_DE_FUENTE_ODBC" using 'FUENTEODBC_connection';

FUENTEODBC_connection es el nombre que hayamos asignado en el tnsnames.ora

Luego se puede ejecutar el SQL para probar:

select * from TABLA_EN_FUENTE_ODBC@FUENTEODBCLink ;

Vistas Materializadas

Las consultas a fuentes de datos externas pueden ser algo lentas, especialmente si están ubicadas en otro equipo. Por ello para un mejor rendimiento recomiendo crear vistas materializadas de las tablas que se vayan a utilizar de la base de datos externa, estas se pueden configurar para que se refresquen cada cierto tiempo.

Ejemplo:

CREATE MATERIALIZED VIEW NOMBRE_VISTAREFRESH START WITH SYSDATE NEXT SYSDATE + 1/96AS SELECT * FROM TABLA_EN_FUENTE_ODBC@FUENTEODBCLink;

Donde 1/96 es cada cuántos días se actualiza la vista. En este caso 1/96 de un día son 15 minutos.

Para no sobrecargar la base de datos del servidor, recomiendo que si la tabla no sufre muchas actualizaciones, la actualización se haga unas pocas veces al día (1 para cada 1 día, 1/4 para cada 6 horas). Si la tabla a vincular se actualiza con frecuencia, recomiendo probar cuanto tarda en ejecutarse la consulta, y si por ejemplo, tarda 2 minutos, configurar la vista materializada a que se actualice cada 5 minutos (1/288). Si siempre se requieren los datos actualizados a "tiempo real", entonces no operar con una vista materializada, sino vincular directamente con la fuente (TABLA_EN_FUENTE_ODBC@FUENTEODBCLink)

Mayor información y referencia del comando CREATE MATERIALIZED VIEW:

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_6002.htm#i2063793

Exportar e Importar esquemas de datos Oracle para migración de Servidor

2012-08-03T13:38:00.002+02:00

En este artículo voy a dar una breve explicación de como exportar e importar datos de Oracle con los comandos expdmp e impdmp. Para ello utilizo como ejemplo un usuario MP con contraseña 1234 y su esquema MP. Solo se realizará la exportación e importación de su esquema. Este ejemplo también está basado, en que ésta exportación e importación se hace en servidores distintos.

Para extender información al respecto se pueden consultar las referencias que he utilizado:

Exportar Datos

Debemos configurar un directorio en Oracle donde se guardarán los ficheros de resultado de la exportación. Para ello:
1. Creamos la carpeta C:\TEMP\MPDMP en Windows.
  - En la consola de comandos de Windows:> mkdir C:\TEMP\MPDMP
2. Con el usuario MP, asociamos el directorio Oracle a la carpeta en el sistema de archivos:
  - Se ejecuta la sentencia SQL: CREATE DIRECTORY mpdmp AS '/TEMP/MPDMP';
3. Opcionalmente podemos asignar permisos sobre ese directorio a otro usuario, por ejemplo para hacer respaldo sobre otro esquema sin tener que crear otro directorio:
  - Se ejecuta la sentencia SQL: GRANT read, write ON DIRECTORY mpdmp TO OTRO_USUARIO;
Luego ya podemos ejecutar el comando de exportar:
- En la consola de comandos de Windows:> expdp MP/1234 dumpfile=MP.dmp directory=mpdmp
Si en la consola de comandos de Windows vemos que todo está OK, buscamos en la carpeta el fichero: C:\TEMP\MPDMP\MP.dmp que debemos copiar al otro servidor

Importar Datos

Para importar datos, también debemos configurar un directorio en Oracle, podemos hacerlo siguiendo lo especificado anteriormente en el punto 1 de la exportación.
Luego copiamos el fichero de respaldo, en este caso MP.dmp, en la carpeta que hemos creado.
Con el fichero de respaldo en su lugar, ya podemos ejecutar el comando para importar:
- En la consola de comandos de Windows:> impdp MP/1234 dumpfile=MP.dmp schemas=MP directory=mpdmp
Si en la consola de comandos de Windows vemos que todo está OK, comprobamos con Toad, DBVisualizer, o cualquier herramienta que dispongamos para navegar y ejecutar sentencias sobre el servidor, si el esquema fue importado correctamente.
- Cuando estuve experimentando con esto, me encontré con algunas fallas, pero generalmente eran por falta de permisos. Así que o asignamos ALL PRIVILEGES, o vamos asignando los permisos que se veamos que van resultando requeridos.

Comparar resultados

Podría recomendarles esta herramienta: http://www.dbsolo.com/, que tiene funciones para comparar la estructura de los esquemas de 2 servidores y también para comparar los datos.

Autenticación WSSE para Servicios REST

2012-07-20T14:55:00.001+02:00

Entre aplicaciones que se comunican con Servicios Web, no se realiza un proceso de autenticación como lo hacemos los humanos, (rellenando un formulario, y luego intercambiando una cookie con un ID de sesión). Más aún en los servicios REST que son stateless (sin estado), por lo que en la aplicación que proporciona el Servicio Web, no existe, o no debería existir una sesión. Esto implica que se debe estar intercambiando constantemente los datos para la autenticación y posterior autorización.

Es necesario implementar alguna forma de seguridad, para que no se ejecute cualquier petición, sin tener identificado al usuario y si poder establecer éste tiene el permiso o no de ejecutarlo.

Algunas alternativas son:

HTTP Basic
HTTP Digest
Alguna de las anteriores + SSL
OpenID
OAuth
WSSE

Las 3 primeras forman partes de los estándares HTTP, por lo cual siendo 100% rest es la que debería usarse.

HTTP Basic, tiene el inconveniente de que se envía el password todo el tiempo por la red sin encriptación.

HTTP Digest usa encriptación pero tal cual como está diseñado es susceptible es vulnerable a ciertos ataques.

SSL refuerza la encriptación en los esquemas anteriores, es decir se combina con uno de los 2 anteriores, todo el contenido va encriptado, es una alternativa más segura, aunque puede implicar una pérdida de rendimiento, y requiere instalar certificados en todos los servidores.

OAuth, es una buena alternativa para cuando hay que dar ciertas autorizaciones a terceros. Por ejemplo, los usuarios, con su identidad en una red social de bodas, autorizan a la web de "El Asador de Guadarrama", a que obtenga la lista de invitados a la boda. "El Asador de Guadarrama" conoce la identidad del novio, pero no tiene porque conocer su contraseña. El novio entra a la web de "El Asador...", esta web reconoce que está identificado en red social de bodas, y le pide permiso al novio para obtener la lista de invitados. Este permiso va de forma de un token, que la web de "El Asador", usa para comunicarse con la web de la red social de bodas y pasar la lista de invitados.

En fin, OAuth, es una útil cuando hay terceros involucrados, y en conjunto con el token se declaran unas autorizaciones específicas. Si cambian las autorizaciones / permisos, el novio tiene que dar de nuevo su visto bueno en la web.

Otra alternativa que se podría evaluar es OpenID, pero implica montar un servidor aparte para esta gestión de identidades, y luego hay que asociar cada identidad OpenID entre los modulos o capas que se implementen (web visual, web movil, core, etc).

Por último tenemos WSSE (Web Service Security UsernameToken - http://www.xml.com/pub/a/2003/12/17/dive.html). WSSE es conjunto de de especificaciones de seguridad para los servicios web tradicionales (SOAP). La especificación de UsernameToken, específicamente es para autenticación, y puede ser utilizado fácilmente en otras comunicaciones HTTP.

Básicamente se trata de que el cliente, genera un token con los siguientes datos: nombre de usuario, número aleatorio criptográfico, fecha-hora de creación y un subtoken de todo esto junto con la contraseña encriptada.

Este token se envía dentro de un Header X-WSSE al servidor. El servidor con los datos que vienen planos (todos menos la contraseña), y con la contraseña extraída de la base de datos. Genera por su cuenta el token y lo compara con lo que viene en el header de la petición. Si coincide se da por identificado al usuario.

La fecha-hora de creación sirve para darle una seguridad adicional al token, configurando en el servidor un tiempo de expiración. Es decir, que el token no puede ser usado más de 30 segundos, 5 minutos o 1 hora después de generado, según como se desee configurar. Mientras menor sea el tiempo de expiración, mayor seguridad de que el token no pueda ser reutilizado si es robado.

Un ejemplo del token sería

X-WSSE: UsernameToken Username="bob", PasswordDigest="quR/EWLAV4xLf9Zqyw4pDmfV9OY=", Nonce="d36e316282959a9ed4c89851497a717f", Created="2003-12-15T14:43:07Z"

En Symfony 2 (PHP) existe un Bundle que se puede implementar, el

https://github.com/phiamo/MopaWSSEAuthenticationBundle que a su vez es un fork de https://github.com/escapestudios/EscapeWSSEAuthenticationBundle. El de phiamo, tiene la ventaja de que está actualizado para la versión 2.1 de Symfomy. Es una implementación del ejemplo expuesto en http://symfony.com/doc/current/cookbook/security/custom_authentication_provider.html.

En una próxima entrada espero compartir con ustedes una implementación en Java que se pueda integrar con los JAAS (Java Authentication and Authorization Service).