sábado, 2 de mayo de 2009

Encriptando columnas con TDE (Transparent Data Encryption) en tablas con millones de registros

Para los que no conocen TDE, es una nueva funcionalidad de 10g R2 que permite proteger datos sensibles de las columnas de nuestras tablas encriptando los datos al almacenarlos en los respectivos Data Files en el sistema operativo. Para protegernos de personas malintencionadas que quieran desencriptar los datos sin autorización, guarda las claves de encriptamiento en un módulo seguro externo a la base de datos.

Mi intención en éste post no es mostrar el paso a paso de cómo implementar TDE, sino mostrarles cómo encriptar columnas con TDE en tablas con millones de datos de la manera más eficiente y con el menor impacto posible en cuanto a la performance.

En el momento en el que encriptamos columnas en una tabla, sólo podemos acceder a la tabla en modo lectura... NO están permitidas las operaciones DML hasta que el encriptamiento termine. En tablas chicas, con muy pocos datos, ésto no suele perjudicarnos demasiado. Pero qué sucede en tablas con millones de registros? En este caso, el encriptamiento puede durar varias horas!!!

La estrategia que vamos a utilizar para encriptar columnas en tablas con millones de registros, es utilizando el paquete DBMS_REDEFINITION.

Veamos un ejemplo:

Tengo una tabla llamada TEST_TDE con 1 millón de registros. Esta tabla tiene 3 columnas: ID (primary key), NOMBRE, NUM_TARJETA. La columna que vamos a encriptar es NUM_TARJETA ya que tiene los números de tarjetas, y como para mi es información sensible, quiero encriptarla y protegerla con TDE.

SQL_10gR2> SELECT count(*) FROM test_tde;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE

Nombre Nulo? Tipo
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NOMBRE VARCHAR2(44)
NUM_TARJETA NUMBER

SQL_10gR2> SELECT index_name, column_name FROM dba_ind_columns WHERE table_name = 'TEST_TDE';

INDEX_NAME COLUMN_NAME
---------------------------------------- ----------------------------------------
TEST_TDE_ID_PK ID

1 fila seleccionada.

SQL_10gR2> SELECT constraint_name, constraint_type FROM dba_constraints WHERE table_name = 'TEST_TDE';

CONSTRAINT_NAME C
------------------------------ -
TEST_TDE_ID_PK P

1 fila seleccionada.

Bien, ahora vamos a encriptar la columna NUM_TARJETA de la manera tradicional y sin utilizar ninguna técnica. En la SESIÓN 1, voy a encriptar la columna, en la SESIÓN 2, voy a modificar el valor de uno de los registros de la tabla mientras se está realizando el procedimiento de encriptado... veamos qué sucede!

SESIÓN 1:

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta ENCRYPT);

Tabla modificada.

Transcurrido: 00:01:20.11

SESIÓN 2:

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:01:17.64

Como podemos observar, el encriptamiento de 1 millón de registros demoró 1 minuto 20 segundos. Mientras que se encriptaban los datos, ejecuté un UPDATE en otra sesión y demoró 1 minuto 17 segundos. Esto es debido a que la tabla quedó loqueda en la SESIÓN 1 por el proceso de encriptamiento y la SESIÓN 2 tuvo que esperar que termine de encriptar los datos para poder modificarlos. Imaginen los problemas de performance que tendrían en sus aplicaciones si tienen varios usuarios concurrentes realizando operaciones DML sobre esa tabla mientras se está ejecutando el procedimiento de encriptación!!!

Si ejecutamos el mismo UPDATE luego de que los datos ya se encuentran encriptados, podemos observar que no demoró nada en ejecutarse la operación DML.

SQL_10gR2> UPDATE test_tde SET num_tarjeta = 123456789 WHERE id = 1000000;

1 fila actualizada.

Transcurrido: 00:00:00.00

Ahora vamos a ver la técnica que les mencionaba con el paquete DBMS_REDEFINITION. Lo primero que vamos a hacer, es ejecutar el procedimiento CAN_REDEF_TABLE de éste paquete para corroborar que la tabla puede redefinirse online.

SQL_10gR2> exec DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST', 'TEST_TDE', dbms_redefinition.cons_use_pk);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.31

