Con este espacio pretendo crear un rincón de información relacionda con las bases de datos Oracle, sus funcionalidades, características, noticias.... etc. Interaré hacer pequeños artículos con ejemplos demostrativos para que resulte más fácil leerlos. No dudéis en mandar vuestros comentarios, críticas, aclaraciones... etc.

Información del Autor

Llevo más de diez años trabajando como DBA Oracle en diferentes compañías y desde el 2004 lo hago de forma freelance a través de una sociedad creada por mí, Infor Consult Soluciones (http://www.inforconsult.es). Si tienes cualquier duda, comentario o propuesta que hacerme, no lo dudes.

lunes, 9 de marzo de 2009

Habilitar y deshabiltar las constraints automáticamente

Muchas veces nos encontramos con la necesidad de tener que borrar el contenido de ciertas tablas o simplemente de todo nuestro esquema y debido a las restricciones/constraints de integridad referencial, más conocidad por Foreign Key -FK-, la tarea se vuelve pesada o sencillamente inabordable. En estos casos hay que tener muy claro el esquema de restricciones para ir borrando primero las tablas sin dependencias (las tablas hijas) e ir llegando a las tablas maestras.

Si no tenemos este esquema claro, no tenemos tiempo... etc. Podemos manejar esta situación deshabilitando todas las FK, lo que nos permitirá borrar el contenido de las tablas en el orden que queramos, y volviéndolas a habilitar. Una vez más, tener que teclear una por una las sentencias que deshabilitan cada FK es una tarea pesada y lenta. Para este propósitito podemos usar SQL, haciéndo que él mismo nos dé todas las instrucciones. De esta forma, nosotros tecleamos una sentencia que como resultado nos da "n" sentencias. Éstas las capturamos en un spool y lo lanzamos. Todo esto lo tengo automatizado en el siguiente script.

--------- INICIO DEL SCRIPT -------
set echo off

-- Al comienzo nos pide el usuario que posee las restriciones. Si somos nosotros mismos no tenemos que teclear nada.
ACCEPT usuario CHAR DEFAULT 'user' PROMPT 'Usuario (Enter para ninguno): '

-- Ahora nos pide si queremos habilitar o deshabilitar las constraints.
ACCEPT objeto CHAR DEFAULT 'ENABLE' PROMPT 'Si desea **deshabilitar** las restricciones escriba: DISABLE. ENTER para **habilitar**: '

-- Guardamos la sentencia en el buffer SQL
select 'ALTER TABLE ' ||
nvl('&usuario.', user ) ||'.'|| table_name ||
' &objeto. constraint ' || constraint_name || ';'
from dba_constraints
where owner = nvl(upper('&usuario.'), user )
and constraint_type = 'R'
.

-- Configuramos algunos parámetros SQLPlus.
set timing off feed off pagesize 0 verify off

-- Abrimos el spool. Esto escribirá en la carpeta temporal del sistema. Después ejecutamos la sentencia del buffer y cerramos el spool.
spool %TEMP%\constraints.sql
/
spool off

-- Ponemos los parámetros igual que estaban.
set timing on feed on pagesize 100 verify on

-- Sacamos unos mensajes aclaratorios.
prompt ******************* PRECAUCIÓN ********************
pause Se van a lanzar las sentencias que deshabilitan las Foreign Key. Si no desea seguir pulse Ctrl+C
pause ¿Seguro?. Si no desea seguir pulse Ctrl+C

-- Se abre el spool donde dejará el resultado de operar con las constraints. Se ejecutan las sentencias y se cierra el spool.
spool %TEMP%\constraints
start %TEMP%\constraints.sql
spool off

prompt ******************* INFORMACIÓN ********************
prompt Fin. Se ha dejado un fichero de log en %temp%\constraints.lst

--------- FIN DEL SCRIPT -------

Para ejecutarlo, os recomiendo que copiéis las lineas entre INICIO y FIN DEL SCRIPT, las peguéis en un fichero, por ejemplo, constraints.sql. Una vez hecho esto, tenéis que iros a SQLPlus, conectaros con el usuario que posee las restricciones o con otro que pueda administrar las restricciones de éste -normalmente un DBA-, y lanzarlo con la sentencia START ruta\constraints.sql.

Solo debéis responder a las preguntas que os hace y si queréis, él mismo lanzará todas las sentencias de enable/disable constraints dejando un log en la carpeta temporal con nombre constraints.lst.

Juan Lorenzo Arellano
Infor Consult Soluciones.
www.inforconsult.es

martes, 3 de marzo de 2009

Transparent Data Encription (TDE)

Introducción
Desde la release 2 de Oracle 10g, existe la capacidad de cifrar los datos de las columnas de tablas de forma transparente para el usuario, que no debe intervenir para nada. La capa de seguridad cifra y descifra la información antes y después de leerla o almacenarla físicamente.

Este nos evita tener que usar las pesadas funciones de DBMS_CRYPTO y tenernos que pegar con los algoritmos, las semillas...etc.

Restricciones
Tiene algunos incovenientes importantes:
  • Permite solamente índices B*Tree.
  • No permite escaneos de rango para índices.
  • No soporta tipos BLOB y CLOB.
  • Utilidades de exportación antiguas.
  • En general, utilidades que accedan directamente a los datos.
  • No se pueden relacionar columnas FK/PK cifradas.
Visión general
La primera vez que se indica el cifrado de una columna, Oracle crea automáticamente la clave para esa tabla. Cada tabla tiene una clave de cifrado diferente de las que usan las demás. La correspondencia entre las claves de cifrado y las tablas se guarda en el diccionario de datos,
estando las claves cifradas con la clave maestra de la base de datos.

Esta clave maestra se guarda en un mecanismo de seguridad externo,
el wallet. El wallet es un fichero con extensión *.p12 que guarda un certificado con propósito de cifrado. Permanece cerrado por omisión, lo que impide que la base de datos pueda conocer la clave maestra y por tanto, cifrar y descifrar. Por el contrario, una vez abierto el wallet permanece abierto hasta que se reinicie la base de datos o hasta que se cierre el wallet explícitamente.

Wallets
Creación
Lo primero de todo es tener un wallet que contenga la clave maestra. Para crearlo, hay que configurar el parámetro ENCRYPTION_WALLET_LOCATION del fichero sqlnet.ora apuntando al directorio donde residirá el wallet:


ENCRYPTION_WALLET_LOCATION=
(SOURCE=
(METHOD=FILE)
(METHOD_DATA=
(DIRECTORY=c:\oracle\product\10.2.0\db_1\network\admin)))


Ahora, para que Oracle genere automáticamente el wallet y le asigne la clave maestra:

ALTER SYSTEM SET ENCRYPTION KEY IDENTIFIED BY password;

lo que habrá generado el fichero ewallet.p12 en directorio indicado arriba. Las utilidades mkwallet (como comando) y Oracle Wallet Manager también se puden usar para crear wallets.

Apertura
Para poder usar TDE primero hay que abrir el wallet que contiene la clave maestra. Esto se puede hacer de forma explícita con:


ALTER SYSTEM SET WALLET OPEN IDENTIFIED BY password;

Siempre hay que abrir el wallet después de reiniciar la base de datos salvo que esté configurado como autologin. Para configurarlo como autologin hay que usar Wallet Manager: en el menú, seleccionar el wallet y marcar la opción Auto Login.

Cierre
El cierre se hace automáticamente cuando se cierra la base de datos. De todas formas, se puede cerrar manualmente con la sentencia:


ALTER SYSTEM SET WALLET CLOSE;

Copia de seguridad
Este fichero contiene la clave maestra sin la cual no es posible descifrar las claves con la que están cifrados los datos. Perderlo significa perder los datos: no hay puerta trasera. Por tanto, es imprescindible tener backup del wallet.

Es un fichero con formato p12. Basta con copiarlo a menudo, tal vez con el backup diario de la base de datos y mantener múltiples copias y en formatos diferentes.

En caso de pérdida, bastará con restaurar el fichero a su ubicación original.

Operaciones de cifrado
Cifrar
Los requisitos para poder cifrar son:
  1. El parámetro COMPATIBLE debe estár a 10.2 o superior.
  2. Debe existir un wallet, tener una clave maestra y estar abierto.
Para cifrar basta con indicarlo después del tipo de datos:

CREATE TABLE table ( col_name col_type ENCRYPT, …);

ALTER TABLE table MODIFY ( col_name col_type ENCRYPT,…);


Una vez cifrada una columna, el acceso a los datos de la misma se hace normalmente, ya que Oracle devuelve los datos descifrados e introduce los datos cifrándolos previamente sin intervención del usuario.

Descifrar
Para eliminar el cifrado de una columna cifrada, hay que indicarlo con DECRYPT:

ALTER TABLE table MODIFY ( col_name col_type DECRYPT,…);

Recifrar
Después de haber cifrado las columnas de una tabla con la clave aleatoria generada por Oracle, se pude recifrar para cambiar la clave y opcionalmente el algoritmo:

ALTER TABLE table REKEY [USING '3DES168');

Algoritmos
Por defecto usa AES de 192 bits añadiendo previamente una lista de bits antes de hacer el cifrado (se conoce como "SALT"). Esto se hace para evitar que posibles atacantes usen técnicas de dedución basándose en parejas conocidas de texto en claro y texto cifrado. Por contra, una columna con "SALT" no puede se indexada. Si es requisito imprescindible, se le puede forzar para que no use la "SALT":

CREATE TABLE table ( col_name col_type ENCRYPT NO SALT, …);

ALTER TABLE table MODIFY ( col_name col_type ENCRYPT NO SALT,…);

Los algoritmos soportados son:
  • Triple Des (DES) con 168 bits: 3DES168.
  • Advanced Encryption Standard (AES) con 128, 168 y 256 bits: AES128, AES168 y AES256.
Vistas
Las vistas que nos ofrecen información sobre las columnas encriptadas son:
  • DBA_ENCRYPTED_COLUMNS.
  • ALL_ENCRYPTED_COLUMNS.
  • USER_ENCRYPTED_COLUMNS.