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