Como el procedimiento terminó sin errores, quiere decir que la tabla puede ser redefinida online.
Ahora vamos a comenzar el proceso de redefinir la tabla. Para ésto, primero tenemos que crear una tabla intermedia (TEST_TDE_TEMP) con la estructura de la tabla TEST_TDE en donde Oracle va a colocar los datos de la tabla original de manera temporal.

SQL_10gR2> ALTER TABLE test_tde MODIFY (num_tarjeta DECRYPT);

Tabla modificada.

Transcurrido: 00:01:04.53

SQL_10gR2> SET long 400000000

SQL_10gR2> SELECT dbms_metadata.get_ddl('TABLE', 'TEST_TDE', 'TEST') FROM dual;

DBMS_METADATA.GET_DDL('TABLE','TEST_TDE','TEST')
--------------------------------------------------------------------------------

CREATE TABLE "TEST"."TEST_TDE"
( "ID" NUMBER,
"NOMBRE" VARCHAR2(44),
"NUM_TARJETA" NUMBER,
CONSTRAINT "TEST_TDE_ID_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"

1 fila seleccionada.

SQL_10gR2> CREATE TABLE TEST_TDE_TEMP
2 (
3 ID NUMBER,
4 NOMBRE VARCHAR2(44),
5 NUM_TARJETA NUMBER ENCRYPT,
6 CONSTRAINT TEST_TDE_TEMP_ID_PK PRIMARY KEY (ID)
7 );

Tabla creada.

Como pueden notar, lo primero que hice fue desencriptar la columna NUM_TARJETA para poder volver a encriptarla con ésta técnica. Luego utilicé el paquete DBMS_METADATA para ver la estructura de la tabla TEST_TDE y poder crear una similar con el nombre TEST_TDE_TEMP.

Ahora vamos a redefinir la tabla con el procedimiento START_REDEF_TABLE.

SQL_10gR2> exec dbms_redefinition.start_redef_table('TEST', 'TEST_TDE', 'TEST_TDE_TEMP', 'id, nombre, num_tarjeta', DBMS_REDEFINITION.CONS_USE_PK);

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:41.18

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000000

1 fila seleccionada.

SQL_10gR2> desc TEST_TDE_TEMP

Nombre Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
NOMBRE VARCHAR2(44)
NUM_TARJETA NUMBER ENCRYPT

SQL_10gR2> SELECT master, log_table FROM dba_mview_logs WHERE log_table LIKE '%TEST%';

MASTER LOG_TABLE
------------------------------ ------------------------------
TEST_TDE MLOG$_TEST_TDE

1 fila seleccionada.

Fijense, que al redefinir la tabla, Oracle copió los datos de la tabla TEST_TDE y los insertó en la tabla transitoria TEST_TDE_TEMP con la columna NUM_TARJETA encriptada. A su vez, Oracle creó una vista materializada para registrar cualquier cambio que se realice sobre la tabla TEST_TDE para luego, en el momento de hacer la sincronización de datos entre las 2 tablas, pueda impactar cualquier cambio realizado.

Antes de realizar el proceso de sincronización entre las 2 tablas, voy a agregar algunos registros a la tabla TEST_TDE para ver si luego de la sincronización, los últimos cambios que realicé fueron impactados. Recuerden, que todos éstos pasos los estamos haciendo online, y que por el momento, los usuarios siguen trabajando sobre la tabla TEST_TDE sin ningún tipo de impacto en cuanto a la performance.

SQL_10gR2> INSERT INTO test_tde
2 SELECT * FROM
3 (
4 SELECT level id, 'nom_'||level, round(dbms_random.value(100000000000,900000000000))
5 FROM dual
6 CONNECT BY level <= 1000100
7 )
8 WHERE id > 1000000;

100 filas creadas.

Transcurrido: 00:00:06.93

SQL_10gR2> COMMIT;

Confirmación terminada.

Ahora vamos a sincronizar con el procedimiento SYNC_INTERIM_TABLE, las 2 tablas para aplicar cualquier cambio realizado en la tabla TEST_TDE a la tabla TEST_TDE_TEMP.
En éste paso en adelante, necesitamos una ventana de tiempo muy chica para ejecutar los 2 procedimientos que restan. En esta ventana de tiempo, los usuarios no deben estar modificando la tabla TEST_TDE.

SQL_10gR2> exec DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.29

SQL_10gR2> SELECT count(*) FROM test_tde_temp;

COUNT(*)
----------
1000100

1 fila seleccionada.

Transcurrido: 00:00:06.92

SQL_10gR2> desc TEST_TDE

Nombre Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
NOMBRE VARCHAR2(44)
NUM_TARJETA NUMBER

Los 100 registros que habiamos agregado a la tabla TEST_TDE fueron impactados con éxito en la tabla TEST_TDE_TEMP! Para finalizar, lo único que resta es ejecutar el procedimiento FINISH_REDEF_TABLE para aplicar en la tabla TEST_TDE todos los cambios de la tabla TEST_TDE_TEMP (incluyendo la columna encriptada).

SQL_10gR2> exec DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST', 'TEST_TDE', 'TEST_TDE_TEMP');

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.37

SQL_10gR2> desc TEST_TDE

Nombre Nulo? Tipo
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
NOMBRE VARCHAR2(44)
NUM_TARJETA NUMBER ENCRYPT

SQL_10gR2> DROP TABLE test_tde_temp;

Tabla borrada.

Con el mas mínimo impacto en la performance, pudimos encriptar la columna NUM_TARJETA de la tabla TEST_TDE (que contiene 1 millón de registros) y no afectar las operaciones de los usuarios online sobre esa tabla! Sólo tuvimos que necesitar una ventana de tiempo de 1 minuto para colocar la tabla TEST_TDE nuevamente disponible para todos los usuarios.

17 comentarios:

Unknown dijo...

Hola Leonardo.
Sabes si se puede utilizar el paquete DBMS_REDEFINITION para sacar el 90% de la información de una tabla con millones de registros?
Tengo entendido que ese paquete solamente trabaja sobre la totalidad de los registros pero no se si haya una funcionalidad que permita hacerlo con un porcentaje del mismo (utilizando un where, por ejemplo, con un costo pequeño sobre la BD y que sea online para no afectar el servicio) sino se puede hacer con este paquete sabes de algun otro que pueda usar?
Muchas gracias :)

