Auditoria de Base de Datos

Auditoria de Base de Datos

  23:01:00    Vladimir

---

Entrando un poco en tema de Base de Datos, al momento de generar mis tablas y colocarlas en producción se produjo una serie de preguntas:

1. ¿Como saber si un usuario modifico un registro?
2. ¿Cual fue el valor del registro antes de la modificación?
3. ¿Que usuario y desde donde realizó la modificación?

Para lo cual se me vino a la mente un script que tenia generado para realizar la Auditoria de una base de datos, es decir; registrar el usuario, programa, fecha, valor anterior y valor actual de el/los campos que fueron modificados.

Creación de tablas

Para este ejercicio lo primero que necesitamos son las tablas donde se va a almacenar la información; yo me decidí por tener una cabecera-detalle. Ya que en la cabecera voy a almacenar los datos de quien/como modificó la información; así como la cantidad de registros afectados y en el detalle los valores modificados.

Aqui el script de la Cabecera:

CREATE TABLE [dbo].[Cabecera_Auditoria](
	[CAB_AUD_CODIGO] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CAB_AUD_FECHA] [datetime] NOT NULL,
	[CAB_AUD_USUARIO] [numeric](18, 0) NOT NULL,
	[CAB_AUD_USUA_APLI] [varchar](500) NOT NULL,
	[CAB_AUD_PROGRAMA] [varchar](1000) NOT NULL,
	[CAB_AUD_FILASAFECTADAS] [numeric](18, 0) NOT NULL,
 CONSTRAINT [PK_Cabecera_Auditoria] PRIMARY KEY CLUSTERED 
