¡Muchísimas gracias! ¡Espero que os guste y os sea útil!
Amazon.es Amazon.com
¡Por fin!
En cuanto finalice el diseño de la portada y la contraportada (si los de Amazon no ponen impedimento) ya estará disponible para comprar tanto en amazon.com como en amazon.eu.
(Continúa de Parte I)
Hace unos años publiqué un artículo llamado «PL/SQL y ejecuciones en host» en el que describía el paso a paso para poder, desde PL/SQL, ejecutar código en el sistema operativo.
Oracle no permite que los procedimientos y funciones puedan acceder al host, pero sí permite llamadas a funciones externas implementadas con C o PASCAL, y redireccionadas como librerías mediante un objeto library.
Mi intención inicial fue la de crear un procedimiento PL/SQL que realizara un backup en caliente del servidor, realizase un export, import, o cualquier otra invocación a un ejecutable residente en el sistema operativo.
Hoy he visto una configuración similar en una base de datos en un entorno de producción, que realizan la misma implementación pero mediante una función.
create or replace
FUNCTION sysrun (syscomm IN VARCHAR2)
RETURN BINARY_INTEGER
AS LANGUAGE C
NAME «sysrun»
LIBRARY shell_lib
PARAMETERS(syscomm string);
Broadcast message from root (Thu Jan 27 13:16:34 2011):
The system is going down for reboot NOW!
SYSTEM.SYSRUN('SUDOREBOOT')
---------------------------
0
En todos los ejemplos que he encontrado sobre encriptación y desencriptación de datos en Oracle, siempre se usan procedimientos PL/SQL para establecer la seguridad en la base de datos. No he encontrado un sólo ejemplo que permita hacer un insert «encriptado» y una consulta «desencriptada».
Imaginando el siguiente escenario: Cada usuario tiene una «palabra secreta» para desencriptar su propia información. En la base de datos todo se registra encriptado.
Para ello, Oracle ofrece dos paquetes:
– DBMS_OBFUSCATION_TOOLKIT. A partir de Oracle8i, que soporta encriptación DES y triple DES (Data Encription Standard), y con ciertas limitaciones (por ejemplo, los datos a encriptar han de ser un múltiplo de 8 bytes).
– DBMS_CRYPTO. A partir de Oracle10g. Soporta más formas de encriptación, como la AES (Advanced Encription Standard), que sustituye el anterior DES y no hay limitación con el número de carácteres.
Para mas información, la documentación de Oracle ofrece esta comparativa de funcionalidades.
El siguiente ejemplo muestra la encriptación de la palabra «SECRETO» (8 bytes) y genera un error al intentar encriptar «SECRETITOS!» (11 bytes)
SQL> select DBMS_OBFUSCATION_TOOLKIT.
DBMS_OBFUSCATION_TOOLKIT.
——————————
lr??
SQL> select DBMS_OBFUSCATION_TOOLKIT.
select DBMS_OBFUSCATION_TOOLKIT.
*
ERROR at line 1:
ORA-28232: invalid input length for obfuscation toolkit
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT_
ORA-06512: at «SYS.DBMS_OBFUSCATION_TOOLKIT»
El error ORA-28232 corresponde a la longitud inadecuada de la cadena a encriptar. ‘SECRETITOS!» tiene 11 carácteres y el paquete está limitado a múltiplos de 8 bytes. Por ejemplo, el número de una tarjeta de crédito.
SQL> select DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1234567812345678′,
2 key_string=>’clavedesencript’)
3 from dual;
DBMS_OBFUSCATION_TOOLKIT.DESENCRYPT(INPUT_STRING=>’1234567812345678′,KEY_STRING=
——————————————————————————–
}??X??
De modo que la desencriptación funciona de igual modo, usando la función DESDecrypt
SQL> select DBMS_OBFUSCATION_TOOLKIT.
2 input_string=>DBMS_
3 key_string=>’CLAVE_BUENA’) from dual;
DBMS_OBFUSCATION_TOOLKIT.
——————————
SECRETO!
SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_BUENA’) from dual;
DBMS_OBFUSCATION_TOOLKIT.DESDECRYPT(INPUT_STRING=>DBMS_OBFUSCATION_TOOLKIT.DESEN
——————————————————————————–
1111222233334444
y si se utiliza una clave distinta, la información no se desencriptará adecuadamente.
SQL> select DBMS_OBFUSCATION_TOOLKIT.
2 input_string=>DBMS_
3 key_string=>’CLAVE_ERRONEA’) from dual;
DBMS_OBFUSCATION_TOOLKIT.
——————————
???! k
…ó producirá un error.
SQL> select DBMS_OBFUSCATION_TOOLKIT.DESDecrypt(
2 input_string=>DBMS_OBFUSCATION_TOOLKIT.DESEncrypt(input_string=>’1111222233334444′,key_string=>’CLAVE_BUENA’),
3 key_string=>’CLAVE_MALA’) from dual;
ERROR:
ORA-29275: partial multibyte character
no rows selected
El siguiente ejemplo muestra la encriptación de la palabra «SECRETITOS!» (11 bytes) usando una suite de encriptación que ya viene implementada. En concreto es la DES_CBC_PCKS5, que contiene encriptación DES, encadenamiento de cifrado de bloques y modificadores de relleno PCKS5.
Es preciso, para invocar correctamente a este paquete, realizar una conversión a RAW de las cadenas a encriptar. He utilizado para ello el paquete UTL_RAW y la función UTL_I18N.STRING_TO_RAW.
SQL> select DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
2 typ => 4353,
3 key => UTL_I18N.STRING_TO_RAW (‘clavedesencript’, ‘AL32UTF8’)
4 )
5 from dual;
DBMS_CRYPTO.ENCRYPT(SRC=>UTL_
——————————
1BA7F933C2CAD0C7F4FDA685775BE0
Y la desencriptación de la información, con la función DECRYPT.
SQL> select UTL_RAW.cast_to_varchar2(
2 DBMS_CRYPTO.DECRYPT(
3 DBMS_CRYPTO.ENCRYPT(src => UTL_I18N.STRING_TO_RAW (‘SECRETITOS!’, ‘AL32UTF8’),
4
5
6
7 typ => 4353,
8 key => UTL_I18N.STRING_TO_RAW (‘clavedesdecript’, ‘AL32UTF8’)
9 )
10 )
11 from dual;
UTL_RAW.CAST_TO_VARCHAR2(DBMS_
——————————
SECRETITOS!
Para más información sobre las múltiples formas de encriptación y uso de claves, lo mejor es consultar la documentación del paquete DBMS_CRYPTO.
El concepto de «minería de datos» se basa en el análisis de los datos con fines predictivos, para encontrar patrones ocultos en éstos… ¿quien podría adivinar que a una determinada hora o un determinado día de la semana se consume un determinado producto? ¿o que un producto orientado a hombres (cuchillas de afeitar) pasa a ser usado por mujeres solteras de un rango de edad?
Una predicción de este tipo podría sugerir la creación de una nueva linea de producto, ofertas, etc.
Hace tiempo impartí una conferencia sobre cómo implementar un modelo de base de datos de reservas en vuelos, desde un diagrama entidad-relación concreto hasta la explotación de datos históricos en el futuro. La aplicación pasaba por varias etapas (diseño, implementación, uso/cargas de datos, paso a histórico y reporting), y aunque los datos eran cargas completamente aleatorias, sucedían ciertos patrones interesantes.
Los casados tomaban vuelos a Roma, los solteros a Milán, «Air France» viajaba con los vuelos a medio llenar, y otras compañías apenas tenían uno o dos vuelos con pérdidas…
De modo que se me ocurrió hacer una prueba de minería de datos, para buscar una predicción que no pudiera verse «a simple vista». Éste fue el resultado:
Al detalle. Una consulta del tipo «Datos de cliente con la fecha del primer contrato, fecha de la primera cancelación de contrato, fecha del último contrato contratado, fecha de…» suele consultarse con una subconsulta para cada «fecha de…».
Éste ejemplo, o el típico «Los tres contratos más recientes, las cinco últimas cancelaciones, etc.» siempre hacen que los programadores realicen una subconsulta por cada una de las condiciones… y otra y otra y al final el rendimiento se incrementa tanto de consultar varias veces la misma tabla.
…evidentemente, la consulta SQL se ha hecho tan vasta que resulta muy complicado mantenerla.
Para esta casuística, las funciones analíticas se aplican a un subconjunto de registros, por lo que Oracle, para gestionarlo correctamente, crea una ventana SQL intermedia para reagrupar una y otra vez los resultados de una consulta. Así, dado el anterior ejemplo, Oracle tomaría todos los contratos de ese cliente y los agruparía para cada columna de resultados: el primer contrato contratado, el primer cancelado, el último contrato de alta, etc. sin necesidad de consultar una y otra vez la tabla de contratos.
Las funciones analíticas tienen la siguiente sintaxis (no es la sintaxis completa).
FUNCIÓN_ANALITICA(campo)
OVER (PARTITION BY campo_agr1, campo_agr2
ORDER BY campo_ord1 NULLS LAST)
Un ejemplo de su uso sería, por ejemplo, intentar corregir esta consulta:
SELECT a.ID_FACTURA,
a.FALINEA_AUX – b.minCount + 1 ID_FALINEA,
a.ID_CLIENT,
a.ID_COMPTEFACT,
a.PRODUCT_ID,
a.ID_PRCATPRODUCTE,
a.DS_PRNUMSERVEI,
a.ID_FACONCEPTE,
a.DT_FAFACTURACIO,
a.NUM_FAIMPORTCONCEPTE,
a.PRODUCT_LABEL,
a.DT_MOVIMENT,
a.FG_TIPUSOPERACIO,
a.asset_id,
a.PRODUCT_ATTR_VALUE
FROM vw_ci_linia_factura_tmp a,
(select t.id_factura,
t.dt_fafacturacio,
min(t.falinea_aux) minCount
from vw_ci_linia_factura_tmp t
group by t.id_factura,
t.dt_fafacturacio
) b
WHERE a.id_factura = b.id_factura
ORDER BY a.id_factura, a.FALINEA_AUX – b.minCount + 1 ASC;
No es necesario. Los costes de ejecución se reducen a la mitad.
SELECT a.ID_FACTURA,
a.FALINEA_AUX – min(falinea_aux) over
(partition by id_factura, dt_fafacturacio) +1 ID_FALINEA,
a.ID_CLIENT,
a.ID_COMPTEFACT,
a.PRODUCT_ID,
a.ID_PRCATPRODUCTE,
a.DS_PRNUMSERVEI,
a.ID_FACONCEPTE,
a.DT_FAFACTURACIO,
a.NUM_FAIMPORTCONCEPTE,
a.PRODUCT_LABEL,
a.DT_MOVIMENT,
a.FG_TIPUSOPERACIO,
a.asset_id,
a.PRODUCT_ATTR_VALUE
FROM sta_vw_ci_linia_factura_tmp a
ORDER BY 1,2 ASC;