4 comentarios

Trigger para auditar tu base de datos SQL Server


Aqui les dejo un ejemplo de como hacer un trigger que sirve para auditar tus bases de datos de SQL Server.

Navegando me encontre con una excelente solucion hecha por Jon Galloway, en la cual consiste en crear un solo script y que este se encarge de crear los triggers necesarios para cada tabla de la base de datos seleccionada, la diferencia con SQLTableHistory es que con nuestra herramienta podemos seleccionar las tablas que queremos que se le haga su respectiva auditoria y con la solucion presentada en este blog.

Con esta informacion podemos hacer reportes de una manera sencilla tales como:

  • Cuales tablas han sufridos cambios recientemente.
  • Cuales tablas no sufrieron cambios el pasado año.
  • Cuales tablas jamaz han sufrido cambios.
  • Mostrar todos los cambios a las tablas por un usuario y periodo especifico.
  • Mostrar las tablas mas activas en un determinado periodo.Con estas herramientas es posible volver al estado anterior de la tabla y volver en un punto especifico, claro esta ya depende de las nuevas extensiones que ustedes le hagan.Aqui os dejo el script completo que crea la tabla de auditoria y corre el script para crear los triggers a todas las tablas de la base de datos.

USE MYDATABASE–Ponerle el nombre de la base de datos que va hacer auditada

GO

IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME= ‘Audit’)

CREATE TABLE Audit

(

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

Type char(1),

TableName varchar(128),

PrimaryKeyField varchar(1000),

PrimaryKeyValue varchar(1000),

FieldName varchar(128),

OldValue varchar(1000),

NewValue varchar(1000),

UpdateDate datetime DEFAULT (GetDate()),

UserNamevarchar(128)

)

GO

DECLARE @sql varchar(8000), @TABLE_NAMEsysname

SET NOCOUNT ON

SELECT @TABLE_NAME= MIN(TABLE_NAME)

FROM INFORMATION_SCHEMA.Tables

WHERE

TABLE_TYPE= ‘BASE TABLE’

AND TABLE_NAME!= ‘sysdiagrams’

AND TABLE_NAME!= ‘Audit’

WHILE @TABLE_NAMEIS NOT NULL

BEGIN

EXEC(‘IF OBJECT_ID (”’ + @TABLE_NAME+ ‘_ChangeTracking”, ”TR”) IS NOT NULL DROP TRIGGER ‘ + @TABLE_NAME+ ‘_ChangeTracking’)

SELECT @sql =

create trigger ‘ + @TABLE_NAME+ ‘_ChangeTracking on ‘ + @TABLE_NAME+ ‘ for insert, update, delete

as

declare @bit int ,

@field int ,

@maxfield int ,

@char int ,

@fieldname varchar(128) ,

@TableName varchar(128) ,

@PKCols varchar(1000) ,

@sql varchar(2000),

@UpdateDate varchar(21) ,

@UserName varchar(128) ,

@Type char(1) ,

@PKFieldSelect varchar(1000),

@PKValueSelect varchar(1000)

select @TableName = ”’ + @TABLE_NAME+ ”’

— date and user

select @UserName = system_user ,

@UpdateDate = convert(varchar(8), getdate(), 112) + ” ” + convert(varchar(12), getdate(), 114)

— Action

if exists (select * from inserted)

if exists (select * from deleted)

select @Type = ”U”

else

select @Type = ”I”

else

select @Type = ”D”

— get list of columns

select * into #ins from inserted

select * into #del from deleted

— Get primary key columns for full outer join

select@PKCols = coalesce(@PKCols + ” and”, ” on”) + ” i.” + c.COLUMN_NAME + ” = d.” + c.COLUMN_NAME

fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

andCONSTRAINT_TYPE = ”PRIMARY KEY”

andc.TABLE_NAME = pk.TABLE_NAME

andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

— Get primary key fields select for insert

select @PKFieldSelect = coalesce(@PKFieldSelect+”+”,””) + ”””” + COLUMN_NAME + ””””

fromINFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where pk.TABLE_NAME = @TableName

andCONSTRAINT_TYPE = ”PRIMARY KEY”

andc.TABLE_NAME = pk.TABLE_NAME

andc.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

select @PKValueSelect = coalesce(@PKValueSelect+”+”,””) + ”convert(varchar(100), coalesce(i.” + COLUMN_NAME + ”,d.” + COLUMN_NAME + ”))”

from INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk ,

INFORMATION_SCHEMA.KEY_COLUMN_USAGE c

where  pk.TABLE_NAME = @TableName

and CONSTRAINT_TYPE = ”PRIMARY KEY”

and c.TABLE_NAME = pk.TABLE_NAME

and c.CONSTRAINT_NAME = pk.CONSTRAINT_NAME

if @PKCols is null

begin

raiserror(”no PK on table %s”, 16, -1, @TableName)

return

end

select @field = 0, @maxfield = max(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

while @field < @maxfield

begin

select @field = min(ORDINAL_POSITION) from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION > @field

select @bit = (@field – 1 )% 8 + 1

select @bit = power(2,@bit – 1)

select @char = ((@field – 1) / 8) + 1

if substring(COLUMNS_UPDATED(),@char, 1) & @bit > 0 or @Type in (”I”,”D”)

begin

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName and ORDINAL_POSITION = @field

select @sql = ”insert Audit (Type, TableName, PrimaryKeyField, PrimaryKeyValue, FieldName, OldValue, NewValue, UpdateDate, UserName)”

select @sql = @sql + ” select ””” + @Type + ””””

select @sql = @sql + ”,””” + @TableName + ””””

select @sql = @sql + ”,” + @PKFieldSelect

select @sql = @sql + ”,” + @PKValueSelect

select @sql = @sql + ”,””” + @fieldname + ””””

select @sql = @sql + ”,convert(varchar(1000),d.” + @fieldname + ”)”

select @sql = @sql + ”,convert(varchar(1000),i.” + @fieldname + ”)”

select @sql = @sql + ”,””” + @UpdateDate + ””””

select @sql = @sql + ”,””” + @UserName + ””””

select @sql = @sql + ” from #ins i full outer join #del d”

select @sql = @sql + @PKCols

select @sql = @sql + ” where i.” + @fieldname + ” <> d.” + @fieldname

select @sql = @sql + ” or (i.” + @fieldname + ” is null and  d.” + @fieldname + ” is not null)”

select @sql = @sql + ” or (i.” + @fieldname + ” is not null and  d.” + @fieldname + ” is null)”

exec (@sql)

end

end

SELECT @sql

EXEC(@sql)

SELECT @TABLE_NAME= MIN(TABLE_NAME) FROM INFORMATION_SCHEMA.Tables

WHERE TABLE_NAME> @TABLE_NAME

AND TABLE_TYPE= ‘BASE TABLE’

AND TABLE_NAME!= ‘sysdiagrams’

AND TABLE_NAME!= ‘Audit’

END

Para mas informacion:

Jon Galloway SQL Server 2005’s DDL triggers Nigel Rivett’s SQL Server Auditing triggers

Fuente:  http://geeks.ms/blogs/ezavala/archive/2008/03/11/agregar-un-simple-trigger-para-auditar-tu-base-de-datos-sql-server.aspx

Espero les sirva,  Saludos

Anuncios

4 comentarios el “Trigger para auditar tu base de datos SQL Server

  1. Pretty section of content. I just stumbled upon
    your weblog and in accession capital to assert that I acquire in fact enjoyed account your blog posts.
    Anyway I will be subscribing to your augment and even I achievement you access consistently rapidly.

  2. En la parte donde se resta @field – 1 me genera error al igual que en las siguientes, uso SQL SERVER 2005

Si te sirvio o te gusto el post, deja un comentario, o vota por el blog, esto nos ayuda a seguir creciendo, Gracias

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: