Conhecendo a Auditoria no Microsoft SQL Server 2008, R2 e 2012.

Olá pessoal, boa tarde.

Nossa que correria, esta semana esta complicada, mas faz parte.

Por acaso em algum momento no decorrer de suas atividades, alguém já perguntou como podemos ou se é possível realizar auditoria no Microsoft SQL Server?

 

Introdução

Posso dizer que essa é uma daquelas perguntas genéricas que sempre sofremos, principalmente dos curiosos ou aqueles que gostam de colocar uma pulginha atrás da orelha dos colaboradores da empresa, em muitos casos, colocando a Equipe de TI num beco sem saída ou sem credibilidade perante a direção.

Pensando justamente nisso e também se levando em consideração um fato que eu me deparei nos últimos dias aqui na empresa, elaborei este artigo com objetivo de ilustrar e esclarecer um pouco sobre como o Microsoft SQL Server trabalha com Auditoria, algo que não se limita a registrar o que esta sendo manipulado ou processado, mas sim apresentar e comprovar o que foi feito, quando e como foi.

 

Para isso, vou começar destacando um pouco o significado da palavra Auditoria.

Auditoria

Auditoria é um exame cuidadoso e sistemático das atividades desenvolvidas em determinada empresa ou setor, cujo objetivo é averiguar se elas estão de acordo com as disposições planejadas e/ou estabelecidas previamente, se foram implementadas com eficácia e se estão adequadas (em conformidade) à consecução dos objetivos.

 

Achados de auditoria

É toda prova obtida pelo auditor, obtidos com a aplicação dos procedimentos de auditoria, para avaliar se os critérios estabelecidos estão sendo ou não atendidos. Ou seja, são fatos resultantes dos programas de auditoria que remetem a deficiências encontradas na entidade auditada.

Seus requisitos básicos são:

  • Mostra a relevância do fato;
  • Ser respaldado nos papéis de trabalho;
  • Ser objetivo;
  • Amparar as conclusões e recomendações;
  • Ser convincente a uma pessoa estranha ao processo.

 

Evidência de auditoria

Evidência de auditoria é o conjunto de fatos comprovados, suficientes, competentes e pertinentes, e por definição, mais consistentes que os achados, em função de determinadas características:

 

Suficiência: A evidência deve ser convincente à pessoas leigas, permitindo-as chegar às mesmas conclusões do auditor.

 

Validade: Deve dar credibilidade e suporte à conclusão do auditor.

 

Relevância: Deve ter relação com os objetivos da auditoria.

 

Objetividade: Deve ser objetiva e respaldar as conclusões do auditor de forma mais profunda do que a simples aparência.

evidência de auditoria é classificada segundo os procedimentos que a originaram. Assim, temos:

  • Evidência física: Obtida em decorrência de uma inspeção física ou observação direta de pessoas, bens ou transações. Normalmente é apresentada sob a forma de fotografias, gráficos, memorandos descritivos, mapas, amostras físicas etc.
  • Evidência documental: É aquela obtida dos exames de ofícios, contratos, documentos comprobatórios (notas fiscais, recibos, duplicatas quitadas, etc.) e informações prestadas por pessoas de dentro e de fora da entidade auditada, sendo que a evidência obtida de fontes externas adequadas é mais fidedigna que a obtida na própria organização sob auditoria.
  • Evidência testemunhal: É aquela decorrente da aplicação de entrevistas e questionários.
  • Evidência analítica: Decorre da conferência de cálculos, comparações, correlações e análises feitas pelo auditor, dentre outras.

 

Você pode ter observado que alguns conceitos sobre Auditoria foram destacados anteriormente, mas se começarmos a pensar em Auditoria no nível de Banco de Dados, mais especificamente para o SQL Server. A seguir destaco os principais conceitos e elementos envolvidos a Auditoria no SQL Server.

 

Auditoria no Microsoft SQL Server

O processo de auditoria de uma instância do SQL Server ou Banco de dados, basicamente tem como finalidade envolver eventos e mecanismos para rastreamento e log. Os recursos de Auditoria estão disponíveis nas edições Enterprise do SQL Server 2008, R2 e 2012, existem diversos níveis de autoria que podem ser aplicados, como também, funcionalidades que permitem comprovar e evidenciar os itens que foram catalogados e achados pelos eventos que envolvem todo da Auditoria.

 

Componentes de Auditoria no Microsoft SQL Server

Auditar basicamente consiste em utilizar os componentes existentes no Microsoft SQL Server nos níveis de Servidor e Banco de Dados. Estes componentes de auditoria são mesclados durante sua execução com objetivo de produzir a saída dos dados coletados, de forma interpretável para o usuário, possibilitando evoluir com este conjunto de dados em sua análise gerando gráficos, relatórios, planilhas ou qualquer outra forma de estudo e compreensão. Podemos dizer que existem basicamente 4 componentes diretamente relacionadas com a Auditoria:

ü  Objeto Auditoria (Audit): O objeto Audit coleta as ações no nível do servidor e/ou do banco de dados conforme o monitoramento da Auditoria em execução.

 

ü  Especificação da Auditoria no Nível de Servidor (Server Audit Specification): Trata-se do objeto criado diretamente na instância SQL Server que nos oferece a capacidade de auditor os eventos que estão sendo processados. Vale ressaltar que este mesmo objeto é utilizado pelos componentes de Auditoria no Nível de Banco de Dados.

 

ü  Especificação da Auditoria no Nível de Banco de Dados (Database Audit Specification): Corresponde ao objeto criado a nível de Banco de Dados, que permite coletar e catalogar os procedimentos que estão sendo executados pela instância do SQL Server, atráves dos eventos identificados pelo objeto Audit.

 

ü  Destino (Destination): Os resultados de uma auditoria são enviados ao destino, que pode ser um arquivo, o log de eventos de Segurança do Windows ou o log de eventos de Aplicativo do Windows. Importante destacar que os dados gravados em arquivos diretamente em file system, podem ser criptografados com a combinação de recursos como, por exemplo, Bit Locker, ou aplicar o controle de acesso através dos controles de permissão existentes no Windows.

 

Após apresentar os conceitos e principais elementos relacionados à Auditoria, vamos começar a trabalhar de forma prática com este recurso, fazendo uso do componente Server Audit Specification e Database Audit Specification.

 

Criando o ambiente para aplicar a Auditoria

 

Antes de realizarmos a criação do ambiente, quero destacar todos os componentes e características de cada um deles. Basicamente nosso ambiente no nível de SQL Server será composto por:

ü  Database: TestAuditDB;

ü  Database Audit Specification: MyDatabaseAudit;

ü  Schema: MySchemaAudit;

ü  Tables: Produtos e Movimentacao;

ü  Trigger: T_MovimentacaoSaldo; e

ü  Server Audit: MyServerAudit.

Além disso, teremos a necessidade de armazenar o arquivo de auditoria que estará sendo gerado pelo próprio objeto de auditor do SQL Server. Para isso será necessário criarmos uma pasta em nossa unidade de disco. Selecione qualquer unidade de disco que possua espaço para armazenamento, como também, que os usuários do SQL Server possam ter acesso de leitura e gravação. Para este ambiente de realizei a criação na Unidade C:\ da pasta chamada: AuditSQL e liberei as permissões para o usuário local da minha máquina. Vamos então realizar a criação do nosso ambiente, utilizando o Código 1 apresentado abaixo:

 

— Código 1 – Criação do Ambiente para uso da Auditoria —

 

— Criando o Banco de Dados —

CREATE DATABASE TestAuditDB;

Go

 

— Acessando —

USE TestAuditDB;

Go

 

— Criando o Schema —

CREATE SCHEMA MySchemaAudit;

Go

 

— Criando a Tabela MySchemaAudit.Produtos —

Create Table MySchemaAudit.Produtos

(Codigo Int Identity(1,1),

Descricao VarChar(20),

Saldo Int)

Go

 

— Criando a Tabela MySchemaAudit.Movimentacao —

