Construcción de un sistema de auditoría con triggers en Microsoft SQL Server

2007-09-29T10:30:00.000-07:00

Construcción de un sistema de auditoría con triggers en Microsoft SQL Server

Por Mariano Minoli.

Introducción

La auditoria de sistemas es un punto importante a considerar en sistemas cliente / servidor en los cuales existe una multitud de usuarios trabajando sobre una misma fuente de datos. Cuando dichos sistemas son de pequeño porte y poseen pocos usuarios el problema podría parecer trivial, pero a medida que la envergadura del sistema crece y (sobre todo) la cantidad y variedad de usuarios es mayor, la auditoria se convierte en un punto difícil de manejar. En este artículo se intenta presentar dos soluciones posibles al problema y desarrollar una de ellas en la cual solo se utiliza transact-SQL, por último se darán las bases para automatizar la instalación de la solución presentada y algunas consideraciones y optimizaciones posibles.

Sobre auditoría

Antes que nada habría que aclarar a que nos referimos cuando hablamos de "auditoria". En este ámbito, con auditoria nos referimos al seguimiento de las operaciones realizadas por cada usuario, básicamente llevar un control de "que se hace", "quien lo hace (el Usuario)", "en donde se hace", y "cuando se hace".
El concepto no es nuevo, en los entornos mainframe suele ser llamado "el login del sistema", aquel lector que esté relacionado con la jerga entenderá que nos referimos a lo mismo, solo diferimos en la forma de implementarlo.
También podría hallarse una cierta analogía con los eventos visualizados en la sección de Seguridad del Visor de Eventos de Windows NT/2000.

Diferentes enfoques para un mismo problema

Primero buscaremos donde almacenar la información, mas allá de cómo hacerlo. Puede que el lector esté pensando en una tabla en la cual se almacenen datos que contesten a las preguntas hechas en el párrafo anterior, no vamos a contradecirlo: las dos soluciones propuestas incluyen una tabla almacenada en la base de datos. El formato de la tabla puede variar dependiendo de las necesidades específicas del entorno, nosotros proponemos la siguiente, a los efectos del desarrollo:

El script para la creación de la tabla es:

CREATE TABLE [dbo].[AUDIT] (

[id_evento] [int] IDENTITY (1, 1) NOT NULL ,

[tipo_evento] [char] (10) NOT NULL ,

[fecha] [datetime] NOT NULL ,

[descripcion] [char] (50) NULL ,

[usuario] [char] (30) NULL ,

[terminal] [char] (30) NULL ,

[aplicacion] [char] (30) NULL) ON [PRIMARY]GOALTER TABLE [dbo].[AUDIT] WITH NOCHECK ADD CONSTRAINT [PK_AUDIT] PRIMARY KEY NONCLUSTERED ( [id_evento] ) ON [PRIMARY]

Ahora que sabemos donde vamos a almacenar la información, necesitamos determinar como vamos a hacerlo. Presentamos dos opciones:

Delegar la responsabilidad en la lógica de negocio de la aplicación.
Crear disparadores en cada tabla que se desee auditar.

1er opción: Delegar la responsabilidad en la lógica de negocio de la aplicación.

Con esto nos referimos a que dentro del código encargado de hacer modificaciones a los datos, se realicen las inserciones a la tabla AUDIT, de este modo, cualquier operación generará un registro de auditoria. La ubicación del código (responsable de las inserciones) dependerá de la arquitectura que utilice el desarrollador, ya sea en una arquitectura de dos o tres capas, en donde se concentre la lógica de negocio de la aplicación, allí estará el código encargado de realizar las inserciones de auditoria.

2da opción: Crear disparadores en cada tabla que se desee auditar.

De este modo, cada tabla (que se desee auditar) tendrá un disparador (o trigger) que, cada vez que detecte una modificación en la tabla, inserte un registro en la tabla de auditoria. Si el lector desconoce el concepto de disparador, en las secciones siguientes describiremos en detalle de que se trata, por ahora veámoslo como "algo" que está "latente" en la base de datos que, al detectar una modificación en los datos de una determinada tabla, ejecuta una serie de sentencias Transact - SQL previamente configuradas.

