SQL Server 2012: User-Defined Audit Event

Olá pessoal,

Atualmente possuimos no SQL Server 2008 assim como no 2012 a possibilidade de criarmos auditorias a nivel de instancia e a nivel de base de dados, porem até o 2008 nao tinhamos a possibilidade de criar nossas proprias Auditorias personalizadas assim como as server roles. Foi por isso então que no SQL Server 2012 foi introduzido a possibilidade de voce mesmo criar suas proprias auditorias.

Mas como faço isso? Abaixo vamos mostrar o passo a passo dessa nova funcionalidade.

Passo 1: Necessario criar a Auditoria para informar local onde vou salvar meus arquivos de auditoria e demais configurações.

image

Passo 2: Criar uma especificação a nivel de servidor utilizando o tipo de auditoria “USER_DEFINED_AUDIT_GROUP”.

image

Depois disso ativamos primeiramente a especificação dessa auditoria Security –> Server Audit Specifications e depois em Security –> Audit e ativamos as mesmas.

Passo 3: Para salvar nossas auditorias personalizadas existe uma nova procedure chamada sp_audit_write (colocar referencia).

exec master.sys.sp_audit_write @user_defined_event_id = 27,
@succeeded = 0,
@user_defined_information = N’Algum texto de exemplo’

Feito isso podemos consultar nosso arquivo de auditoria para nos certificarmos que nossa informação foi realmente auditada.

select event_time,succeeded =
CASE
WHEN cast(succeeded as varchar(20)) = 1 THEN ‘Ação concluida com sucesso’
WHEN cast(succeeded as varchar(20))= 0 THEN ‘Ação não foi concluida com sucesso’
END ,server_principal_name,database_principal_name,server_instance_name,
database_name,[object_name],user_defined_event_id,user_defined_information
from fn_get_audit_file(‘C:\Audit\*’,null,null)

Confesso que gostei bastante dessa nova funcionalidade, pois ela nos dá uma infinidade de novas possibilidades de auditorias.

Segue um pequeno exemplo onde pode ser totalmente util.

create database UserAuditDefined
go
use UserAuditDefined
go
create table tblPasswords ( id int identity, UsrLogin varchar(30), UsrPassword varbinary(128))
go
create unique index ix_UsrLogin on tblPasswords(UsrLogin)
go
create procedure NewLogin
@NewLogin nvarchar(30),@NewPassword nvarchar(128)
as
BEGIN TRY
declare @ConvertPassword varbinary(128)
declare @InformacaoAdicional nvarchar(500)
set @ConvertPassword = (select PWDENCRYPT(@NewPassword))

insert into tblPasswords
values (@NewLogin,@ConvertPassword)

select @InformacaoAdicional = N’Usuario ‘ + QUOTENAME(@NewLogin) + ‘ cadastrado com sucesso’;
EXEC sys.sp_audit_write 1, 1, @InformacaoAdicional
END TRY
BEGIN CATCH
select @InformacaoAdicional = N’Não foi possivel cadastrar o usuario ‘ + QUOTENAME(@NewLogin) + ‘. Motivo: ‘ + ERROR_MESSAGE();
EXEC sys.sp_audit_write 1, 0, @InformacaoAdicional
select ERROR_LINE(),ERROR_NUMBER(),ERROR_PROCEDURE(),ERROR_SEVERITY(),ERROR_STATE(),ERROR_MESSAGE()
END CATCH

Vamos rodar um teste

exec NewLogin N’marcos.freccia’,N’SQLServer2012′

E executar a consulta novamente.

select succeeded =
CASE
WHEN cast(succeeded as varchar(20)) = 1 THEN ‘Ação concluida com sucesso’
WHEN cast(succeeded as varchar(20))= 0 THEN ‘Ação não foi concluida com sucesso’
END ,server_principal_name,database_principal_name,server_instance_name,
database_name,[object_name],user_defined_event_id,user_defined_information
from fn_get_audit_file(‘C:\Audit\*’,null,null)

image

Como podem ver, apenas inclui o erro também na auditoria.

Bom pessoal por hoje era isso, espero que tenham gostado dessa nova funcionalidade de claro dessa semana focada em SQL Server 2012. Siga meu blog, pois sempre teremos novos assuntos relacionados a nova plataforma de dados da Microsoft.
Segunda Feira – DMF não documentada
Terça – Feira – SQL Server 2012: SSMS Snippets
Quarta – Feira – SQL Server 2012: Nova facet para Server Role
Quinta – Feira – SQL Server 2012: Nova aba de Startup

Abraços,
Marcos Freccia
[MCTS|MCITP|MCT SQL Server 2008]

Sobre Marcos Freccia

MVP em SQL Server (Data Plataform) , especialista em SQL Server, e atualmente trabalhando com Microsoft Azure!

Publicado em junho 29, 2012, em Administração, Auditoria, SQL Server 2012 e marcado como , . Adicione o link aos favoritos. 1 comentário.

Deixe uma resposta

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 )

Imagem do Twitter

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

Foto do Facebook

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

Foto do Google+

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

Conectando a %s

%d blogueiros gostam disto: