Auditoria no SQL Server

Em algumas corporações a auditoria de dados é primordial, em uma instituição financeira por exemplo, é extremamente importante e algumas vezes prevista em leis.

Nas versões anteriores ao SQL 2008 para se ter uma “rastreabilidade” dos dados, usávamos o TRACE, triggers ou alguma ferramenta de terceiro e no SQL Server 2005, em alguns casos, o Event Notification ou DDL Triggers. Pois bem o SQL Server 2008 tem uma excelente ferramenta o SQL Server Audit, que possui algumas vantagens que será demonstrada ao longo deste artigo.

Diversas corporações trabalham com dados sensíveis, para proteger esses dados sensíveis existem alguns regimes com “SOX” ou mesmo uma convenção interna, algumas corporação devem ter o controle de “quem acessa o que e quando”, acessos não autorizados, entre outros…

O SQL Server Audit visa substituir o Trace como auditoria, o Audit é um recurso destinado somente à auditoria ao contrário do Trace que também é usado para coletar dados de performance, uma das vantagens do Audit perante o trace é que ele foi projetado com o objetivo de se ter o mínimo de impacto possível na performance, pois ele consome menos recursos que o trace, e possui uma interface muito simples e fácil de se gerenciar.

Vale lembrar que este recurso somente esta disponível na versão Enterprise do SQL Server 2008.

A auditoria é tão importante em diversas corporações que o SQL Server Audit possui uma recurso muito importante que o Shutdown do serviço do SQL Server se por acaso o SQL Server não conseguir fazer a auditoria, e o serviço não pode ser iniciado enquanto o problema persistir (podemos forçar a inicialização do serviço com o flag –M e em alguns casos –F).

O resultado desta auditoria pode ser gravado em um arquivo binário (.sqlaudit) ou no Event Viewer do Windows (Application ou Security) , com exceção para no Windows XP, o SQL Audit não grava no Event Viewer.

clip_image002 clip_image004
Event Viewer Log File Viewer (arquivo binário)

Existem 4 principais objetos para se configurar a auditoria, Server Audit, Target, Server Audit Specification e o Database Audit Specification, vamos detalhar um pouco mais sobre cada item:

SQL Server Audit

O SQL Server Audit coleta ações de uma única instância de servidor ou ações em nível de banco de dados. A auditoria é no nível da instância do SQL Server. Você pode ter várias auditorias por instância do SQL Server. Quando você define uma auditoria, você precisa especificar o local (target) para o armazenamento dos resultados. Por default a auditoria é criada em um estado desabilitado.

Target

O Resultado de uma auditoria é enviado à um destino, que pode ser um arquivo binário ou uma registro no event viewer (security event log ou windows application log).

Nota: no Windows XP não possível gravar a auditoria no Event Viewer!

Server Audit Specification

O Server Audit Specification pertence a um Audit. Você só pode criar um Server Audit Specification por Audit, porque ambos são criados no escopo da instância do SQL Server (no nível do servidor).

O Server Audit Specification coleta ações de nível de servidor.

Estas ações são enviadas para o SQL Server Audit, que grava estas ações no Target (em arquivo binário ou no Event Viewer).

Neste objeto podemos coletar dados como BACKUP_RESTORE_GROUP, FAILED_LOGIN_GROUP, LOGOUT_GROUP, SUCCESSFUL_LOGIN_GROUP etc…

Para visualizar todos os objetos possíveis, faça uma pesquisa na DMV sys.dm_audit_actions

SELECT name FROM sys.dm_audit_actions WHERE class_desc=‘SERVER’ AND configuration_level=‘Group’ ORDER BY name

Database Audit Specification

O Database Audit Specification também pretence ao SQL Server audit. Em um Database Audit Specification podemos ter vários databases e o destino dele será um único Audit, ou seja, em um Audit podemos ter vários Databases Specificarions mas em um Database Specification só podemos ter um Audit, e podemos ter vários databases em um Database Specification, parace complicado, mas é simples, nos exemplos dos tópicos a seguir explicarei esta característica.

O Database Audit Specification coleta ações no nível de databases.

Neste objeto podemos coletar dados como DELETE, INSERT, SELECT, UPDATE, EXECUTE, DBCC_GROUP etc…

Para visualizar todos os objetos possíveis, faça uma pesquisa na DMV sys.dm_audit_actions

SELECT name FROM sys.dm_audit_actions WHERE class_desc=‘DATABASE’ AND configuration_level IN (‘ACtion’,‘Group’) ORDER BY name

Como auditar tabelas usadas

Vamos imaginar o seguinte cenário: Tenho diversas tabelas em minha base, e quero saber quais tabelas são usa usadas.

Nas versões anteriores ao 2008 usaríamos um Trace ou Trigger, mas no 2008 temos o Audit que é mais objetivo, ou seja, consigo filtrar melhor e com um custo muito baixo.

A primeira tarefa é criarmos um Server Audit, na pasta Audit, clique com o botão direito e escolha a opção “New Audit”.

clip_image006

Na janela, New Audit, Informe o nome no campo “Audit Name”, selecione o target “Audit Destination” como foi explicado, pode ser selecionado o target para um arquivo binário ou gravar registros no Event Viewer e clique em “OK” para criar o Audit.

clip_image008

Por default o Audit é criado desabilitado, para hablilidar o Audit, clique com o botão direito sobre o Audit e escolha a opção “Enable Audit”.

clip_image010

Como o nosso cenário é auditar tabelas utilizadas, a segunda etapa será a criação do “Database Audit Specification”, para isto, clique com o botão direito em Database Audit Specification e escolha a opção “New Database Audit Specification…

clip_image012

Na janela “Create Database Audit Specification” informe o nome para o Database Specification no campo “Name” e selecione o Audit.