Create Table MySchemaAudit.Movimentacao

(Codigo Int Identity(1,1),

CodProduto Int,

TipoMovimentacao Char(1),

Valor Int)

Go

 

— Acessando o Banco de Dados Master —

USE master;

Go

 

— Criando o Server Audit para armazenar os dados em arquivo, no Banco de Dados de Sistema – Master —

Create Server Audit MyServerAudit

TO FILE ( FILEPATH =’C:\AuditSQL\’)

Go

 

— Ativando o Server Audit após a sua criação —

Alter Server Audit MyServerAudit WITH (STATE = ON);

Go

 

— Criando o Database Audit Specification no Banco de Dados TestAuditDB —

USE TestAuditDB;

Go

 

 

— MyDatabaseAudit —

Create Database Audit Specification MyDatabaseAudit

For Server AUDIT MyServerAudit

Add (Select On Schema::MySchemaAudit BY public),

Add (Insert On Schema::MySchemaAudit BY public)

With (State = On);

Go

Se analisar o ambiente é bastante simples e deve ser mesmo, o objetivo deste artigo não é criar um cenário grandioso, muito pelo contrário, o que eu quero é justamente mostrar os dados que a Auditoria coleta e posteriormente podem ser utilizamos.

Observações:

  1. Podemos notar que as tabelas Produtos e Movimentacao foram criadas tendo como MySchema como schema deste objetos, algo necessário para que o Server Audit e Database Audit possam conseguir coletar os eventos que estes objetos estão executando, neste caso, fica fácil entender que a auditoria necessita que seja objetos estejam vinculados com Schemas dedicados e reconhecidos pelo objeto Audit.

 

  1. Outro detalhe nosso Server Audit estará enviando para a pasta C:\AuditSQL todos os dados coletados, sendo estes armazenados normalmente em arquivos com seu nome se iniciando pelo nome do Server Audit, no nosso caso, MyServerAudit.

 

 

  1. Nosso Database Audit foi criado para catalogar os eventos de Select e Insert para os objetos vinculados com o schema MySchemaAudit, desta forma, estes elementos tem como função trabalharem como filtros para somente registros e apresentar as informações referentes aos eventos que os mesmos esteja envolvidos.

 

Validando os Componentes e Ambiente de Auditoria

Observe que foi criado na Guia Security da nossa instância SQL Server o MyServerAudit, conforme apresenta a Figura 1:

Auditoria1

 

Figura 1 – Server Audit MyServerAudit criado na instância SQL Server.

Agora vamos validar se o Database Audit Specification foi criado corretamente, para isso acesse o Banco de Dados TestAuditDB, expanda a estrutura interna e posteriormente a Guia Security em seguida localize a Guia Database Audit Specificantion, provavelmente deverá existir o objeto MyDatabaseAudit, conforme apresenta a Figura 2:

Auditoria1 Auditoria2

 

Figura 2 – Database Audit Specification criado no nível de Banco de Dados.

Legal, legal, todos os objetos estão criados e configurados, vamos fazer um pequeno teste. Como criamos um Server Audit, o Microsoft SQL Server realizou a criação de alguns arquivos de auditoria na nossa pasta C:\AuditSQL, conforme apresenta a Figura 3:

Auditoria3

 

Figura 3 – Relação de arquivos utilizados pelo Server Audit para catalogar os eventos de auditoria.

Outra forma de validar se o processo de auditoria foi criado corretamente é acessar os Logs de Eventos de Auditoria criado para nosso Server Audit – MyServerAudit.

Estes logs podem ser consultados diretamente nas propriedades do Server Audit, conforme apresenta a Figura 4:

Auditoria4

 

Figura 4: Visualizador do Log de Eventos de Auditoria.

Como o SQL Server é uma ferramenta extremamente flexível, que apresenta um leque enorme de recursos e funcionalidades, não poderíamos deixar passar em branco os recursos Transact-SQL que podem ser utilizados para obter mais informações sobre Auditoria e seus componentes, sendo assim destaco:

 

 

