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

Curso Administração de banco de dados SQL Server 2008.

Olá Pessoal, 

Hoje trago uma notícia boa, foi confirmado o meu curso sobre Administração de banco de dados SQL Server 2008 na faculdade Unipac Teófilo Otoni, irei dar um curso rápido e introdutório sobre o SQL Server 2008, revisando alguns conceitos sobre bancos de dados e SQL Server.

O Curso será oferecido para as tumas de Sistemas de Informação e Ciencia da Computação nos dias 19 e 20 de Junho, vamos iniciar com uma introdução  teórica sobre o que é o SQL Server 2008, a família de produtos, principais edições e alguns fundamentos sobre arquitetura. Logo após iremos abortar algumas novas funcionalidades do SQL Server 2008 voltadas para a administração do banco dados como Auditoria, Resource Governor, Compressão de dados e Backup, Policy-Based Management e claro Powershell.

Conversando com o Laerte Junior um dos maiores influenciadores e pioneiros de Powershell e SQL Server no Brasil e no mundo, ele me autorizou a mostrar seus scripts e utiliza-los, dos quais serão de grande utilidade para os alunos que terão uma visão do poder do Powershell integrado ao SQL Server, (Claro que irei mostrar e mencionar o autor).

Segue abaixo os  tópicos que serão abordados no curso:      sql_se10

  • Pré-Requisitos e Instalação do Microsoft SQL Server 2008.
    • Preparar o ambiente de sistema operacional pra instalação do produto.
    • Verificar requisitos de software e hardware necessários.
    • Verificar componentes do SQL Server 2008 a serem instalados.
    • Selecionar funcionalidades do SQL Server 2008 a serem instaladas.
    • Especificar diretório para instalação de binários do SQL Server, definir instalação de instância nomeada ou padrão.
    • Definir configurações de segurança e serviços.
    • Especificar modos de autenticação.
  • Configuração de opções da instância.
    • Configuração de memória e processadores.
    • Definição dos diretórios de backup, e arquivos de banco de dados de sistema.
    • Configuração e otimização do banco de dados de sistema tempdb.
    • Configuração da instância para acesso remoto.
    • Definir configuração padrão para arquivos de log e dados.
    • Configurar opções de cursores, paralelismo, locks, etc.
  • Configuração de segurança e auditoria.
    • Criação de Logins para acesso a instância.
    • Entender as Fixed Server Roles e permissões de acesso.
    • Atribuir Logins a Fixed Server Roles.
    • Criar objetos de auditoria no nível de instância e bancos de dados.
    • Configurar auditoria para escrever em logs de aplicação, segurança e arquivos do Windows.
  • Criação e configuração de bancos de dados.
    • Criação do banco de dados, utilizando o SQL Server Management Studio e Transact-SQL.
    • Criação e configuração de Files e Filegroups.
    • Configuração do log de transação e melhores práticas.
    • Configuração de opções automáticas para manutenção do banco de dados e performance.
    • Configuração de opções para verificação de corrupção do banco de dados.
  • SQL Server Integration Services para importar e exportar dados.
    • Entender os conceitos do processo de ETL (Extract Transform Load)
    • Entender os conceitos a  importação a partir de outras fontes de dados como Oracle, Access, Excel,
      etc.
    • Utilizar o SQL Server  Integration Services para  importar massa de dados a partir de um arquivo de
      texto.
    • Criar tabelas, configurar colunas e data types para receber os dados.
    • Configurar agendamentos para pacotes Integration Services
  • Backup e Recover de base de dados.
    • Entender o conceito de Backup e Recover de banco de dados.
    • Entender os tipos de backup e estratégias de recuperação.
    • Criar backup Full, Differential, Log de Transação.
    • Criar backups comprimidos.
    • Entender o que é um Database Snapshot.
    • Criando e restaurando um banco de dados a partir de um Snapshot.
    • Técnicas de Disaster Recover.
    • Restaurar e recuperar bancos de dados em caso de falha.
    • Recuperando bancos de dados corrompidos.
  • Configurar o Resource Governor para gerenciar recursos do servidor.
    • Entender como funciona o Resource Governor.
    • Criar funções de classificação e Workloads Groups.
    • Configurar a alocação de Pool´s utilizados pelos Workloads Groups.
    • Monitorar a utilização de recursos do servidor utilizando o Performance Monitor.
  • Ferramentas para monitoração do SQL Server 2008.
    • Monitorar a atividade do servidor utilizando o Activity Monitor.
    • Monitorar consultas e instruções enviadas ao servidor utilizando o SQL Server Profiler.
    • Utilizar Dynamics Management View´s para consultar metadados de objetos do SQL Server.
  • Gerenciamento baseado em políticas e Compressão de dados.
    • Entender o conceito de Gerenciamento baseado em políticas no SQL Server 2008.
    • Criar políticas e especificar objetos alvos.
    • Entender o conceito de compressão de dados.
    • Utilizar a compressão de dados a nível de página.
    • Utilizar a compressão de dados a nível de linhas.
  • Powershell e SQL Server 2008
    • Como funciona a linguagem de script Powershell e sua integração com o SQL Server.
    • Escrevendo scripts para automatizar tarefas no SQL Server.
    • Visualizando  alguns  exemplos  da  usabilidade  da  linguagem  Powershell  integrada  ao  SQL Server.

É isso ai pessoal, estou ancioso por esse dia e acredito que será muito bom aprender e compartilhar meu humilde conhecimento com os amigos, espero que gostem, até lá 🙂