Thiago Zavaschi R2 www.zavaschi.com

19Jan/1110

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:

image

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 Smile).

É isso pessoal, espero que tenham gostado!

Em caso de dúvidas entrem em contato!

Abraços,
Thiago Zavaschi

Comments (10) Trackbacks (1)
  1. Auditoria é o que precisávamos no SQL e pelo jeito ela veio pra ficar, parabéns pelo Post!

  2. Como vai mestre Thiago? Tenho uma pergunta: e se eu criar um audit para todos os objetos do meu banco, aproximadamente uns 3.000, como funciona o esquema de performance?

  3. O quanto pior de performance é difícil mensurar em um % fixo, no entanto esse monitoramento através do SQL audit é menos nocivo que o monitoramento através do SQL Trace, pois trabalha com camadas mais baixas da própria engine (além de possibilitar mais objetos e ações).
    O que recomendo é um teste de carga sobre um ambiente de homologação que possua um hardware similar ao de produção! O que encontramos na literatura é algo em torno de 5% de overhead, mas isso depende muito.
    Grande abraço!

  4. Ótima explicação!

    Artigo salvo.

    Aqui na empresa utilizamos o CDC e por enquanto tem funcionado muito bem para a nossa necessidade.

    :D

  5. O CDC é muito bom! Apenas lembrando que ele não faz tracking de SELECT, apenas INSERT UPDATE e DELETE.
    O Audit serve para outras áreas também e não só para manipulação de dados!
    Meus próximos artigos serão sobre CDC e Change Tracking :)

  6. Thiago eu notei que neste caso ele monitora apenas a tbl empregado, onde eu deveria alterar pra pegar de todas as tabelas

  7. Artigo bacana Thiago ! Parabéns, muito objetivo e esclarecedor !

    Dá até pra fazer uma rotina que salva os dados em uma tabela e envia um report diário pelo SSRS. Show de bola !

  8. Bom dia Thiago,
    Muito bacana este recurso, eu não conhecia.
    Estou tentando criar uma auditoria de teste mas não estou conseguindo!
    Será que vc pode me dar um help?
    Seguem meus scripts:
    USE [master]
    GO

    /****** Object: Audit [Audit-TESTE] Script Date: 09/23/2014 10:38:47 ******/
    CREATE SERVER AUDIT [Audit-TESTE]
    TO FILE
    ( FILEPATH = N’D:\Auditoria\’
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = ’4e1535ee-8249-4929-8d30-b0c06a19985d’
    )
    GO

    ————————————————————————–
    USE [PRIME]
    GO

    CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-TESTE]
    FOR SERVER AUDIT [Audit-TESTE]
    ADD (DELETE ON OBJECT::[dbo].[TESTE] BY [public]),
    ADD (INSERT ON OBJECT::[dbo].[TESTE] BY [public]),
    ADD (SELECT ON OBJECT::[dbo].[TESTE] BY [public]),
    ADD (UPDATE ON OBJECT::[dbo].[TESTE] BY [public])
    WITH (STATE = ON)
    GO
    ————————————————————

    Estou executando a consulta que vc informou
    SELECT * FROM sys.fn_get_audit_file (N’D:\Auditoria*’,default,default);

    Mas não retorna nada!!
    Abraços

  9. Oi Anderson, você ativou a auditoria com os comandos similares a estes:
    ALTER SERVER AUDIT AuditoriaSalario
    WITH (STATE = ON);
    GO

    USE [demo]
    GO

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

  10. Olá,

    Na minha empresa temos apenas um usuário de banco de dados e ele é compartilhado entre todos, logo quando alguém faz uma alteração de DDL, por exemplo, precisamos recuperar não somente o usuário conectado no banco, mas também o usuário da máquina, ou então o IP do cliente.
    Por meio de triggers isso é possível mas e pela auditoria oferecida pelo SQL Server, é possível também?


Leave a comment