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.