Auditoria de um ambiente SQL Server 2008 R2

CÓDIGO DA SESSÃO: DBP302
Auditoria de um ambiente SQL
Server 2008 R2
Nilton Pinheiro
Microsoft MVP | MCITP | MCSE | MCDBA | MCTS
http://www.mcdbabrasil.com.br/
http://www.twitter/nilton_pinheiro (niltonpinheiro é pirata :) )
Herleson Pontes
MCT | MCITP | MCSA | MCTS
http://www.herlesonpontes.com.br/
http://www.twitter.com/herlesonpontes
Agenda
Visão Geral
Auditoria nas versões anteriores
Auditando Dados com Tabela Espelho
Cláusula OUTPUT
Default Trace (Profiler)
DDL Triggers
Event Notification
Auditoria no SQL Server 2008 R2
Change Tracking
Change Data Capture
SQL Audit
2
Visão Geral
Porque auditar uma solução de dados?
Auditar modificações de schema
Auditar modificações de dados
Analisar as atividades realizadas no seu banco e
detectar possíveis falhas na segurança (Quem acessou
seu dados e quando?)
Diagnosticar falhas ocasionadas por alterações na
configuração do servidor (Quando a configuração
mudou?)
Entender a utilização dos dados da sua base por parte
dos usuários do sistema
Atender a requisitos de auditoria SOX
3
Auditoria nas versões anteriores
Auditando Dados com Tabela Espelho
Técnica bastante utilizada nas auditorias de dados
SQL Server 2000 ou anteriores
Auditoria de Operações DML
Consiste em ter tabelas idênticas com triggers
monitorando as operações
A tabela espelho armazena os dados afetados pelas
operações
Fácil introdução de erros no processo
Requer alteração no modelo lógico
Impacto na performance (cuidado com as triggers!!)
4
Exemplo
5
Auditoria nas versões anteriores
Auditando dados com a cláusula OUTPUT
Auditoria de Dados no SQL Server 2005
SQL Server 2005 e superiores
Auditoria de Operações DML
Ainda requer a utilização de tabela espelho
A tabela espelho armazena os dados afetados pelas
operações
Requer alteração de código das operações
Impacto em performance é praticamente zero
Não requer uso de triggers !!
6
Exemplo OUTPUT
7
Exemplo INSERT
8
Exemplo UPDATE
9
Exemplo DELETE
10
Auditoria nas versões anteriores
Default Trace
Auditoria de operações DDL e eventos de trace
Disponível a partir do SQL Server 2005
ON por default e controlado pela sp_configure ‘default
trace enable’
Armazenado no mesmo caminho que o Error Log
Caminho pode ser alterado pelo parâmetro de
inicialização -e utilizando o SSCM
Captura principalmente eventos relacionados a
auditoria
A função fn_trace_gettable foi melhorada para
permitir ler o arquivo ainda em execução
11
Exemplo Default Trace
12
Auditoria nas versões anteriores
DDL Triggers
Utilizada para auditar operações DDL
Disponível no SQL Server 2005 e superiores
São disparadas em resposta aos eventos DDL
Atua no nível servidor ou banco de dados (Ex. ALTER
TABLE, DROP TABLE, ALTER LOGIN, DROP LOGIN)
Ao invés da tabelas Inserted e Deleted, utiliza-se a
funcão EVENTDATA() para capturar informações sobre
os eventos (em formato XML)
Possibilita impedir a execução de operações DDL
Permite monitorar as alterações de schemas
É síncrono !!!
13
Exemplo DDL Triggers
14
Auditoria nas versões anteriores
Event Notification
Auditoria de operações DDL e eventos
Disponível no SQL Server 2005 e superiores
Sistema de envio de mensagens utilizando a
infraestrutura do Service Broker
Atua capturando eventos que ocorrem no nível
servidor ou banco de dados
Além dos eventos DDL disponíveis nas DDL Triggers, o
Event Notification também permite a captura de
eventos de trace como:
Audit_Login, Audit_Login_Failed, Lock_Deadlock,
Data_File_Auto_Grow, Blocked_Process_Report
É assíncrono !!!
17
Auditoria nas versões anteriores
Event Notification
Criação de um Event Notification
Criar uma FILA (QUEUE)
Criar um SERVIÇO (SERVICE) em uma FILA
Criar uma ROTA (ROUTE) para o SERVIÇO
Criar um EVENT NOTIFICATION para um SERVIÇO
Criar um SERVICE PROGRAM para processar as
notificações existentes na FILA
Para a auditoria de eventos de nível servidor, usuário
guest deve ter acesso ao msdb
EXEC('use msdb CREATE USER guest')
DENY EXECUTE,SELECT,INSERT,UPDATE,DELETE ON DATABASE::msdb to guest
18
Event Notification
19
Agenda
Visão Geral
Auditoria nas versões anteriores
Auditando Dados com Tabela Espelho
Cláusula OUTPUT
Default Trace (Profiler)
DDL Triggers
Event Notification
Auditoria no SQL Server 2008 R2
Change Tracking
Change Data Capture
SQL Audit
20
Auditoria no SQL Server 2008 R2
Novos recursos para auditoria
O SQL Server 2008 R2 trás
três tecnologias distintas de
auditoria
Cada uma responde a
diferentes questões sobre as
modificações realizadas
Estas tecnologias permitem
o desenvolvimento de uma
solução robusta de dados
Integração com o Windows
Server 2008 R2
21
Change Tracking
Visão geral do recurso
Recurso que armazena informações sobre as
modificações feitas nos registros de uma tabela
O rastreamento das alterações se dá através do
número da versão de um registro
Disponível em todas as edições (inclusive Express)
Exibe as mudanças feitas nos registros da tabela
Os dados sobre as modificações são armazenados
em tabelas do sistema do próprio banco
O administrador define quais tabelas terão suas
alterações gerenciadas por este recurso
22
Change Tracking
Principais características
Permite visualizar quais registros foram
afetados, as colunas envolvidas neste processo e
as operações que modificaram esses registros
Dados sobre as modificações são vinculadas aos
registros da tabela através da chave primária
Entretanto, este recurso não exibe como os dados
foram alterados e nem o usuário que os alterou
As alterações são gravadas de acordo com a
execução das transações
A tabela é populada de forma síncrona
23
Change Tracking
Arquitetura interna
Database Commit Table
• xact_id: Identificador da
transação
• commit_ts: Horário da
gravação
Tabela Base
• xact_id: Identificador da
última transação que
modificou o registro
(campo oculto)
24
Change Table
•
•
•
•
xact_id
xact_sequence
xact_operation
PK do registro modificado
Change Tracking
Etapas durante a execução de transações
25
Quando uma transação é
iniciada, o atributo xact_id
é gerado
Em seguida, quando os
registros da tabela base são
modificados, o xact_id da
transação é utilizado pelas
tabela base e modificação
Por fim, quando a
transação faz o commit, o
atributo commit_ts é então
gerado
Change Tracking
Configuração
Nível Banco de Dados
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON;
Ativa o banco de dados para registrar as alterações
Recomendado ativar o SNAPSHOT ISOLATION
Nível de tabela
ALTER TABLE HumanResources.Employee
ENABLE CHANGE_TRACKING;
Diz ao QE para monitorar as mudanças na tabela
Permite identificar quais colunas foram referenciadas
em um UPDATE
26
Change Tracking
27
Change Data Capture
Visão geral do recurso
Recurso que armazena informações sobre as
modificações nos registros de uma tabela e
mostra como os dados foram alterados
O rastreamento das alterações se dá através do
acesso ao log de transações do banco de dados
Os dados sobre as modificações são copiados para
tabelas pertencentes a um esquema chamado cdc,
localizado no banco de dados auditado
Disponível apenas nas edições Enterprise, Developer
e Datacenter
28
Change Data Capture
Principais características
Permite visualizar o histórico das alterações
efetuadas nos registros de uma tabela
Exibe como os valores de um registro foram
alterados dentro de um intervalo de LSN
Este recurso não exibe o usuário que realizou as
modificações
O SQL Server fornece funções que permitem ao
administrador resgatar o intervalo LSN desejado
a partir do arquivo de log de transações
29
Change Data Capture
Processo de gerenciamento das modificações
As alterações são gravadas
em intervalos de tempo
após a execução das
transações
Utiliza o agente log reader
(Replicação)
Cria duas tarefas no SQL
Server Agent
Uma para iniciar o agente de
leitura do log
Outro para remover
alterações expiradas
30
Change Data Capture
Configuração
Nível Banco de Dados
EXEC sys.sp_cdc_enable_db
Ativa o CDC no metadado
Cria um database schema chamado “cdc”
Cria tabelas no schema
Cria dois jobs:
Um para disparar o log reader
Um para expurgar dados históricoss
Nível de tabela
EXEC sys.sp_cdc_enable_table
Ativa o CDC para uma dada tabela
Cria uma tabela e duas funções no schema “cdc”
31
Change Data Capture
32
SQL Audit
Visão geral do recurso
Recurso que armazena informações sobre
eventos realizados no servidor e os seus
respectivos usuários
Realiza auditoria granular de eventos executados
nos níveis de servidor e banco de dados
Permite salvar as informações do rastreamento em
arquivo, no log de segurança e no log de aplicações
Disponível somente nas edições Enterprise,
Developer e Datacenter
33
SQL Audit
Principais características
Armazena informações detalhadas sobre a
execução de diversos eventos, com seus
respectivos usuários
Cada evento é chamado de action, e pode ser
classificado em três categorias: Servidor, Banco de
Dados e Auditoria
Este recurso não exibe o estado dos objetos e dados
antes das modificações
34
Eventos envolvendo vários registros possuem
uma coluna que mostra a sequência da
execução dos eventos
SQL Audit
Arquitetura
Alvo
(Destino)
Auditoria
no
Servidor
35
Auditoria
no Banco
de Dados
SQL Audit
36
37
Conteúdo relacionado
DBP308 Sincronizando dados com a nuvem através do SQL Azure Data Sync
e Sync Framework 2.0
DBP304 Distribuição e gerenciamento de aplicações data-tier dentro do
SQL Server 2008 R2 Daniel Camilo
DBP303 Boas práticas para upgrade do Microsoft SQL Server 2000 - 2005 - 2008 2008 R2
DBP305 Estratégias para otimizar a concorrência dentro do Microsoft SQL Server
2008 R2
http://www.herlesonpontes.com.br - @herlesonpontes
http://www.mcdbabrasil.com.br - @nilton_pinheiro
38
© 2008 Microsoft Corporation. Todos os direitos reservados. Microsoft, Windows, Windows Vista e outros nomes de produtos são ou podem ser marcas registradas e/ou marcas comerciais nos EUA e/ou outros países.
Este documento é meramente informativo e representa a visão atual da Microsoft Corporation a partir da data desta apresentação. Como a Microsoft deve atender a condições de mercado em constante alteração, este
documento não deve ser interpretado como um compromisso por parte da Microsoft, e a Microsoft não pode garantir a precisão de qualquer informação fornecida após a data desta apresentação. A MICROSOFT NÃO DÁ
QUALQUER GARANTIA, SEJA ELA EXPRESSA, IMPLÍCITA OU ESTATUTÁRIA, REFERENTE ÀS INFORMAÇÕES DESTA APRESENTAÇÃO.
Por favor preencha a
avaliação