Auditoria de operaciones personalizada con Temporal Tables

En no raras ocasiones nos vemos en la necesidad de mantener una auditoría de seguridad personalizada ante operaciones INSERT, UPDATE y DELETE. Se nos presenta entonces la problemática de querer añadir a la detección de la operación, determinados artefactos lógicos para nuestra trazabilidad interna. Imaginemos por ejemplo que queremos añadir el usuario lógico de nuestra aplicación, que le pasamos en una propiedad del Application Name de la cadena de conexión, es decir…algo digamos que requiera de cierta lógica por nuestra parte a la hora de guardar el dato.

Esto es fácil de visualizar viendo esta supuesta tabla de lo que quisiéramos ver para una fila con id=1:

En la imagen anterior tenemos lo que queremos analizar. Los diferentes estados por los que el valor de una columna ha pasado. Vemos, por tanto, que para el ID =1

• El día 1 de Mayo Pepe, insertó el valor “Hola”
• El día 2 de Mayo Pepe actualizó su valor a “Adiós”
• El día 3 de Mayo, Juan atualizó su valor a “Textonuevo”
• El día 4 de Mayo, Andres borró la fila

El valor de “usuario que cambió el dato” es un valor que puede venir de donde queramos (SUSER_SNAME() o incluso de un valor introducido en el APP_NAME() que queramos parsear). La problemática a resolver es fácil de ver y se puede resumir en que queremos saber la vida que ha tenido cada fila de nuestra tabla durante el tiempo, para determinar quien ha modificado un dato, cuando y cual era su valor anterior antes de dicho momento con fines de auditoria de seguridad.
Esto que queremos resolver es un problema tradicional de bases de datos que está ampliamente resuelto mediante herramientas de terceros como las propuestas por APEXSQL o mediante triggers de tabla manuales donde obtener esta información y mapearla.  Ambos escenarios son y siguen siendo obviamente válidos y prácticos, pero en este artículo quería comentar uno nuevo que podemos utilizar ahora mediante una feature que viene de la mano de SQL Server 2016 llamado “System-Versioned Temporal tables” (compatibles con Azure SQL Databases). No es la intención hablar de esta característica en este post puesto que ya lo ha introducido mi compañero Ruben Garrigos en este post que te recomiendo leer, sino darle una aplicación práctica bastante útil a mi modo de ver como es la de facilitar el desarrollo de auditorias personalizadas de operaciones INSERT-UPDATE-DELETE.
Para conseguir lo anterior de forma nativa con system-versioned temporal tables, dado que su uso requiere que la tabla base tenga una pequeña modificación, lo mejor es que usemos una vista como interfaz para la aplicación, en lugar de hacerlo directamente a través de la tabla. De esta forma nos aseguramos que la aplicación no vea información que no debería ver, por seguridad. Esto como digo es algo opcional pero recomendable desde el punto de vista de la seguridad.

[box type=”info”] La gracia de esta solución es que nos abstraemos de toda la parte del historificado, que lo hará SQL Server nativamente y además nuestros triggers van a ser realmente livianos y sin apenas código.[/box]

Una vez visto lo que queremos conseguir, procedemos a mostrar un ejemplo de solución.

Paso 1: Creación de tabla

Creamos nuestra tabla de ejemplo:

-- Creamos la tabla con soporte de versionado
-- 
CREATE TABLE datospersonales_tbl 
(
    ID int identity (1,1) NOT NULL PRIMARY KEY CLUSTERED, 
    Nombre varchar (50) NOT NULL, 
    Telefono varchar (20) NULL,
    [user_name]  sysname,
    transaction_id UNIQUEIDENTIFIER,
    operation CHAR(1),
    inicio datetime2 GENERATED ALWAYS AS ROW START NOT NULL, 
    fin datetime2 GENERATED ALWAYS AS ROW END NOT NULL,   
    PERIOD FOR SYSTEM_TIME (inicio,fin)   
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.datospersonales_historico));
go

Paso 2: Creación de vista

Creamos la vista que hará de interfaz para la aplicación (la que verán realmente los usuarios). En esta vista solo exponemos las columnas que realmente necesita la aplicación. Además creamos la vista con SCHEMABINDING para vincularla a la tabla base. De esta forma si se quiere modificar la tabla base, dará error advirtiendo de que antes hay que eliminar esta vista…así evitamos problemas inesperados.

CREATE VIEW dbo.datospersonales
WITH SCHEMABINDING
AS
    SELECT  id ,
            nombre ,
            telefono
    FROM    dbo.datospersonales_tbl;
GO

Paso 3: Creación de triggers

En este caso la información extra es:

• Usuario lógico de aplicación
  • Lo sacamos del aplication name que la aplicación ha configurado
  • En este caso hemos concatenado en el APP_NAME el usuario lógico de nuestra aplicación
• Id de operación
  • lo sacamos con un simple NEW_ID() por poner ejemplo
• Tipo de operación
  • Según si es Insert (I), Update (U) o Delete (D)

CREATE TRIGGER ins_trigger_datospersonales_tbl ON dbo.datospersonales
    INSTEAD OF INSERT