La opción que se va a desarrollar es la segunda, la decisión no es arbitraria, obedece a una serie de ventajas que se detallan a continuación:

Primero que nada, el hecho de descargar lógica del lado del cliente suele ser PELIGROSO, en este caso las aplicaciones se hacen mas complejas de programar y mantener. Si se usan disparadores como solución se esta reutilizando código implícitamente, cualquier nueva aplicación que acceda a una tabla existente, será "automáticamente" auditada sin necesidad de escribir una sola línea de código de más.

Además, hay que tener en cuenta que si el sistema es accedido a través de Webforms (ASP.Net) no será bueno cargar al cliente de trabajo que se debería concentrar del lado del servidor.
Por último, como hemos dicho antes, es posible que existan "múltiples usuarios" accediendo de "múltiples maneras" al gestor. Muchas de estas "maneras" pueden no ser aplicaciones propietarias, un usuario por ejemplo podría acceder a través del Query Analyzer y ejecutar una sentencia delete a una tabla cualquiera, si la auditoria se encuentra en el cliente que hemos programado, nunca sabremos quien fue. El lector debe tener en cuenta que NO existe manera de modificar datos en una base de datos SQL Server y evitar que se ejecuten los disparadores que estén asociados a estos datos.

Conceptos teóricos y construcciones utilizadas

Para poder comprender correctamente las secciones siguientes, es necesario tener claros algunos conceptos de teoría de bases de datos y la sintaxis de algunas sentencias:

Desencadenador (trigger)

Podría definirse como un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando un usuario intenta modificar datos sobre una tabla determinada. Los disparadores se almacenan en la base de datos en forma similar a los procedimientos almacenados, sin embargo NUNCA son ejecutados explícitamente por los usuarios, sólo se disparan cuando el gestor detecta una modificación.

Existen tres tipos de disparadores, uno para cada tipo de actualización:
Eliminación
Inserción
Modificación

La sintaxis que utilizaremos durante este desarrollo para la creación de disparadores es la siguiente:

CREATE TRIGGER nombre_trigger ON tabla

[WITH ENCRYPTION]