System Table

ü  Sys.Server_Audits: Contém uma linha para cada auditoria do SQL Server em uma instância de servidor;

 

ü  Sys.Server_File_Audits: Contém informações estendidas sobre o tipo de auditoria de arquivo em uma auditoria do SQL Server, em uma instância de servidor;

 

ü  Sys.Server_Audit_Specifications: Contém informações sobre as especificações de auditoria do servidor em uma auditoria do SQL Server de uma instância de servidor;

 

ü  Sys.Server_Audit_Specification_Details: Contém informações sobre os detalhes (ações) de especificação de auditoria de servidor em uma auditoria do SQL Server, em uma instância de servidor;

 

ü  Sys.Database_Audit_Specifications: Contém informações sobre as especificações de auditoria de banco de dados em uma auditoria do SQL Server de uma instância de servidor;

 

ü  Sys.Database_Audit_Specification_Details: Contém informações sobre os detalhes (ações) de especificação de auditoria de banco de dados em uma auditoria do SQL Server, em uma instância de servidor;

 

Dynamic Management View

ü  Sys.dm_Server_Audit_Specification: Retorna uma linha de informações para cada auditoria de servidor, apresentando seu estado atual na auditoria corrente.

 

ü  Sys.dm_Audit_Actions: Retorna uma linha para cada ação de auditoria que pode ser reportada no log de auditoria e para cada grupo de ação de auditoria que pode ser configurado como parte do SQL Server Audit.

 

ü  Sys.dm_audit_class_type_map: Retorna uma tabela que mapeia o campo class_type do log de auditoria para o campo class_desc em sys.dm_audit_actions.

 

Function

ü  fn_get_audit_file: Retorna informações sobre o arquivo de auditoria criado para o Server Audit.

 

Gerando dados para coleta de Auditoria

Para finalizar este artigo, vamos gerar uma massa de dados para que o Server Audit e Database Audit realizem suas respectivas coletas de dados e posteriormente através dos Logs de Eventos de Auditoria e da Função fn_get_audit_file possamos analisar os dados auditados. Para isso vamos executar o Código 2, apresentando abaixo:

— Código 2 – Gerando a massa de dados —

— Inserindo 100 Linhas de Registro —

Insert Into MySchemaAudit.Produtos Values(‘Produto -‘+Convert(VarChar(3),@@Identity),0)

Go 100

 

— Fazendo lançamentos de entrada —

Insert Into MySchemaAudit.Movimentacao Values(2,’E’,10)

Insert Into MySchemaAudit.Movimentacao Values(2,’E’,15)

Insert Into MySchemaAudit.Movimentacao Values(2,’E’,5)

Insert Into MySchemaAudit.Movimentacao Values(2,’E’,22)

Insert Into MySchemaAudit.Movimentacao Values(2,’E’,10)

Go

 

–Fazendo lançamentos de saída —

Insert Into MySchemaAudit.Movimentacao Values(2,’S’,8)

Insert Into MySchemaAudit.Movimentacao Values(2,’S’,5)

Insert Into MySchemaAudit.Movimentacao Values(2,’S’,3)

Insert Into MySchemaAudit.Movimentacao Values(2,’S’,2)

Insert Into MySchemaAudit.Movimentacao Values(2,’S’,1)

Go

 

Insert Into MySchemaAudit.Movimentacao Values(8,’E’,10)

Insert Into MySchemaAudit.Movimentacao Values(9,’E’,15)

Insert Into MySchemaAudit.Movimentacao Values(1,’E’,5)

Insert Into MySchemaAudit.Movimentacao Values(3,’E’,22)

Insert Into MySchemaAudit.Movimentacao Values(22,’E’,10)

Go

 

–Fazendo lançamentos de saída —

Insert Into MySchemaAudit.Movimentacao Values(8,’S’,8)

Insert Into MySchemaAudit.Movimentacao Values(9,’S’,5)

Insert Into MySchemaAudit.Movimentacao Values(1,’S’,3)

Insert Into MySchemaAudit.Movimentacao Values(3,’S’,2)

