Introdução


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 próprias auditorias.

Criação da auditoria


​O primeiro passo para a criação da auditoria personalizada é você informar o local onde irá salvar os arquivos de auditoria, além de especificar também as demais configurações conforme a imagem 1.


(Imagem 01 - Criação da auditoria)

Criação da auditoria personalizada



Após você ter concluído o primeiro passo da configuração da auditoria, você deverá agora criar as especificações necessárias para que os eventos de usuário sejam gravados nos arquivos de auditoria.


(Imagem 02 - Adicionando o tipo de auditoria personalizada)

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

Executando a auditoria personalizada


Para salvar nossas auditorias personalizadas existe uma nova procedure chamada sp_audit_write onde a execução da procedure é bastante simples conforme a listagem 01.

Listagem 01. Execução da procedure de auditoria

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

Executada a procedure podemos consultar nosso arquivo de auditoria para nos certificarmos que nossa informação foi realmente auditada através da listagem 02.

Listagem 02. Consultando os dados auditados.

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)


Com a consulta acima, você consegue ler o arquivo de auditoria e verificar o que foi auditado.

 

Caso prático para a auditoria personalizada



 A auditoria personalizada pode ser muito utilizada para aplicações comerciais que desejam utilizar recursos do SQL Server para armazenar dados auditados. Conforme a listagem 03 segue um exemplo pratico da aplicação da nova funcionalidade.

​Listagem 03. Caso prático para auditoria personalizada


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

Executando um teste.

Listagem 04. Executando procedure de testes.


exec NewLogin N'marcos.freccia',N'SQLServer2012'

Feito a execução, podemos novamente executar a consulta da listagem 02 e termos o seguinte resultado visualizado na imagem 03.



(Imagem 03. Visualizando as auditorias personalizadas)

Conclusão


As auditorias personalizadas presentes no SQL Server 2012, permitem uma maior flexibilidade do produto, entregando o máximo de usabilidade e performance para o usuário que está utilizando. Em questões de performance o SQL Audit trabalha em níveis mais baixos do SQL Server impedindo assim problemas de overhead e performance.