(
	[CAB_AUD_CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Este es el script del detalle

CREATE TABLE [dbo].[Detalle Auditoria](
	[DET_AUD_CODIGO] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
	[CAB_AUD_CODIGO] [numeric](18, 0) NOT NULL,
	[DET_AUD_VALOR_ANTERIOR] [varchar](max) NOT NULL,
	[DET_AUD_VALOR_ACTUAL] [varchar](max) NOT NULL,
	[DET_AUT_TABLA_MODIFICADA] [varchar](300) NOT NULL,
	[DET_AUT_CLAVE_PRINCIPAL] [numeric](18, 0) NOT NULL,
 CONSTRAINT [PK_Detalle Auditoria] PRIMARY KEY CLUSTERED 
(
	[DET_AUD_CODIGO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Nuestras tablas nos quedarían de esta forma:

Indistintamente de como tengan implementada su Base de Datos, estas dos tablas nos servirán en cualquier caso que queramos realizar una Auditoría. En mi caso tengo una tabla de usuarios en la que, los dos primeros registros corresponden al Super Usuario y un usuario adicional denominado Sistemas; estos usuarios van a ser muy importantes a la hora de realizar la Auditoria.

Cabe señalar que este post se va a enfocar solo a actualización de información en las tablas, pero también puede ser aplicado para nuevos registros y eliminación de registros, en una próxima oportunidad estaré escribiendo sobre estos temas.

Creación de Triggers

Para este ejemplo voy a generar triggers, para que, una vez se realice la actualización de algún campo en nuestra tabla este se dispare e inserte la información necesaria. De mi tabla FE_EMPRESA solo voy a necesitar que ciertos campos sean auditados:

• EM_RAZOSOCI
  
• EM_DIRECCIO
  
• EM_REPRLEGA
  
• EM_CEDRUC

Siendo este el caso dentro del trigger yo tengo la función UPDATE que me permitirá saber si los campos que se indicó antes fueron modificados.

CREATE TRIGGER trg_Update_Empresa
   ON  FE_EMPRESA
   AFTER UPDATE
AS 
BEGIN
	DECLARE @IDENTIDAD NUMERIC
	INSERT INTO [Cabecera_Auditoria]
	SELECT TOP 1 
		GETDATE(), 
		ISNULL(I.USU_CODIGO,3),
		ISNULL(USER_NAME(),'N/A'), 
		ISNULL(APP_NAME(),'N/A'), 
		@@ROWCOUNT
	FROM inserted i 
	INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
	
	SET @IDENTIDAD = @@IDENTITY
	IF UPDATE([EM_RAZOSOCI])
		BEGIN
			INSERT INTO [Detalle Auditoria]
			SELECT
				@IDENTIDAD,
				d.EM_RAZOSOCI,
				i.EM_RAZOSOCI,
				'FE_EMPRESA',
				i.EM_CODIGO
			FROM inserted i
			INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
			WHERE d.[EM_RAZOSOCI] <> i.[EM_RAZOSOCI]
			or (d.[EM_RAZOSOCI] <> '' and i.[EM_RAZOSOCI] = '')
			or (d.[EM_RAZOSOCI] is not null and i.[EM_RAZOSOCI] is null)
			
			SELECT
				@IDENTIDAD,
				d.EM_RAZOSOCI,
				i.EM_RAZOSOCI,
				'FE_EMPRESA',
				i.EM_CODIGO
			FROM inserted i
			INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
			WHERE d.[EM_RAZOSOCI] <> i.[EM_RAZOSOCI]
			or (d.[EM_RAZOSOCI] <> '' and i.[EM_RAZOSOCI] = '')
			or (d.[EM_RAZOSOCI] is not null and i.[EM_RAZOSOCI] is null)

		END
	IF UPDATE([EM_DIRECCIO])
		BEGIN
			INSERT INTO [Detalle Auditoria]
			SELECT
				@IDENTIDAD,
				d.[EM_DIRECCIO],
				i.[EM_DIRECCIO],
				'FE_EMPRESA',
				i.EM_CODIGO
			FROM inserted i
			INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
			WHERE d.[EM_DIRECCIO] <> i.[EM_DIRECCIO]
			or (d.[EM_DIRECCIO] <> '' and i.[EM_DIRECCIO] = '')
			or (d.[EM_DIRECCIO] is not null and i.[EM_DIRECCIO] is null)
		END
	IF UPDATE([EM_REPRLEGA])
		BEGIN
			INSERT INTO [Detalle Auditoria]
			SELECT
				@IDENTIDAD,
				d.[EM_REPRLEGA],
				i.[EM_REPRLEGA],
				'FE_EMPRESA',
				i.EM_CODIGO
			FROM inserted i
			INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
			WHERE d.[EM_REPRLEGA] <> i.[EM_REPRLEGA]
			or (d.[EM_REPRLEGA] <> '' and i.[EM_REPRLEGA] = '')
			or (d.[EM_REPRLEGA] is not null and i.[EM_REPRLEGA] is null)
		END
	IF UPDATE([EM_CEDRUC])
		BEGIN
			INSERT INTO [Detalle Auditoria]
			SELECT
				@IDENTIDAD,
				d.[EM_CEDRUC],
				i.[EM_CEDRUC],
				'FE_EMPRESA',
				i.EM_CODIGO
			FROM inserted i
			INNER JOIN deleted d ON i.EM_CODIGO = d.EM_CODIGO
			WHERE d.[EM_CEDRUC] <> i.[EM_CEDRUC]
			or (d.[EM_CEDRUC] <> '' and i.[EM_CEDRUC] = '')
			or (d.[EM_CEDRUC] is not null and i.[EM_CEDRUC] is null)
		END
END
GO

Prueba de Auditoria

Una vez creado el trigger vamos a realizar una prueba, tengo la informacion en la tabla FE_EMPRESA

Y ahora voy a realizar una modificacion de la Razon Social de Vladimir Miranda a Carlos Miranda

UPDATE FE_EMPRESA SET EM_RAZOSOCI = 'Carlos Miranda' where EM_CODIGO = 1

En cuanto ejecute el script en el SQL nos mostrará la información de las filas afectadas

Si revisamos las tablas que creamos para la auditoria podremos encontrar la información

Y este sería el final, no queda mas que aplicar esto a las tablas que necesitemos.