Insert Into MySchemaAudit.Movimentacao Values(22,’S’,1)

Go

 

— Consultando os Dados —

Select * from MySchemaAudit.Produtos

Select * from MySchemaAudit.Movimentacao

Como da operação processada anteriormente será coletada e catalogada pelos elementos de Auditoria, cada linha de comando processada acima, foi armazenada, sendo assim, teremos nosso Log de Eventos de Auditoria terá mais informações que poderemos analisar, conforme apresenta a Figura 5 a seguir:

Auditoria5

 

Figura 5 – Visualizador do Log de Eventos de Auditoria apresentando todos os eventos coletados e catalogados.

 

Nossa outra forma de visualizar estes mesmos dados é através da Function fn_get_audit_file, conforme apresenta o Código 3 abaixo:

 

— Código 3 – Consultando os dados coletados pelos componentes de Auditoria –

SELECT * FROM fn_get_audit_file(‘C:\AuditSQL\MyServer*.sqlaudit’,default,default);

Go

Após executar esta função você terá o mesmo conjunto de informações apresentadas nos Logs de Eventos de Auditoria.

————————————————————————————————————————————————————-

Com isso chegamos ao final de mais um artigo. Concluído, parecia que não iria conseguir finalizar este conteúdo, devido a sua complexidade e particularidades. Acredito que eu consegui me superar e compartilhar com vocês mais um pouco de uma grande funcionalidade existente no Microsoft SQL Server.

Mais uma vez obrigado por sua visita, nos encontramos em breve.

Até mais.

Autor: Junior Galvão - MVP

Profissional com vasta experiência na área de Tecnologia da Informação e soluções Microsoft. Graduado no Curso Superior em Gestão da Tecnologia de Sistemas de Informação pela Uninove - Campus São Roque. Pós-Graduado no Curso de Gestão e Engenharia de Processos para Desenvolvimento de Software com RUP na Faculdade FIAP - Faculdade de Informática e Administração Paulista de São Paulo. Pós-Graduado em Gestão da Tecnologia da Informação Faculdade - ESAMC Sorocaba. Cursando Mestrado em Ciências da Computação - UFSCar - Campus - Sorocaba. Formação MCDBA Microsoft, autor de artigos acadêmicos e profissionais postados em Revistas, Instituições de Ensino e WebSistes. Meu primeiro contato com tecnologia ocorreu em 1995 após meus pais comprarem nosso primeiro computador, ano em que as portas para este fantástico mundo se abriram. Neste mesmo ano, comecei o de Processamento de Dados, naquele momento a palavra TI não existia, na verdade a Tecnologia da Informação era conhecida como Computação ou Informática, foi assim que tudo começou e desde então não parei mais, continuando nesta longa estrada até hoje. Desde 2001 tenho atuado como Database Administrator - Administrador de Banco de Dados - SQL Server em tarefas de Administração, Gerenciamento, Migração de Servidores e Bancos de Dados, Estratégias de Backup/Restauração, Replicação, LogShipping, Implantação de ERPs que utilizam bancos SQL Server, Desenvolvimento de Funções, Stored Procedure, Triggers. Experiência na Coordenação de Projetos de Alta Disponibilidade de Dados, utilizando Database Mirroring, Replicação Transacional e Merge, Log Shipping, para versões: 2000, 2005, 2008, 2008 R2, 2012 e 2014. Atualmente trabalho como Administrador de Banco de Dados no FIT - Instituto de Tecnologia da Flextronics, como também, Consultor em Projetos de Tunnig e Performance para clientes. Desde 2008 exerço a função de Professor Universitário, para as disciplinas de Banco de Dados, Administração, Modelagem de Banco de Dados, Programação em Banco de Dados, Sistemas Operacionais, Análise e Projetos de Sistemas, entre outras. Possuo titulação Oficial Microsoft MVP - SQL Server renovada desde 2007.

Um comentário em “Conhecendo a Auditoria no Microsoft SQL Server 2008, R2 e 2012.”

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s