Auditoria no SQL Server

Prévia do material em texto

www.devmedia.com.br
[versão para impressão]
Link original: http://www.devmedia.com.br/articles/viewcomp.asp?comp=30168
Auditoria no SQL Server
Este artigo descreve conceitos fundamentais sobre o tema auditoria de banco de dados através de contextualizações e
exemplos simples, são salientadas melhores práticas com relação às permissões de acesso, logs e visualização dos
dados auditados.
Fique por dentro
Este artigo descreve conceitos fundamentais sobre o tema auditoria de banco de dados. Através de contextualizações e exemplos simples, são salientadas melhores práticas com
relação às permissões de acesso, logs e visualização dos dados auditados, bem como segurança para a proteção de dados sigilosos dentro da organização.
A aplicação de conceitos de auditoria é recomendada para empresas de todos os segmentos, mas, em especial, instituições financeiras.
Da mesma forma, em situações em que é necessário proteger os dados de usuários não autorizados ou mal intencionados, é importante a adoção de uma estratégia de segurança
para auditar as informações.
A crescente necessidade da segurança de informação que as empresas precisam gerenciar para atender demandas operacionais, táticas e estratégicas, tendo em vista a exponencial
dependência da utilização de sistemas informatizados para o provimento dos dados, faz com que haja, cada vez mais, preocupação em como os dados estão sendo manipulados.
No SQL Server existe diversas maneiras de se monitorar o acesso aos dados como, por exemplo, a utilização de ferramentas do próprio SQL Server como triggers, profilers e o SQL
Server Audit que vamos ver adiante.
Nesse contexto, este artigo apresentada algumas boas práticas de monitoramento e auditoria no acesso a dados, utilizando a própria ferramenta do SQL Server chamada SQL Server
Audit.
Conceitos iniciais sobre auditoria no SQL Server
Para iniciarmos, é indispensável o entendimento sobre como funciona a auditoria no SQL Server. A auditoria não se limita apenas a registrar o que está sendo manipulado ou
processado, mas sim comprovar e mostrar o que foi feito, quando foi feito e como foi feita determinada ação.
A auditoria do SQL Server pode monitorar todas as ações que são realizadas dentro do banco de dados. Estas ações vão desde tentativas de login, operações simples de leitura de
tabelas, até mudanças na estrutura de objetos ou informações dentro do SQL Server.
Um ponto positivo da auditoria é a performance, provendo capacidade de leitura assíncrona ou síncrona (ver BOX 1 ). Por padrão, a auditoria do SQL Server usa o modelo assíncrono
para uma melhor performance.
Esta auditoria é feita através de um tipo protegido de evento estendido do SQL Server e por questões de segurança não se pode executar diretamente nenhum comando a não ser de
leitura dos dados auditados.
BOX 1 . D i fe r e n ças en t r e a l e i t u r a assín cr o n a e sín cr on a
Numa comunicação assíncrona, cada bloco de dados inclui um bloco de informação de controle (chamado flag), para que se saiba exatamente onde começa e acaba o bloco de da
dos e qual a sua posição na sequência da informação transmitida; ao contrário da comunicação síncrona, onde cada bloco de informação é transmitido e recebido num instante de
tempo bem definido e conhecido pelo transmissor e receptor, ou seja, eles têm que estar sincronizados.
Para se manter essa sincronia, é transmitido periodicamente um bloco de informações que ajuda a manter o emissor e receptor sincronizados.
Como já informado, a auditoria no SQL Server pode monitorar todas as ações feitas, mas existe a possibilidade de escolhermos o que deve ser auditado, como logins, ações (DML,
DDL ou DCL) ou objetos, incluindo tabelas e views.
Esta estratégia é utilizada para não gastar mais recursos do que o necessário, gerando informações que não têm a necessidade de serem auditadas.
Com isto, também há um menor espaço em disco a ser ocupado e uma melhor performance. A melhor performance e desempenho é alcançado quando usamos um arquivo físico
como destino das informações que estão sendo auditadas, ao invés de utilizarmos uma tabela dentro da base de dados.
Entretanto, a diferença de performance entre os diferentes destinos das informações de auditoria dependerá muito da configuração e do planejamento da auditoria.
Assim como há usuários que não requerem monitoração devido a terem somente permissões de leitura, também existem tabelas que não precisam de auditoria. Isto se aplica quando
existe somente um conjunto de tabelas que podem ter seus dados modificados. Deste modo não é necessário monitorar as outras tabelas.
Podemos verificar o arquivo de auditoria utilizando o utilitário Log File Viewer, que já existe no SQL Server Management Studio. Também é possível adotar uma função T-SQL,
chamada FN_Get _Audit_File, para ler o arquivo de auditoria, ou podemos ainda utilizar o SQL Server Integration Services, para importar os arquivos de auditoria para a base de
dados e até gerar relatórios via SQL Server Reporting Services.
Por fim, um ponto a ser levado em consideração é que a auditoria no SQL Server somente está disponível na edição Enterprise das versões 2008, 2008R2 e 2012.
Níveis de auditoria
Uma auditoria no SQL Server pode ser dividida em dois diferentes níveis:
1) N ív e l d e ser v id or : Esse tipo de auditoria monitora ações no nível do servidor, como operações de logon, logoff e mudanças de gerenciamento;
2) N ív e l d e ba n co d e da do s: A auditoria no nível de banco de dados engloba operações de manipulação e definição de dados (DML e DDL).
Neste artigo vamos tratar somente da auditoria no nível de banco de dados, o qual é o mais utilizado pelas empresas, por que é onde a parte auditada são os dados que muitas
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
1 de 8 07/02/2017 16:02
vezes são críticos e confidenciais das empresas, como resultados financeiros, informações sobre os empregados, clientes, etc.
Neste nível a auditoria suporta ações diretamente na base de dados em variados objetos, como tabelas, views, stored procedures, functions, schemas, campos XML, etc. Na Ta be la 1
estão as ações que podem ser capturadas pela auditoria no nível de banco de dados.
Ta be la 1 . Ações capturadas pela auditoria
Criando uma base de dados exemplo
Vamos iniciar criando os objetos que serão utilizados nos nossos testes de auditoria. Para isso, vamos criar uma base de dados chamada DB_Audit oria, uma tabela chamada
TB_Produto e inserir uma massa de dados para gerarmos os testes (veja a Li st ag em 1 ).
Li st a gem 1 . Criando os objetos para os testes
 -- Criando uma base de dados
 Create Database DB_Auditoria
 
 -- Acessando a base de dados criada
 use DB_Auditoria
 
 -- Criando uma tabela de produtos
 Create table TB_Produto (Nome_Produto varchar(30), ID int identity(1,1))
 
 -- Inserindo uma massa de dados na tabela com 100 registros
 Insert Into TB_Produto Values('Produto - '+Convert(VarChar(3),@@Identity))
 
 Go 100
 
 -- verificando os registros inseridos
 select * from TB_Produto