Leonardo Horikian dijo...

Hola Astrid,

Con este paquete no se puede realizar sobre un porcentaje de registros, siempre es sobre la totalidad.

Comentame exactamente que es lo que necesitas hacer para poder ayudarte y darte la solución que más te convenga.

Saludos

Unknown dijo...

Hola...

Lo que necesito es lo siguiente:
Tengo una tabla con 2mil millones de registros la cuál se encuentra en uso constantemente, necesito pasar los datos de la tabla en línea excluyendo algunos registros, por ejemplo...
WHERE COLUMNA1 NOT IN ('1','2','3').

Estabamos pensando en usar el redefinition para pasar de la tabla origen a la tabla temporal todos los registros y sobre la tabla temporal crear un trigger BEFORE INSERT donde se valide si el registro insertado esta entre 1,2 y 3 si es así no lo inserte y sino que lo inserte... Pero al parecer el redefinition deshabilita el trigger mientras se ejecuta el DBMS_REDEFINITION.START_REDEF_TABLE, sabes si de alguna manera podemos hacerlo con el trigger sin que este sea bloqueado? o que otra alternativa me puedes seguerir...

Gracias....

Leonardo Horikian dijo...

Hola Astrid,

Me gustaría que me cuentes más en detalle para que necesitan pasar los datos a una tabla temporal.
Esto sería fácil de realizar con un simple INSERT AS SELECT.

Ej:

INSERT INTO tabla_temporal
SELECT *
FROM tabla_origen
WHERE COLUMNA1 NOT IN ('1','2','3');

Por favor, contame más en detalle para poder ayudarte a encontrar la mejor manera de realizar lo que necesitas.

Saludos

Anónimo dijo...

Hola Leonardo
Estoy utilizando FORMS 4.5 y la base 8.1 tengo que realizar una encriptación de un solo campo, cual sería la mejor manera de llevar a cabo esta modalidad.