{ {FOR { [DELETE] [,] [INSERT] [,] [UPDATE] }

AS sentencia_sql [...n]}

Cabe aclarar que existen otras opciones adicionales que no son utilizadas durante este desarrollo.

Sentencia insert utilizada con un select

Debido a que no es la sintaxis más popular de un insert, nos pareció interesante explicarle que se va a utilizar la cláusula de la siguiente manera:

Insert into TABLA _ DESTINO

Select * from TABLA _ ORIGEN

Esta sentencia inserta todos los registros obtenidos como el resultado de la consulta a la TABLA_ORIGEN en la TABLA_DESTINO

Execute

Puede ser utilizado para ejecutar un procedimiento almacenado o (como en nuestro caso) para ejecutar un conjunto de sentencias Transact-SQL que es pasada como un string, esto nos permite "armar" la sentencia concatenando partes fijas con variables que van tomando distintos valores.

Las funciones getdate(), host_name(), SYSTEM_USER, APP_NAME

Usamos estas funciones para "capturar" valores que nos interesa almacenar en la tabla AUDIT. Sus significados son:

Getdate(): Fecha-hora actual del sistema.
SYSTEM_USER: Nombre del usuario que envía una sentencia al servidor.
host_name(): Nombre de la maquina cliente desde donde se envía la sentencia.
APP_NAME: Nombre de la aplicación cliente a través de la cual se envía la sentencia.

Desarrollo

Con lo que se ha explicado hasta el momento, poco nos resta para implementar el sistema de auditoria, simplemente hay que crear un disparador por cada tabla que se quiera auditar, el disparador tiene que ejecutar un insert a la tabla AUDIT, veámoslo con un ejemplo. Supongamos una tabla CLIENTES, que presenta la siguiente estructura (muy simplificada, solo para fines demostrativos):

La sentencia de creación de esta tabla es:

CREATE TABLE [dbo].[CLIENTES] (

[id_cliente] [int] NOT NULL ,

[Nombre] [char] (50) NOT NULL)

ON [PRIMARY]

Para esta tabla las sentencias de creación de los disparadores son las siguientes:

CREATE TRIGGER AUDITdel ON CLIENTES

FOR DELETE

Insert into AUDIT select "Delete", getdate(), "Eliminacion de un registro", SYSTEM_USER, host_name(),APP_NAME()

CREATE TRIGGER AUDITins ON CLIENTES

FOR INSERT

insert into AUDITselect "Insert", getdate(), "Insercion de un registro", SYSTEM_USER, host_name(),APP_NAME()

CREATE TRIGGER AUDITdel ON CLIENTES

FOR UPDATE

insert into AUDITselect "Update", getdate(), "Modificacion de un registro", SYSTEM_USER, host_name(),APP_NAME()

Cabe aclarar que lo mismo se podría hacer de una manera más "amigable" desde el Administrador Corporativo (Enterprise Manager).

Habiendo creado los tres disparadores sobre la tabla clientes, intente hacer cualquier operación que modifique los datos de la misma, por ejemplo podría ejecutar las tres sentencias siguientes:

Insert into CLIENTES values (2356, "Juan Perez")

Update CLIENTES set Nombre = "Ricardo Perez"

where id_cliente = 2356

Delete CLIENTES where id_cliente = 2356

Si analiza la tabla AUDIT verá que las sentencias ejecutadas accionaron los disparadores que se habían creado. Éstos insertaron un registro por cada modificación efectuada a la tabla. Verifique con la siguiente consulta:

select * from AUDIT

Optimización 1: Creación de un Procedimiento Almacenado que automatice la instalación del sistema

Con lo visto hasta el momento, ya esta en condiciones de crear sus propios disparadores sobre cada tabla que desee auditar, ahora suponga que quiere auditar con este método la base de datos corporativa, en la cual se alojan datos referentes a todos los subsistemas de la organización. Suponga que las tablas a auditar son 300.

¿No le interesa la idea de automatizar la instalación de los disparadores?

Lo que se presenta a continuación es un procedimiento almacenado que encapsula la creación de los tres disparadores para una tabla determinada que es pasada como parámetro de entrada:

CREATE PROCEDURE sp_instalarTrigger

@tabla as char(128)

-- Aseguramos que el parametro de entrada esté libre de espacios

set @tabla = ltrim(rtrim(@tabla))

-- Creacion del trigger para ELIMINACION

EXECUTE ('CREATE TRIGGER AUDITdel_' + @tabla +' ON ' + @tabla +' FOR DELETE AS Insert into AUDITselect "Delete", getdate(), "Eliminacion de un registro", SYSTEM_USER, host_name(),APP_NAME()')

-- Creacion del trigger para INSERCIONEXECUTE ('CREATE TRIGGER AUDITins_' + @tabla +' ON ' + @tabla +' FOR INSERT AS Insert into AUDITselect "Insert", getdate(), "Insercion de un registro", SYSTEM_USER, host_name(),APP_NAME()')

-- Creacion del trigger para MODIFICACIONEXECUTE ('CREATE TRIGGER AUDITupd_' + @tabla +' ON ' + @tabla +' FOR UPDATE AS Insert into AUDITselect "Update", getdate(), "Modificacion de un registro", SYSTEM_USER, host_name(),APP_NAME()')

Por ejemplo, para nuestra tabla sería:

Exec sp_instalarTrigger "CLIENTES"

Un segundo paso para lograr una instalación totalmente automática podría ser crear otro procedimiento almacenado que:
Cargue un cursor con todas las tablas de usuario a través de un select de la tabla sysobjects (con la condición de que el campo xtype sea "U").
Luego ejecute el procedimiento almacenado antes creado por cada registro dentro del cursor.
Sin embargo debe ser cuidadoso con este enfoque de "instalación masiva" porque la sobrecarga de procesamiento que existirá en el servidor a partir de la instalación de los disparadores podría no ser aceptable (o deseable) en todas la tablas, mas adelante hablaremos de ello.
Optimización 2: Personalización del mensaje grabado (tablas inserted y deleted)Si analiza la tabla AUDIT que se ha propuesto verá que uno de los campos es "descripción". Hasta el momento hemos estado insertando una cadena fija que presenta una suerte de descripción acerca del evento que dispara el disparador, en realidad ese campo está pensado para guardar una descripción detallada del evento ocurrido, ¿cuán detallada?, todo lo detallada que el usuario considere necesario y esté dispuesto a programar.

A continuación desarrollamos un ejemplo práctico para el disparador que controla la eliminación, en donde utilizamos la tabla "deleted", ésta es una tabla temporal a la cual se puede tener acceso dentro de un disparador, de este modo podemos recuperar los datos que están siendo eliminados y que han disparado la ejecución del disparador (nótese que también se podría utilizar la tabla temporal inserted accesible desde los disparadores para inserción).
Lo que vamos a hacer es recuperar el nombre del cliente que se está eliminando y lo vamos a guardar en el campo descripción de la tabla AUDIT.
De este modo reformulamos la creación del disparador de la siguiente manera:

-- Primero eliminamos el trigger que se habia creado anteriormente

if exists (select * from sysobjects where id =object_id(N'[dbo].[AUDITdel_CLIENTES]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)

drop trigger [dbo].[AUDITdel_CLIENTES]

-- Luego creamos el trigger para ELIMINACION con el mensaje personalizado

CREATE TRIGGER AUDITdel_CLIENTES ON CLIENTES FOR DELETE

Insert into AUDIT select "Delete",getdate(), (select "Nombre eliminado: " + convert(char(15),Nombre) from deleted) ,SYSTEM_USER, host_name(),APP_NAME()

Con este enfoque se podrían almacenar todos los datos antes de una eliminación para poder reconstruir un registro si es necesario, aunque si su aplicación hace eliminaciones masivas (es decir de mas de dos registros), debería replantear la inserción a la tabla AUDIT recorriendo, por ejemplo, un cursor armado con una consulta a la tabla deleted.Aquí cabe una aclaración: el código Transact-SQL que se encuentra dentro del trigger puede estar compuesto por bucles, condicionales, casi cualquier construcción Transact-SQL válida, sin embargo deberá tener en cuenta que toda la demora que introduzca este código impactará directamente en cada una de las operaciones que los usuarios hagan sobre los datos.

Encriptación de los disparadores

Una consideración que podría ser importante es la de encriptar el código de los disparadores creados, de este modo evitará que se conozca cómo y donde se está almacenando la información de auditoria, mas allá de que luego restrinja el acceso a la tabla AUDIT. El código debe ser encriptado al momento de la creación del disparador a través de la cláusula "WITH ENCRYPTION", para nuestro disparador de eliminación sería:

-- Primero eliminamos el trigger que se había creado anteriormente

if exists (select * from sysobjects where id =object_id(N'[dbo].[AUDITdel_CLIENTES]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)drop trigger [dbo].[AUDITdel_CLIENTES]

-- Luego creamos el trigger para ELIMINACION con el mensaje personalizado y ENCRIPTADO

CREATE TRIGGER AUDITdel_CLIENTES WITH ENCRYPTION ON CLIENTES FOR DELETE

Insert into AUDIT select "Delete",Getdate(), (select "Nombre eliminado: " + convert(char(15),Nombre) from deleted) ,SYSTEM_USER, host_name(),APP_NAME()

Conclusiones

Hemos visto como, a partir de unas simples sentencias de Transact-SQL , se puede crear todo un sistema que realice un seguimiento muy detallado de las operaciones de los usuarios sobre los datos. Además vimos que su diseño es flexible y puede ser personalizado para necesidades específicas.
Si desea, puede completar el desarrollo creando una interfaz gráfica que acceda a la tabla AUDIT y permita hacer consultas con filtros configurables por el usuario, emitir reportes, etc.

Se viene SQL Server 2008 !

2007-08-31T14:46:00.000-07:00

Se hemos visto últimamente en distintos lugares, la versión 2008 de SQL Server estará disponible para el primer cuatrimestre del año que viene.

Aqui van algunas urls relacionadas al tema:
http://www.microsoft.com/sql/prodinfo/futureversion/default.mspx
https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

Esperamos con ansiedad la nueva versión !

.:: Mariano Minoli ::.

Construcción de un sistema de auditoría con triggers en Microsoft SQL Server

Se viene SQL Server 2008 !