Thiago Zavaschi R2 www.zavaschi.com

Auditoria no SQL Server – SQL Audit

Olá a todos!

No último artigo vimos o que, na minha opinião, é o carro chefe do SQL Server 2008 (“new” feature), o Resource Governor. Mas não é só isso!

Hoje vamos ver sobre auditoria! :)

Há diferentes formas de se fazer auditoria no SQL Server: triggers, CDC, Audit, entre outras.

O que veremos hoje é o audit e serve (não só) para responder dúvidas como:

“Tenho uma aplicação de recursos humanos e que possui uma tabela com o salário dos funcionários, tem como eu saber os usuários que executaram um comando de select sobre essa tabela?” (sim é possível, além de ser menos custoso do que rodar um profiler contra a base e ainda filtrando sobre a tabela desejada (parse de texto)).

Para efeito de simplicidade imagine que o seu cenário tenha a seguinte tabela:

O script para criação da tabela:

CREATE TABLE Empregado
( idEmpregado INT IDENTITY PRIMARY KEY,
nome VARCHAR(100) NOT NULL,
valor MONEY NOT NULL)
GO

O log de auditoria pode ir para três destinos distintos: arquivo, security log e application log (do servidor onde o SQL Server roda).

O primeiro passo é criar um Audit:

Pelo Management Studio: (Security > Audits > New Audit)

Ou através de scripts.

Para cada destino escolhido há configurações específicas. Segue o script para criar o Audit para arquivo:

USE [master]
GO
CREATE SERVER AUDIT [AuditoriaSalario]
TO FILE
(    FILEPATH = N'C:\Temp'
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)

Após isso você precisa criar uma especificação da auditoria (Audit Specification) que pode ser Server Audit Specification (Security > Server Audit Specifications > New Server Audit Specification…) ou Database Audit Specification (DatabaseName > Security > Database Audit Specification), que mapeiam efetivamente os objetos (ou servidores) sendo monitorados e conecta a um Audit (que nada mais é que a configuração do local aonde estarão os dados da auditoria).

Existem MUITOS tipos de ações de auditoria (Audit Action Type). No nosso exemplo (para responder ao questionamento inicial) vamos utilizar Database Audit Specification (pois o objeto a ser monitorado é uma tabela de uma base de dados específica).

O comando abaixo cria a especificação (public que é o role sendo monitorado):

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Salario]
FOR SERVER AUDIT [AuditoriaSalario]
ADD (SELECT ON OBJECT::[dbo].[Empregado] BY [public])

Depois disso temos que ativar a auditoria:

USE [master]
GO

ALTER SERVER AUDIT AuditoriaSalario
WITH (STATE = ON);
GO

USE [demo]
GO

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-Salario]
WITH (STATE = ON)
GO

Beleza.. Auditoria “de pé”! Vamos inserir alguns dados na tabela de Empregado (sintaxe SQL2008+):

INSERT INTO [demo].[dbo].[Empregado]
           ([nome],[valor])
     VALUES
           ('Thiago', 15000),
           ('Joao', 1000),
           ('Pedro', 7500)
GO

E na sequência vamos fazer um select para descobrir o meu salário:

SELECT 'Valor do Salário: ' + CONVERT(VARCHAR,valor) FROM Empregado WHERE nome = 'Thiago'

Se tudo foi configurado corretamente, devemos ter no arquivo essa informação logada.

O arquivo não é plain text, para ler basta executar o seguinte comando:

SELECT * FROM sys.fn_get_audit_file (N'C:\Temp\*',default,default);

E lá estão os selects que executei. Para facilitar, uma visão resumida:

SELECT
event_time 'horario',
database_name + '.' + schema_name + '.' + object_name 'objeto',
statement,
server_principal_name 'login',
session_server_principal_name 'user'
FROM sys.fn_get_audit_file (N'C:\Temp\*',default,default)
WHERE action_id = 'SL';

Que retorna (no meu caso):

Horario: 2011-01-19 20:04:37.0349242   
Objeto: demo.dbo.Empregado   
Statement: SELECT 'Valor do Salário: ' + CONVERT(VARCHAR,valor) FROM Empregado WHERE nome = 'Thiago'      
Login: SOUTHAMERICA\v-thzava   
User: SOUTHAMERICA\v-thzava

E relembrando que isso pode ser enviado ao security log ou app log (onde até mesmo um sysadmin do banco pode não ter acesso ).

É isso pessoal, espero que tenham gostado!

Em caso de dúvidas entrem em contato!

Abraços,
Thiago Zavaschi