Com os objetos criados e nossa tabela TB_Produto populada, vamos ter nesta tabela 100 registros para os nossos testes, podemos iniciar a criação e configuração da auditoria.
Criando o SQL Server Audit
Antes de criarmos o SQL Server Audit, temos obrigatoriamente que criar um SQL Server Audit. Este componente é a instância da auditoria que faz todo o gerenciamento do que está
sendo capturado. Em uma instância podemos ter vários componentes SQL Server Audit, são neles que configuramos o local onde serão armazenadas as informações auditadas.
Quando iniciamos a criação, temos que definir uma pasta onde serão guardados os arquivos de auditoria. Para o nossoexemplo, utilizaremos a pasta C: \ Auditor ia.
Podemos definir também o parâmetro MAX_ROLLOVER_FILES no momento da criação, no qual podemos limitar o tamanho do arquivo de auditoria. Quando o tamanho chegar ao
limite que estabelecemos, outro arquivo será gerado e as novas informações serão armazenadas neste, ainda permanecendo o outro arquivo como um histórico.
No nosso caso, vamos definir o tamanho máximo para centralizarmos tudo em um único arquivo.
Temos outras duas opções interessantes. Uma delas é que podemos reservar o espaço em disco que o arquivo irá ocupar ou deixá-lo crescer à medida que as informações são
inseridas.
Isto pode ser configurado no parâmetro RESERVE_DI SK_SPACE para ON, quando onde já reservamos o espaço em disco para o arquivo ou para OFF, quando o espaço vai sendo
ocupado à medida que o arquivo cresce. Isto é definido no momento da criação.
Outra opção é que em casos críticos que a auditoria não pode parar de forma alguma, onde é melhor parar o serviço caso aconteça alguma falha na auditoria como alguém
desabilita-la, temos a opção ON_FAILURE.
Em caso de falhas, se esta opção estiver como CONTINUE, a instância irá continuar com suas operações normalmente, caso coloquemos como opção SHUTDOWN o serviço da
instância será forçado a parar.
Com os parâmetros definidos, vamos criar o SQL Server Audit conforme a List a ge m 2 .
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
2 de 8 07/02/2017 16:02
Li st a gem 2 . Criando o SQL Server Audit
 -- Acessando a base de dados Master
 USE [master]
 
 GO
 -- Criando o SQL Server Audit chamado Auditoria
 CREATE SERVER AUDIT [Auditoria]
 TO FILE 
 ( FILEPATH = N'C:\Auditoria'
 ,MAXSIZE = 0 MB -- Tamanho máximo do arquivo
 ,MAX_ROLLOVER_FILES = 2147483647
 ,RESERVE_DISK_SPACE = OFF
 )
 WITH
 ( QUEUE_DELAY = 1000
 ,ON_FAILURE = CONTINUE
 )
 
 GO
Após executar esse script, já teremos o nosso SQL Server Audit criado. É importante lembrar que, por default, ele é desabilitado, portanto devemos habilitá-lo com o seguinte
comando:
-- Habilitando o SQL Server Audit
ALTER SERVER AUDIT [Auditoria] WITH (STATE = ON)
Outra forma de criar o SQL Server Audit é através do modo gráfico da ferramenta SQL Server Management Studio (veja a Fi gu r a 1 ). Para isto, devemos realizar as seguintes ações:
1. Expanda a pasta Securit y;
2. Clique com o botão direito em Audit s e selecione New Audit;
3. Preencha os campos com os mesmos valores que utilizamos via T-SQL (ver Li st ag em 2 );
4. Clique em Ok;
5. Dentro da pasta Securit y, expanda a pasta Audit s;
6. Clique com o botão direito em Audit oria e selecione Enable Audit .
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
3 de 8 07/02/2017 16:02
Fi gu r a 1 . Criação do SQL Server Audit via SSMS
Criando a auditoria da base de dados
Após criarmos o SQL Server Audit, nosso próximo passo é definir o componente de auditoria na base de dados.
A auditoria da base de dados é a responsável por coletar as ações que estão sendo realizadas.
A partir do momento que este componente registra um acesso a um objeto, ele envia esta informação para o SQL Server Audit, que criamos anteriormente, e este registra no arquivo
de auditoria todas as informações pertinentes que foram capturadas.
Para isto, quando criarmos o Database Audit, temos que vinculá-lo ao SQL Server Audit que é onde contém a configuração inicial.
Nosso próximo passo será configurar a auditoria na base de dados DB_Auditor ia e adicionar a nossa tabela TB_produto para auditar todos os comandos Select, Insert, Update ou
Delete que forem realizados neste objeto por qualquer login que tenha permissão. Isto é feito de uma forma simples, onde vamos acessar nossa base de dados DB_Auditoria e depois
disto utilizar o comando de criação da auditoria no SQL Server, conforme mostra a List a ge m 3 .
Li st a gem 3 . Criando a auditoria na base de dados
 -- Acessando a base de dados
 USE [DB_Auditoria]
 
 GO
 -- Fazendo a criação do DataBase Audit
 CREATE DATABASE AUDIT SPECIFICATION [DB_Audit]
 FOR SERVER AUDIT [Auditoria]
 ADD (SELECT ON OBJECT::[dbo].[TB_Produto] BY [public]),
 ADD (INSERT ON OBJECT::[dbo].[TB_Produto] BY [public]),
 ADD (UPDATE ON OBJECT::[dbo].[TB_Produto] BY [public]),
 ADD (DELETE ON OBJECT::[dbo].[TB_Produto] BY [public])
 
 GO
 
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
4 de 8 07/02/2017 16:02
 Assim como acontece com o SQL Server Audit, o Database Audit, por default, também é criado desabilitado. Deste modo, o próximo passo é habilitá-lo conforme o comando:
 
 -- Habilitando o DataBase Audit
 ALTER DATABASE AUDIT SPECIFICATION [DB_Audit]
 WITH (STATE = ON)
