La auditoría de una instancia de SQL Server o de una
base de datos de SQL Server implica el seguimiento y registro de los eventos
que se producen en el sistema. En SQL
Server puedes utilizar varios métodos de
auditoría, como se describe en
Auditoría (motor de
base de datos) -> http://msdn.microsoft.com/es-es/library/cc280526.aspx.
A partir de SQL Server 2008 Enterprise, también puedes configurar la auditoría
automática mediante SQL
Server Audit.
Paso a comentar un par de formas fáciles y personalizadas, para que podáis
crear vuestras propias auditorias. Ayudarte de procedimientos almacenados que
te permitan capturar información adicional del usuario que realiza el cambio, si
se puedes (que en ocasiones no se puede), encapsular las modificaciones en
procedimientos almacenados y en esos mismos procedimientos incluir el registro
en tablas de auditoría. Esta solución mediante procedimientos almacenados es
buena pues te da mucha flexibilidad en cuanto a grabar datos de la aplicación
que no están accesibles mediantes triggers.
De cualquier forma...la auditoría con triggers tiene una ventaja respecto a
hacerlo mediante un stored procedure y es que a través de triggers quedan
auditados todos los cambios de datos, no solo los que se hacen a través de tu
aplicación sino también los que se hacen por fuera (por ejemplo cambios hechos
con el managment studio a través de un update/delete/insert).
Programar triggers de auditoría..no es nada sencillo, más bien es una tarea tediosa.
Ya desde SQL Server 2005 tenemos los DDL Triggers. Son triggers que se
ejecutan cuando se produce la ejecución de instrucciones DDL (create, alter,
drop, ...). Hasta este momento esto no era posible, sólo podíamos crear
triggers para instrucciones DML (insert, update, delete). Con esta nueva funcionalidad
ya podemos, por ejemplo auditar las creaciones, modificaciones y borrados de
objetos en nuestra base de datos, e incluso, no permitir que se realicen estas
acciones.
La sintaxis de DDL triggers, es:
|
CREATE
TRIGGER trigger_name
ON { ALL
SERVER | DATABASE }
[ WITH
<ddl_trigger_option> [ ,...n ] ]
{ FOR |
AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement
[ ; ] [ ...n ] | EXTERNAL NAME < method specifier > [ ; ] }
|
Por ejemplo, si deseamos supervisar las creaciones y borrados
de tablas de nuestra base de datos, podemos crear desencadenadores
DDL a nivel de base de datos, mientras para que supervise las operaciones como por
ejemplo las creación de bases de
datos se debe crear un
desencadenador DDL a nivel de servidor-instancia.
Tomemos un ejemplo simple creando
una base de datos:
CREATE DATABASE [DDL_TRIGGERS_DB]
Supongamos que queremos registrar todas las creaciones de nuevas tablas y vamos a registrar todos los eventos en otra base de datos llamada DDL_Trigger_Log,
en una tabla que tiene el esquema siguiente:
CREATE TABLE [dbo].[tblDDLEventLog](
[ID] [int] IDENTITY(1,1) NOT NULL,
[EventTime] [datetime] NULL,
[EventType] [varchar](15) NULL,
[ServerName] [varchar](25) NULL,
[DatabaseName] [varchar](25) NULL,
[ObjectType] [varchar](25) NULL,
[ObjectName] [varchar](25) NULL,
[UserName] [varchar](15) NULL,
[CommandText] [varchar](max) NULL,)
Tenemos que crear un desencadenador DDL, en la bd: DDL_TRIGGERS_DB para que todos los datos del evento relevante se actualiza en la tabla anterior. El desencadenador DDL ha de ser:
CREATE TRIGGER [ddltrg_CREATE_TABLE_LOG]
ON DATABASE — Create Database DDL Trigger
FOR CREATE_TABLE — Trigger
will raise when creating a Table
AS
SET NOCOUNT ON
DECLARE @xmlEventData XML
– Capture the event data that is created
SET @xmlEventData = eventdata()
– Insert information to a EventLog table
INSERT INTO DDL_Trigger_Log.dbo.tblDDLEventLog
(
EventTime,
EventType,
ServerName,
DatabaseName,
ObjectType,
ObjectName,
UserName,
CommandText
)
SELECT REPLACE(CONVERT(VARCHAR(50), @xmlEventData.query(‘data(/EVENT_INSTANCE/PostTime)’)),
‘T’,
‘ ‘),
CONVERT(VARCHAR(15), @xmlEventData.query(‘data(/EVENT_INSTANCE/EventType)’)),
CONVERT(VARCHAR(25),
@xmlEventData.query(‘data(/EVENT_INSTANCE/ServerName)’)),
CONVERT(VARCHAR(25),
@xmlEventData.query(‘data(/EVENT_INSTANCE/DatabaseName)’)),
CONVERT(VARCHAR(25),
@xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectType)’)),
CONVERT(VARCHAR(25),
@xmlEventData.query(‘data(/EVENT_INSTANCE/ObjectName)’)),
CONVERT(VARCHAR(15),
@xmlEventData.query(‘data(/EVENT_INSTANCE/UserName)’)),
CONVERT(VARCHAR(MAX), @xmlEventData.query(‘data(/EVENT_INSTANCE/TSQLCommand/CommandText)’))
GO
Ahora, si creamos una tabla y luego hacemos una Select a la tabla tblDDLEvetnLog:
Aqui os pongo mas ejemplos y comenarios sobre Auditoría personalizada con triggers DDL
Apunte y recopilación por Norman M. Pardell
Puedes consultarme, si deseas cualquier aclaración, pregunta o sugerencia en: Contacto, contestaré tan pronto como me sea posible.
