Post on 18-Apr-2015
Nilton PinheiroMicrosoft MVP | MCITP | MCSE | MCDBA | MCTShttp://www.mcdbabrasil.com.br/http://www.twitter/nilton_pinheiro (niltonpinheiro é pirata :) )
Herleson PontesMCT | MCITP | MCSA | MCTShttp://www.herlesonpontes.com.br/http://www.twitter.com/herlesonpontes
Auditoria de um ambiente SQL Server 2008 R2
CÓDIGO DA SESSÃO: DBP302
2
AgendaVisão GeralAuditoria nas versões anteriores
Auditando Dados com Tabela EspelhoCláusula OUTPUTDefault Trace (Profiler)DDL TriggersEvent Notification
Auditoria no SQL Server 2008 R2Change TrackingChange Data CaptureSQL Audit
3
Visão GeralPorque auditar uma solução de dados?
Auditar modificações de schemaAuditar modificações de dadosAnalisar 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 sistemaAtender a requisitos de auditoria SOX
4
Auditoria nas versões anteriores Auditando Dados com Tabela Espelho
Técnica bastante utilizada nas auditorias de dadosSQL Server 2000 ou anterioresAuditoria de Operações DMLConsiste em ter tabelas idênticas com triggers monitorando as operaçõesA tabela espelho armazena os dados afetados pelas operaçõesFácil introdução de erros no processoRequer alteração no modelo lógicoImpacto na performance (cuidado com as triggers!!)
5
Exemplo
6
Auditoria nas versões anteriores Auditando dados com a cláusula OUTPUT
Auditoria de Dados no SQL Server 2005SQL Server 2005 e superioresAuditoria de Operações DMLAinda requer a utilização de tabela espelhoA tabela espelho armazena os dados afetados pelas operaçõesRequer alteração de código das operaçõesImpacto em performance é praticamente zeroNão requer uso de triggers !!
7
Exemplo OUTPUT
8
Exemplo INSERT
9
Exemplo UPDATE
10
Exemplo DELETE
11
Auditoria nas versões anteriores Default Trace
Auditoria de operações DDL e eventos de traceDisponível a partir do SQL Server 2005ON por default e controlado pela sp_configure ‘default trace enable’Armazenado no mesmo caminho que o Error LogCaminho pode ser alterado pelo parâmetro de inicialização -e utilizando o SSCMCaptura principalmente eventos relacionados a auditoriaA função fn_trace_gettable foi melhorada para permitir ler o arquivo ainda em execução
12
Exemplo Default Trace
13
Auditoria nas versões anteriores DDL Triggers
Utilizada para auditar operações DDLDisponível no SQL Server 2005 e superioresSão disparadas em resposta aos eventos DDLAtua 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 DDLPermite monitorar as alterações de schemasÉ síncrono !!!
14
Exemplo DDL Triggers
17
Auditoria nas versões anteriores Event Notification
Auditoria de operações DDL e eventosDisponível no SQL Server 2005 e superioresSistema de envio de mensagens utilizando a infraestrutura do Service BrokerAtua capturando eventos que ocorrem no nível servidor ou banco de dadosAlé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 !!!
18
Auditoria nas versões anteriores Event Notification
Criação de um Event NotificationCriar uma FILA (QUEUE)Criar um SERVIÇO (SERVICE) em uma FILACriar uma ROTA (ROUTE) para o SERVIÇOCriar um EVENT NOTIFICATION para um SERVIÇOCriar um SERVICE PROGRAM para processar as notificações existentes na FILAPara 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
19
Event Notification
20
AgendaVisão GeralAuditoria nas versões anteriores
Auditando Dados com Tabela EspelhoCláusula OUTPUTDefault Trace (Profiler) DDL TriggersEvent Notification
Auditoria no SQL Server 2008 R2Change TrackingChange Data CaptureSQL Audit
21
Auditoria no SQL Server 2008 R2Novos 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
22
Change TrackingVisã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 registroDisponível em todas as edições (inclusive Express)Exibe as mudanças feitas nos registros da tabelaOs 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
23
Change TrackingPrincipais 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áriaEntretanto, 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
24
Change TrackingArquitetura 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)
Change Table• xact_id• xact_sequence• xact_operation• PK do registro modificado
25
Change TrackingEtapas durante a execução de transações
Quando uma transação é iniciada, o atributo xact_id é geradoEm seguida, quando os registros da tabela base são modificados, o xact_id da transação é utilizado pelas tabela base e modificaçãoPor fim, quando a transação faz o commit, o atributo commit_ts é então gerado
26
Change TrackingConfiguração
Nível Banco de DadosALTER DATABASE AdventureWorks SET CHANGE_TRACKING = ON;
Ativa o banco de dados para registrar as alteraçõesRecomendado ativar o SNAPSHOT ISOLATION
Nível de tabelaALTER TABLE HumanResources.EmployeeENABLE CHANGE_TRACKING;
Diz ao QE para monitorar as mudanças na tabelaPermite identificar quais colunas foram referenciadas em um UPDATE
27
Change Tracking
28
Change Data CaptureVisã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 dadosOs dados sobre as modificações são copiados para tabelas pertencentes a um esquema chamado cdc, localizado no banco de dados auditadoDisponível apenas nas edições Enterprise, Developer e Datacenter
29
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 LSNEste 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
30
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 logOutro para remover alterações expiradas
31
Change Data Capture Configuração
Nível Banco de Dados EXEC sys.sp_cdc_enable_db
Ativa o CDC no metadadoCria um database schema chamado “cdc”Cria tabelas no schemaCria dois jobs:
Um para disparar o log readerUm para expurgar dados históricoss
Nível de tabela EXEC sys.sp_cdc_enable_table
Ativa o CDC para uma dada tabelaCria uma tabela e duas funções no schema “cdc”
32
Change Data Capture
33
SQL AuditVisã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 dadosPermite salvar as informações do rastreamento em arquivo, no log de segurança e no log de aplicaçõesDisponível somente nas edições Enterprise, Developer e Datacenter
34
SQL AuditPrincipais 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 AuditoriaEste recurso não exibe o estado dos objetos e dados antes das modificações
Eventos envolvendo vários registros possuem uma coluna que mostra a sequência da execução dos eventos
35
SQL AuditArquitetura
Alvo (Destino)
Auditoria no
Servidor
Auditoria no Banco de Dados
36
SQL Audit
37
38
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 - @herlesonponteshttp://www.mcdbabrasil.com.br - @nilton_pinheiro
© 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