Outra maneira de criar o Database Audit é através do modo gráfico da ferramenta SQL Server Management Studio (veja a Fig u r a 2 ), porem é sempre válido aprenderemos outras
maneiras que temos para realizar determinada ação. A seguir é apresentado o passo a passo para realizar essas ações que fizemos via query utilizando o SSMS:
1. Expanda a pasta referente à base de dados DB_Auditoria;
2. Expanda a pasta Securit y de dentro da base de dados;
3. Clique com o botão direito na pasta Database Audit Specificat ions e, em seguida, em New Database Audit Specif icat ion;
4. Preencha o nome do Dat abase Audit , selecione o SQL Server Audit que estará vinculado e depois as ações auditadas, indicando as ações de cada objeto que devem ser auditadas
(Delete, Insert, Select e Update). Assim que finalizar, clique no botão Ok;
5. Expanda a pasta referente à base de dados. Dentro dela, expanda a pasta Securit y e, por último, a pasta Dat abase Audit Specif icat ions;
6. Nesta pasta estará o Database Audit que acabamos de criar. Então, clique com o botão direito em DB_Audit e selecione Enable Database Audit Specificat ion.
ab r i r i m ag em em n o va j an e la
Fi gu r a 2 . Criação do DataBase Audit via SSMS
Veri�cando os logs da auditoria
Depois que criamos e configurarmos todos os componentes que compõem a auditoria, chegou o momento de descobrirmos como visualizar o que foi capturado pelo nosso processo.
Antes disto, é interessante conhecermos as funções que podem nos auxiliar nesse momento. Na Ta be la 2 estão as principais tabelas e funções com seus respectivos papéis.
ab r i r i m ag em em n o va j an e la
Ta be la 2 . Tabelas e funções de auditoria
Destas funções, a que vamos utilizar com mais frequência é a fn_get _Audit_file. Executamos esta função passando o caminho completo do arquivo físico de auditoria como parâmetro
ou podemos passar todos os arquivos físicos desde que todos estejam em uma mesma pasta.
Todos os arquivos estando na mesma pasta, não vamos especificar no parâmetro da função o caminho completo com o nome do arquivo, mas somente a pasta onde os arquivos
estão.
O retorno desta função são 28 campos com informações sobre as ações que ocorreram sobre os objetos auditados. A Ta be l a 3 lista todos os campos de retorno com seus
significados.
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
5 de 8 07/02/2017 16:02
ab r i r i m ag em em n o va j an e la
Ta be la 3 . Campos de retorno da função fn_get_audit_file
O próximo passo é iniciar o nosso teste fazendo operações de manipulação com os dados da tabela TB_Produto utilizando oscomandos select, update, insert e delete, que
configuramos para serem capturados pela auditoria. Veja o código para executar esse teste na List a ge m 4 .
Li st a gem 4 . Manipulando os dados
 -- utilizando o comando select para constar na auditoria
 select * from [dbo].[TB_Produto]
 
 -- utilizando o comando update para constar na auditoria
 update [dbo].[TB_Produto]
 set Nome_Produto = 'Produto - Auditado'
 where ID = 1
 
 -- utilizando o comando delete para constar na auditoria
 delete from [dbo].[TB_Produto]
 where ID = 2
 
 -- utilizando o comando insert para constar na auditoria
 insert into [dbo].[TB_Produto]
 values ('Produto - 100') 