Muchas gracias

Leonardo Horikian dijo...

Hola Anónimo,

En 8i podes utilizar el paquete DBMS_OBFUSCATION_TOOLKIT que te permite encriptar/desencriptar el contenido de una columna.

http://download-west.oracle.com/docs/cd/A87860_01/doc/appdev.817/a76936/dbms_obf.htm#6518

Saludos

Anónimo dijo...

Hola Leonardo , mi nombre es Santiago, no puedo utilizar DBMS_OBFUSCATION_TOOLKIT debido a que mi base es 8.0 perdon por el error , conoces algo para realizar lo mismo.

Muchas gracias por tu tiempo

Anónimo dijo...

Hola mi nombre es Santiago y necesito encriptar y descentriptar un cmapo en una tabla pero estoy trabajando con una base oracle 8.0 que puedo utilizar

Muchas gracias

Anónimo dijo...

Hola Leonardo, excelente POST y gracias por ello. Quisiera saber si el TDE es bajo licencia adicional, o si en el Oracle Standad Edition One se puede activar sin licenciamiento adicional.

Gracias,

Jesús

Leonardo Horikian dijo...

Hola Jesús,

TDE es parte de Oracle Advanced Security Option (ASO) y necesita licencia adicional.

Saludos

Luis Mauricio Brenes Gonzalez dijo...

Saludos, se supone que el TDE es precisamente eso....Transparente, cuando una habilita la llave y encripta alguna columna de una tabla, esta columna puede verse siempre y cuando el Wallet este Open, pero que pasa cuando esta Close y desde una aplicacion en Developer es invocada la columna encriptada?? da error o se necesita de algun privilegio especial para que sea transparente desde Developer.

Gracias

fede dijo...

Hola leonardo estoy haciendo algo on respecto a encriptacion. Para hacerlo cree un wallet con el (alter system set enc. key ....)luego modifiq el sqlnet en el servidor lo tengo asi
SQLNET.AUTHENTICATION_SERVICES = (NTS)

ENCRYPTION_WALLET_LOCATION =
(SOURCE =
(METHOD = file)
(METHOD_DATA =
(DIRECTORY = C:\app\Oracle\admin\fede\wallet)))
SQLNET.ENCRYPTION_SERVER = ACCEPTED
SQLNET.ENCRYPTION_TYPES_SERVER = AES256
SQLNET.CRYPTO_CHECKSUM_SERVER=ACCEPTED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER=MD5
en el servidor y cuando le hice alter table x modify(colx encrypt)
al hacerle un select* from x no me aparece la columna encriptada te suena q puede estar mal? agradeceria una mano

Mario dijo...

Buenas tardes Leonardo,

me puedes explicar un poquito por encima todo este codigo, ya que, la idea que yo tenia era que al realizar esto, si yo por ejemplo hago un encrypt de un campo de un tabla, pensaba que posteriormente al hacerle un select no visualizaría ese campo, sin embargo si lo veo igual.

Un saludo y muchas gracias.

Leonardo Horikian dijo...

Hola Anónimo,

En la versión 8 puedes utilizar el paquete DBMS_OBFUSCATION_TOOLKIT.

Saludos

Leonardo Horikian dijo...

Hola Mario,

Te explico. TDE permite encriptar datos 'sensibles' de los datafiles para prevenir el acceso a los mismos desde el sistema operativo. Esto significa que si tenes un campo encriptado y si ejecutas un select, vas a poder ver los datos del campo pero si abres el datafiles desde el sistema operativo utilizando un editor hexadecimal, no podrás ver los datos de ese campo encriptado.

Saludos

Leonardo Horikian dijo...

Hola Luis Mauricio,

Si la Wallet esta CLOSE entonces siempre va a dar error al intentar ver los datos de la columna encriptada.

ERROR at line 1:
ORA-28365: wallet is not open

Saludos

Leonardo Horikian dijo...

Hola fede,

La columna está encriptada dentro del datafile pero podés ver los datos al ejecutar un SELECT sobre la columna.

Para corroborar que la columna está encriptada dentro del datafile, ejecutá la siguiente consulta:

SELECT * FROM dba_encrypted_columns;

Saludos

The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.