Salvo pelo Default Trace!

Olá pessoal,

Hoje vou falar de uma caso real, que aconteceu comigo. Um amigo entrou em contato comigo e disse que queria saber qual usuário excluiu algumas tabelas do banco de dados, só que no servidor não estava habilitado a auditoria, nem por triggers DDL e nem pela feature de Auditoria do SQL Server 2008.

E agora, como saber qual login executou o comando de DROP TABLE no banco de dados de produção?

A situação foi a seguinte: Um pessoa (mal intencionada) que tinha acesso ao banco de dados da empresa, insatisfeita por algum motivo que não vem ao caso agora, excluiu algumas tabelas do banco de dados com o objetivo de prejudicar a empresa e seus dados.

Por sorte, o problema foi resolvido parcialmente pois outro funcionário responsável pelo banco de dados tinha backups atualizados e conseguiu recuperar os dados, mas como provar para seus superiores quem causou essa indisponibilidade do serviço e convece-los de que o que ocorreu não foi uma falha da equipe de banco de dados?

Quando se tem uma política de auditoria habilitada e bem planejada fica fácil, mas quando nenhum desses recursos estão disponíveis, temos uma última carta na manga que é o Default Trace!.

Default Trace, como o nome já diz é um trace padrão que é criado após a instalação do SQL Server e fica habilitado capturando apenas alguns pequenos conjuntos de eventos, a intenção é capturar eventos que pode ser causas para problemas de estabilidade do sistema, etc. O eventos capturados são alterações ao banco de dados, erros, e para nossa sorte este captura  as operações de CREATE, ALTER e DROP em objetos, dentre outros tipos de eventos. O arquivo de saída do Default Trace, fica localizado dentro do diretório de instalação do SQL Server dentro do diretório Log. O arquivo pode atingir até 20 MB e um máximo de 5 arquivos.

Para simular o cenário proposto, vamos criar um banco de dados e uma tabela e logo após criaremos um login e usuário para excluir a tabela de exemplo.

CREATE DATABASE Producao

USE Producao

CREATE TABLE VENDAS
(
    ID INT IDENTITY(1,1),
    PRODUTO VARCHAR(30)
)

INSERT INTO VENDAS VALUES(‘A’)
INSERT INTO VENDAS VALUES(‘B’)
INSERT INTO VENDAS VALUES(‘C’)

Criando o login e usuário de exemplo.

USE [Producao]
GO
–Criando o login e usuario de teste.
CREATE LOGIN [Joao] WITH PASSWORD=‘password’,
DEFAULT_DATABASE = [Producao],CHECK_POLICY=OFF
GO
CREATE USER [Joao] FOR LOGIN [Joao] WITH DEFAULT_SCHEMA=[dbo]
GO
–Dando permissao para o usuario na tabela VENDAS.
GRANT CONTROL ON [dbo].[VENDAS] TO [Joao]
GO

Agora, que criamos o usuário vamos fazer logon utilizando o usuário e excluir a tabela. Após excluir a tabela vamos utilizar a função fn_trace_gettable para armazenar os dados do trace dentro de uma tabela temporária e analisar os dados.

USE Producao

–Excluindo a tabela VENDAS com o usuario Joao
DROP TABLE VENDAS

–Retornando os dados do trace.
SELECT * INTO temp_trc
FROM fn_trace_gettable (‘D:\MSSQL10.MSSQLSERVER\MSSQL\Log\log.trc’,default);

Agora com os resultados do trace dentro da tabela temporária temp_trc vamos selecionar os dados relativos ao usuário Joao, para recuperar as operações realizadas por este.

SELECT   DB_NAME(DatabaseID) AS [Database]
        ,HostName
        ,ApplicationName
        ,LoginName
        ,StartTime
        ,ObjectID
        ,EventClass
        ,ObjectName
        ,DatabaseName
FROM temp_trc
WHERE LoginName = ‘JOAO’

Podemos ver o resultado da query acima, e observe o EventClass 47 que significa Object:Deleted conforme a tabela relativa aos eventos que pode ser encontrada no BOL, se estiver abrindo o arquivo de trace utilizando o SQL Server Profiler é mostrado como o EventClass Object:Deleted.

image

Tabela de eventos do BOL.

47 Object:Deleted Indicates that an object has been deleted, such as in DROP INDEX and DROP TABLE statements.

Desta forma podemos ver que o Default Trace nos foi útil, capturando o evento e provando que o usuário Joao excluiu a tabela VENDAS, com detalhes de data e hora de execução da operação, aplicação utilizada, banco de dados, etc.

É bom lembrar que o Default Trace é limitado para apenas os eventos pré-configurados, e que este não é a solução ideal para auditoria, pois é criado apenas 5 arquivos e pode serem sobrescritos a cada restart do serviço SQL Server. Para uma solução eficiente e completa de auditoria, pode ser utilizar a feature presente no SQL Server 2008 de qual possui diversos recursos.

Espero que seja útil, abraços!

Advertisements

5 Responses to Salvo pelo Default Trace!

  1. alexbarbosa says:

    Otimo post!!!

    Alex

    http://www.alexberre.com

  2. Leonardo says:

    Felipe,
    é bem interessante mesmo esse default trace, inclusive estou pensando em aumentar o tamanho padrão dele, de 20 MB pra 100MB, você sabe como proceder para realizar essa alteração?

  3. HUDSON says:

    Ótimo Post !!!
    Parabéns pelo blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: