Gestión de la Seguridad de bases de datos SQL Server - Auditoría de Accesos



Siempre es bueno gestionar la Seguridad de las bases de datos, para ese efecto debemos tener como mínimo algún mecanismo de auditoría. Así podríamos comenzar por saber quiénes acceden al servidor, qué hacen, cuándo, etc.

Luego lo primero que hay que hacer es crear una tabla para que contenga la información de auditoría. Esta tabla la llamaremos "tabspwho2"

CREATE TABLE [dbo].[tabspwho2] (
[spid1] [smallint] NULL ,
[status] [varchar] (30) NULL ,
[loginame] [varchar] (128) NULL ,
[hostname] [varchar] (128) NULL ,
[blk] [varchar] (5) NULL ,
[dbname] [varchar] (128) NULL ,
[command] [nvarchar] (16) NULL ,
[cputime] [int] NULL ,
[diskio] [int] NULL ,
[lastbatch] [varchar] (20) NULL ,
[programname] [varchar] (128) NULL ,
[spid2] [smallint] NULL ,
[muestra] [datetime] NULL ,
[EventType] [nvarchar] (50) NULL ,
[Parameters] [int] NULL ,
[EventInfo] [nvarchar] (255) NULL
) ON [PRIMARY]
GO

CREATE CLUSTERED INDEX [IX_tabspwho2] ON [dbo].[tabspwho2]([muestra]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tabspwho2] ADD
CONSTRAINT [DF_tabspwho2_muestra] DEFAULT (getdate()) FOR [muestra]
GO

Luego creamos un procedimiento almacenado para capturar los accesos:

create procedure capturalogins
as
begin tran


declare @sql varchar(1000)

declare @spidx as smallint,
@statusx as varchar(30) ,
@loginamex as varchar(128) ,
@hostnamex as varchar(128) ,
@blkx as varchar (5) ,
@dbnamex as varchar (128) ,
@commandx as nvarchar (16) ,
@cputimex as int,
@diskiox as int,
@lastbatchx as varchar (20) ,
@programnamex as varchar(128) ,
@spid2x as smallint ,
@muestrax as datetime,
@eventtypex as nvarchar(50),
@parametersx as int,
@eventinfox as nvarchar(255)

CREATE TABLE #tablapaso (
[spid1] [smallint] NULL ,
[status] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[loginame] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[hostname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[blk] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dbname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[command] [nvarchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cputime] [int] NULL ,
[diskio] [int] NULL ,
[lastbatch] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[programname] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[spid2] [smallint] NULL ,
[muestra] [datetime] NULL CONSTRAINT [DF_tablapaso_muestra] DEFAULT (getdate())
) ON [PRIMARY]

create table #tablapaso2(
[EventType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Parameters] [int] NULL ,
[EventInfo] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]


insert into

#tablapaso(spid1,status,loginame,hostname,blk,dbname,command,cputime,diskio,lastbatch,programname,spid2) exec sp_who2

declare micursor CURSOR for select * from #tablapaso
open micursor
fetch next from micursor
into @spidx,@statusx,@loginamex,@hostnamex,@blkx,@dbnamex,@commandx,@cputimex,@diskiox,@lastbatchx,@programnamex,@spid2x,@muestrax

while @@fetch_status = 0
begin

set @sql='dbcc inputbuffer('+cast(@spidx as nvarchar(6))+') WITH NO_INFOMSGS;'
insert into #tablapaso2(eventtype, parameters, eventinfo) exec (@sql)
select @eventtypex=eventtype, @parametersx=parameters, @eventinfox=eventinfo
from #tablapaso2

insert into tabspwho2(spid1,status,loginame,hostname,blk,dbname,command,cputime,diskio,lastbatch,programname,spid2,muestra,eventtype,parameters,eventinfo)
values (@spidx,@statusx,@loginamex,@hostnamex,@blkx,@dbnamex,@commandx,@cputimex,@diskiox,@lastbatchx,@programnamex,@spid2x,@muestrax,@eventtypex,@parametersx,@eventinfox)

fetch next from micursor
into @spidx,@statusx,@loginamex,@hostnamex,@blkx,@dbnamex,@commandx,@cputimex,@diskiox,@lastbatchx,@programnamex,@spid2x,@muestrax

end
close micursor
deallocate micursor
commit tran

drop table #tablapaso
drop table #tablapaso2
go

y para poblar la tabla creamos un job que ejecute el procedimiento almacenado:

exec capturalogins

NECESITAS MÁS AYUDA CON ESTO? ¿TIENES DUDAS? Entonces abónate con el servicio de soporte en línea (Chat) o deja una donación, gustoso te atenderemos. (haz click aquí para ir al link de subscripción del soporte chat)