clip_image014

Em seguida faremos os filtros na seção “Actions”, para isto, no primeiro combo selecionaremos a opção “Delete” como segue:

clip_image016

Em seguida devemos selecionar a primeira opção do nosso filtro na coluna Object Class, selecione “Database

clip_image018

Devemos agora selecionar qual o database sera Auditado, para isto clique no botão “” da coluna Object Name e na janela “Select Objects” clique no botão “Browse…

clip_image020

Selecione o seu database, no caso deste exemplo estou selecionando o dbTeste, clique no botão “OK”. OBS. Conforme falei no tópico acima pode ser auditado vários databases por Audit Specification, observe que tenho a possibilidade de escolher mais de um DB, mas o Audit sera o mesmo neste caso o audittoFile” criado na etapa 1.

clip_image022

Clique em “OK” na janela “Select Objects”.

A proxima etapa é selecionar o “Principal Name”, ou seja,quem nós vamos auditar, podemos selecionar usuários e Roles. Para isto clique no botão “” da coluna Principal Name e clique sobre o botão “Browse” na janela “Select Objects”.

clip_image024

Selecione os usuários e/ou roles que você deseja autidar e em seguida clique sobre o botão “OK” e na janela “Select Object” clique sobre o botão “OK”.

clip_image026

Após as etapas acima concluimos o nosso primeiro filtro para todas as tabelas do database dbTeste para todos os usuários que da role “Public

clip_image028

Vamos repetir a operação para os comandos “Insert, Select e Update” como segue:

clip_image030

Na janela “Create Database Specification” clique sobre o botão “OK” para concluir a criação do database audit.

Por default o “Database Specification” também é criado desabilitado, para habilitar clique sobre o mesmo com o botão direito e escolha a opção “Enable Database Audit Specification”.

clip_image032

Testando o Audit

Vamos criar 4 tabelas simples em nosso database.

use dbTeste
go

Create table tbTeste1 (id int, nome varchar(60))
Create table tbTeste2 (id int, nome varchar(60))
Create table tbTeste3 (id int, nome varchar(60))
Create table tbTeste4 (id int, nome varchar(60))

Agora vamos inserir, excluir, selecionar e alterar registros nestas tabelas com exceção da tabela 1.

–tb2 insert + select
insert into tbTeste2 values (1,‘Marcelo’)
go

Select id,nome from tbTeste2
go

–tb3 insert + Update + select
insert into tbTeste3 values (1,‘Marcelo’)
go

update tbTeste3 set nome=‘Fernandes’
go

Select id,nome from tbTeste3
go

–tb4 insert + delete
insert into tbTeste4 values (1,‘Marcelo’)
go

delete from tbTeste4

Como escolhenos o target “File” existe duas maneiras para a coleta do resultado do Audit, pela ferramenta gráfica e por linha de comandos.

Para visualizarmos pela ferramenta gráfica, basta clicar com o botão direito sobre o Audit e escolher a opção “View Audit Logs”.

clip_image034

Será exibido a janeta “Log FileViewer” com diversas informações sobre a auditoria, por exemplo quem efetuou o comando, de onde efetuou, data e hora, comando etc…

clip_image036

Observe que não temos registro para a tabela1, pois para esta tabela não executamos nenhum comando que esta sendo auditado.

Para ver o log por linha de comando, devemos utilizar a funcão “fn_get_audit_file” como segue:

clip_image038

Criando o Audit por linha de comando

Podemos criar o Audit por linha de comando também, para o audit acima podemos executar os seguintes comandos:

–Cria Audit
USE [master]
GO

IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N’Audit-20100918-toFile’)
DROP SERVER AUDIT [Audit-20100918-toFile]
GO

CREATE SERVER AUDIT [Audit-20100918-toFile] TO FILE
( FILEPATH = N’E:\’
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000,ON_FAILURE = CONTINUE )
GO

–Hablita Audit
ALTER SERVER AUDIT [Audit-20100918-toFile] WITH (STATE = ON);
GO

–Cria Database Specification
USE [dbTeste]
GO

IF EXISTS (SELECT * FROM sys.database_audit_specifications WHERE name = N’DatabaseAuditSpecification-20100918-TabelasUtilizadas’)
DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20100918-TabelasUtilizadas]
GO

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20100918-TabelasUtilizadas]
FOR SERVER AUDIT [Audit-20100918-toFile]
ADD (DELETE ON DATABASE::[dbTeste] BY [public]),
ADD (INSERT ON DATABASE::[dbTeste] BY [public]),
ADD (SELECT ON DATABASE::[dbTeste] BY [public]),
ADD (UPDATE ON DATABASE::[dbTeste] BY [public])
GO

–Habilita Database Specification
ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20100918-TabelasUtilizadas] WITH (STATE = ON);

Bom galera, o Audit é muito mais poderoso do que se imagina, neste caso eu demonstrei um audit para um database, mas você pode fazer um audit para um servidor, por exemplo, auditar login/logoff, alteração de permissões, restores etc…

Para maiores informações consulte o BOL.

Leitura adicional:

http://msdn.microsoft.com/en-us/library/dd392015.aspx

http://blogs.msdn.com/b/sqlsecurity/archive/2008/12/18/configuring-sql-audit-using-the-audit-dynamic-management-views.aspx

Att.
Marcelo Fernandes

Anúncios

3 comentários sobre “Auditoria no SQL Server

  1. Welder

    Marcelo, ótimo tutorial, porém estou com um probleminha, não estou conseguindo selecionar a opção DELETE na coluna Action, esta opção não aparece. Poderia me ajudar por gentileza?

    Estou usando SQL SERVER 2008 R2 – Standart Edition
    Muito obrigado,

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 )

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