AS
    BEGIN
        SET NOCOUNT ON;
        INSERT  INTO dbo.datospersonales_tbl
                ( Nombre ,
                  Telefono ,
                  [user_name] ,
                  transaction_id ,
                  operation
                )
                SELECT  nombre ,
                        telefono ,
                        SUBSTRING(APP_NAME(), CHARINDEX(',', APP_NAME()) + 1,
                                  LEN(APP_NAME()) - CHARINDEX(',', APP_NAME())) ,
                        NEWID() ,
                        'I'
                FROM    inserted;

    END;
GO

CREATE TRIGGER upd_trigger_datospersonales_tbl ON dbo.datospersonales
    INSTEAD OF UPDATE
AS
    BEGIN
        SET NOCOUNT ON;
        UPDATE  dbo.datospersonales_tbl
        SET     [user_name] = SUBSTRING(APP_NAME(),
                                        CHARINDEX(',', APP_NAME()) + 1,
                                        LEN(APP_NAME()) - CHARINDEX(',',
                                                              APP_NAME())) ,
                transaction_id = NEWID() ,
                Nombre = inserted.nombre ,
                Telefono = inserted.telefono ,
                operation = 'U'
        FROM    inserted
                JOIN dbo.datospersonales_tbl t ON inserted.id = t.ID;
    END;
GO

CREATE TRIGGER del_trigger_datospersonales_tbl ON dbo.datospersonales
    INSTEAD OF DELETE
AS
    BEGIN
        SET NOCOUNT ON;
        UPDATE  dbo.datospersonales_tbl
        SET     [user_name] = SUBSTRING(APP_NAME(),
                                        CHARINDEX(',', APP_NAME()) + 1,
                                        LEN(APP_NAME()) - CHARINDEX(',',
                                                              APP_NAME())) ,
                transaction_id = NEWID() ,
                operation = 'D'
        FROM    Deleted
                JOIN dbo.datospersonales_tbl t ON Deleted.id = t.ID;

        DELETE  FROM dbo.datospersonales_tbl
        FROM    deleted
                JOIN dbo.datospersonales_tbl t ON deleted.id = t.ID;

    END;
GO

Paso 4: Probando la solución

Conectaremos con dos cadenas de conexión diferentes simulando dos usuarios lógicos distintos.  Recuerda que vamos a trabajar para insertar-actualizar-borrar sobre la vista, nunca sobre la tabla…pero que esto es únicamente porque así lo decidimos nosotros. Para hacer esto, supongamos que nuestra aplicación introduce en el Application Name el usuario lógico que ha entrado en la aplicación. Simular esto con SSMS sería tan fácil como hacer esto:

Usuariologico1 lanza una inserción:

insert into datospersonales (nombre,telefono) 
values ('Sun Scardina',NULL)

Usuariologico2 lanza otra inserción:

insert into datospersonales (nombre,telefono) 
values ('Edris Blunk','12345678')

Si vemos lo que tenemos en la tabla con la sintaxis propia de Temporal tables:

select * 
from datospersonales_tbl
FOR system_time all

Vamos a modificar ahora el id=2 con el usuariologico2:

UPDATE datospersonales 
SET telefono = '12'
FROM dbo.datospersonales
WHERE id = 1

Vamos a ver el estado:

select * 
FROM datospersonales_tbl
FOR system_time ALL
WHERE id=1

Probemos ahora a volver a modificar el valor, pero por el usuariologico1:

UPDATE  datospersonales
SET     telefono = '99'
FROM    dbo.datospersonales
WHERE   id = 1

Veamos en qué estado queda:

select * 
FROM datospersonales_tbl
FOR system_time ALL
WHERE id=1
ORDER BY inicio desc

Finalmente vamos a lanzar la operación de borrado, esta vez desde el usuariologico2. Tras ello, lo que vamos a hacer es lanzar la consulta de reporte final, que nos muestre la evolución de la fila a lo largo del tiempo:

DELETE  dbo.datospersonales
WHERE   id = 1;

SELECT  ID ,
        Nombre AS nombre_anterior ,
        Telefono AS telefono_anterior ,
        LEAD(nombre, 1) OVER ( ORDER BY inicio ) AS nombre_nuevo ,
        LEAD(telefono, 1) OVER ( ORDER BY inicio ) AS telefono_nuevo ,
        transaction_id ,
        operation ,
        inicio ,
        fin ,
        user_name
FROM    dbo.datospersonales_historico
WHERE   id = 1
ORDER BY inicio ASC

Con la query anterior podemos ver cómo va siendo modificado el valor de cada celda en qué operación y el resultado del cambio, cosa bastante útil de cara a ver la vida de la fila a lo largo del tiempo.

Como vemos, las temporal tables son una manera bastante limpia y elegante de crear nuestra auditoria personalizada, ahorrándonos la tediosa parte de crear las tablas de historificado. Ademas aportan la ventaja de que se encargan por nosotros de mantener una sintaxis propia para hacer consultas a dicho histórico de forma nativa.

¿te ha parecido una buena idea?