Após executarmos esses comandos, todas essas operações estarão gravadas no arquivo de auditoria, localizado em c: \ auditoria. Se tentarmos abrir o arquivo no seu local de origem,
não fará muito sentido, o que irá retornar serão letras e palavras soltas no arquivo conforme a Fi gu r a 3 .
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
6 de 8 07/02/2017 16:02
Fi gu r a 3 . Conteúdo do arquivo de auditoria
Para conseguirmos entender o que realmente foi auditado nas operações que demonstramos na List a ge m 4 , podemos utilizar a função fn_get_audit_file no SQL Server Management
Studio, passando como parâmetro o caminho da pasta onde está o arquivo de auditoria. Para a função ler todos os arquivos que tenham a extensão .sqlaudit , vamos fazer conforme
o código:
-- Lendo os arquivos de auditoria
SELECT * FROM fn_get_audit_file('C:\Auditoria\Auditoria*.sqlaudit',default,default);
O retorno dessa instrução serão os campos da função fn_get_audit_file, como mostra a Tab e l a 4 .
ab r i r i m ag em em n o va j an e la
Ta be la 4 . Retorno da função fn_get_audit_file
Como dito anteriormente, a função fn_get_Audit_f ile retorna 28 campos com informações referentes ao arquivo de auditoria. Destes, separamos na Ta be la 4 os principais que
devemos analisar. São eles:
· Ev e n t _ t im e : Retorna o momento em que a ação ocorreu. O horário deste campo não é o mesmo do servidor onde a instancia do SQL Server está localizada, mas sim está em UTC
time (UTC 0);
No caso da maior parte do Brasil devemos retirar 3 horas devido a nosso fuso horário ser o UTC -03:00. Podemos fazer isto utilizando o comando dat eadd (dateadd(HH,-
3,Event_time)) junto com a função fn_get_audit_file;
· Act io n _ I D: Retorna o tipo de ação que foi executada, sendo SL, UP, DL e IN para select, update, delete e insert, respectivamente;
· Su cce ed ed : Indica se a execução da ação foi bem sucedida. O valor 0 sinaliza que houve falha e 1 sinaliza que a operação teve êxito;
· Sessio n _ se r v e r _ p r in ci pa l _ n am e : Retorna o login que executou a ação;
· Da t ab ase _ N a m e: Retorna o nome da base de dados onde está o objeto auditado que sofreu a ação;
· Ob j ect _ N am e : Retorna o nome do objeto que sofreu a ação;
· St a t e m en t : Retorna o comando que foi executado.
Neste artigo foi apresentado um recurso valioso para segurança e auditoria em bancos de dados SQL Server. Embora existam outras maneiras de se auditar processos, seja no próprio
SQL Server ou através de ferramentas externas, vale lembrar que a adoção de recursos nativos do banco de dados garante maior capacidade de gerenciamento.
A auditoria não depende de nenhum outro recurso, além do que já vem na instalação do SQL Server. Em casos de alterações de dados indevidos, fica muito fácil saber quando, quem
e como foi feita a alteração, podendo facilmente ser revertida utilizando os registros que constam na auditoria.
Li n k s
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
7 de 8 07/02/2017 16:02
Au di t o r i a d o SQL Ser v er ( M eca n i sm o de b an co de d ad os)
ht tp: / / t echnet .m icrosoft .com / pt -br/ library/ cc280386.aspx
SQL Ser ve r 2 0 0 8 R2 – Dat a ba se Au d i t Spe ci f i ca t i o n s
ht tp: / / msdn.m icrosoft .com / en-us/ library/ cc280472(v= sql.105) .aspx
Do w n l oa d do SQL Se r v e r 2 0 0 8 R2 – Sof t w ar e d e av a l i açã o ( 1 8 0 d ia s)
ht tp: / / www.m icrosoft .com/ sqlserver/ en/ us/ get -sql-server/ t ry- it .aspx
por Alexandre Matayosi
Revista SQL Magazine lover 
R
e
ce
b
a
 n
o
tif
ic
a
çõ
e
s 
:)
Auditoria no SQL Server http://www.devmedia.com.br/articles/viewcomp_forprint.asp?comp=30168
8 de 8 07/02/2017 16:02