Auditoria de DDL a nível banco e instância

Olá pessoal, vamos falar de auditoria DDL no SQL Server. A auditoria envolve a coleta e acompanhamento de eventos ocorridos em bases de dados. Nunca se sabe se um dia te pedirão a informação de quem apagou aquela tabela, stored procedure,banco de dados, etc. Vamos à solução:

  • Trigger DDL de banco de dados e Trigger de Instância.

Minha recomendação é que você crie no seu ambiente um banco de dados específico para auditoria. A preparação da auditoria de criação e alteração em objetos de banco de dados é realizada criando a trigger [tr_ddl_db] que irá capturar eventos realizados em stored procedures, functions, views, tabelas, índices, entre outros.

Passo a Passo:

  • Cria a tabela abaixo no seu banco de auditoria:

USE BD_AUDITORIA
GO
CREATE TABLE dbo.TD_AUDITORIA_DDL(
 IDAuditoriaDDL int IDENTITY(1,1) NOT NULL,
 TipoEvento varchar(30) NULL,
 Login varchar(100) NULL,
 Usuario varchar(100) NULL,
 Banco varchar(100) NULL,
 Comando varchar(max) NULL,
 DataEvento datetime NULL,
 Evento xml NULL,
 NivelAuditoria char(1) NOT NULL) 
--Cria a PK e o índice Clustered
ALTER TABLE dbo.TD_AUDITORIA_DDL ADD CONSTRAINT PK_AuditoriaDDL PRIMARY KEY CLUSTERED (IDAuditoriaDDL)
ALTER TABLE dbo.TD_AUDITORIA_DDL ADD DEFAULT (getdate()) FOR DataEvento
  • Cria a trigger DDL abaixo em todos os bancos de dados que serão auditados:

USE BancoAuditado 
GO
CREATE TRIGGER TR_DDL_DB ON DATABASE
 FOR DDL_DATABASE_LEVEL_EVENTS 
 AS 
 DECLARE @data xml 
 SET @data = eventdata() 
 INSERT INTO BD_AUDITORIA.dbo.TD_AUDITORIA_DDL
 (TipoEvento, Login, Usuario, Banco, Comando, Evento, NivelAuditoria )
 VALUES (data.value('(/EVENT_INSTANCE/EventType)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/LoginName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/UserName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/DatabaseName)1', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)1', 'nvarchar(2000)') 
 ,@data
 ,'D')

A preparação da auditoria de instância é realizada criando a trigger [tr_ddl_audit] que irá capturar eventos de instância como alteração/criação de logins e de banco de dados. A trigger deve ser criada uma única vez na instância.


CREATE TRIGGER [TR_DDL_AUDIT]
ON ALL SERVER 
FOR 
 ddl_endpoint_events, 
 ddl_login_events, 
 ddl_gdr_server_events, 
 ddl_authorization_server_events,
 create_database,
 alter_database,
 drop_database
AS 
 DECLARE @data xml 
 SET @data = eventdata() 
 INSERT INTO BD_AUDITORIA.dbo.TD_AUDITORIA_DDL
 (TipoEvento, Login, Usuario, Banco, Comando, Evento, NivelAuditoria) 
 VALUES (
 @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/UserName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(100)') 
 ,@data.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(2000)') 
 ,@data
 ,'S')

Após a criação dos objetos envolvidos se torna necessário ainda à execução do script abaixo para evitar erros de gravação em casos específicos.

USE BD_AUDITORIA
GO
EXECUTE sp_grantdbaccess guest
GO
GRANT INSERT ON dbo.TD_AUDITORIA_DDL TO public;

E pronto! A coleta de dados para auditoria será realizada de forma automática e você terá todas as alterações DDL armazenados na tabela BD_AUDITORIA.dbo.TD_AUDITORIA_DDL.

Até a próxima pessoal!!!

=)

 

Anúncios

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s