Escrito por Cibelle Castro

Descobrindo Auditoria no SQL SERVER 2008 – Parte 7

Olá Pessoal,

É com muita satisfação que hoje finalmente termino os posts sobre auditoria (“para a nossa alegria”).  Aos que ainda não viram os outros segue os links:

E agora mão a obra! Nesse post vamos criar um Database Audit Specification, o mesmo irá auditar todos os comando de Insert, Update, Delete ou Select. Para criar um Database Audit Specification você deve escolher o banco de dados que deseja fazer a auditoria. No nosso caso será o AdventureWorks.

1) Clique com o botão direito em “Database Audit Specification” e selecione “New Audit Specification”, como mostrado na figura abaixo:

1

2) Na janela “Create Database Audit Specification” você pode aceitar o nome sugerido ou caso queira pode digitar seu próprio nome. Como disse anteriormente acho válido sempre escolhermos um nome que tenha algum sentido, pois a pessoa que for administrar ou dar uma manutenção ou está ali de bobeira olhando o servidor (tomara que seja algum estagiário porque se for alguém que você não tem a menor ideia, pode sentar e chorar! rs). Brincadeiras a parte, na opção Name digite:

DatabaseAuditSpecificationSelectInsertDeleteUpdate.

3) Em seguida selecione a partir da caixa suspensa o Server Audit Object que armazenaremos os dados da auditoria. Dessa vez vamos armazenar os dados da auditoria em um arquivo, utilizaremos o Server Audit Object criado anteriormente. Selecione o Target TargetFileSSMS.

2

4) Na seção “Audit Action Types”, você deve especificar o tipo de ação de auditoria que deseja capturar. No primeiro combo selecione a opção SELECT, com o objetivo de gravar toda atividade do comando SELECT para uma determinada tabela, ou para a base inteira.

3

5) Agora devemos escolher “Object Class”. Vamos auditar mais de uma tabela de um banco especifico então selecione a segunda opção dessa coluna, ou seja, OBJECT.

4

6) Devemos selecionar qual o OBJECT será auditado, para isto clique no botão “…” da coluna Object Name.

5

7)  Na janela “Select Objects” clique no botão “Object Types…”. Escolha o tipo “Tables” e desabilite as outras opções.

6

8) Selecione o botão “Browse..” e escolha as tabelas que serão monitoradas no banco AdventureWorks. Selecione as seguintes tabelas:

  • HumanResources.Employee
  • Person.Contact
  • Production.Location
  • Production.UnitMeasure

Clique no botão OK.

7

9) A próxima etapa será escolher o “Principal Name”, ou seja, qual o usuário que queremos auditar. Nessa opção podemos selecionar Users e Roles. Para fazer isso, clique no botão “…” da coluna “Principal Name” e na janela “Select Objects” clique no botão “Browser…”. Agora você pode ver quais “Principals” pode escolher, nesse caso nos vamos escolher a opção “public”, pois o objetivo dessa auditoria é identificar qualquer pessoas que execute a cláusula SELECT nas tabelas selecionadas.

8

10) Concluímos o nosso primeiro filtro, vamos repetir as operações descritas acima para os comandos INSERT, UPDATE e DELETE.

11) Uma vez concluída as configurações clique em OK para concluirmos a criação do Database Audit Specification.

12) Assim como Server Audit Specification a especificação de banco de dados é criado desabilitado, se você observar verá que ele tem uma seta vermelha indicando que esta desativada, para habilita-la clique sobre o mesmo como botão direito e escolha a opção “Enable Database Audit Specification”.

13) Agora vamos selecionar, inserir, excluir e atualizar alguns registros das tabelas que estamos auditando para ver se tudo aquilo que configuramos esta funcionando conforme o esperado. Execute os scripts:

------------------------------
 --AUDITANDO COMANDO SELECT
 ------------------------------
 USE AdventureWorks
 GO
 SELECT
 C.FirstName + ' ' + C.LastName AS 'Nome Completo',
 E.VacationHours AS 'Férias (horas)',
 E.SickLeaveHours AS 'Afastamento por motivo de doença (horas)',
 E.LoginID AS 'Login'
 FROM
 HumanResources.Employee E
 LEFT JOIN Person.Contact C ON E.ContactID = C.ContactID
 ORDER BY [Nome Completo];
----------------------------
 --AUDITANDO COMANDO SELECT
 ----------------------------
 SELECT * FROM Production.Location
----------------------------
 --AUDITANDO COMANDO UPDATE
 ----------------------------
 UPDATE Person.Contact
 SET FirstName = 'Cibelle Castro',
 emailAddress = 'cibascastro@outlook.com.br'
 WHERE ContactID = 6
----------------------------
 --AUDITANDO COMANDO DELETE
 ----------------------------
 DELETE FROM Production.UnitMeasure WHERE Name = 'Boxes'
 ----------------------------
 --AUDITANDO COMANDO INSERT
 ----------------------------
 INSERT INTO Production.Location  VALUES ('Metal',0.00,0.00,GETDATE())

14) Os dados foram armazenados em um arquivo que foi gerado na pasta C:\Auditoria.

9

 

15) Vamos visualizar os dados auditados utilizando a função fn_get_audit_file, que é responsável em retornar as informações de um arquivo de auditoria.

10

/* RETORNA INFORMAÇÕES DE UM ARQUIVO DE AUDITORIA. SINTAXE:
fn_get_audit_file ( file_pattern, {default | initial_file_name | NULL }, {default | audit_file_offset | NULL } ) */

SELECT
 CONVERT(VARCHAR(10), SWITCHOFFSET(CONVERT(DATETIMEOFFSET, EVENT_TIME), DATENAME(TZOFFSET, SYSDATETIMEOFFSET())),103) AS [DATA], --DATA EM QUE A AÇÃO FOI ACIONADA
 CONVERT(VARCHAR(12), SWITCHOFFSET(CONVERT(DATETIMEOFFSET, EVENT_TIME), DATENAME(TZOFFSET, SYSDATETIMEOFFSET())),114) AS [HORA], --HORA EM QUE A AÇÃO FOI ACIONADA,
 CONVERT(DATETIME, SWITCHOFFSET(CONVERT(DATETIMEOFFSET, EVENT_TIME), DATENAME(TZOFFSET, SYSDATETIMEOFFSET()))) AS EVENT_TIME,
 SERVER_INSTANCE_NAME AS [NOME INSTÂNCIA], --NOME DA INSTÂNCIA DE SERVIDOR NO QUAL A AUDITORIA OCORREU
 DATABASE_NAME AS [NOME DO BANCO], --O CONTEXTO DO BANCO DE DADOS NO QUAL A AÇÃO ACONTECEU. PERMITE VALOR NULO. RETORNA NULL PARA AUDITORIAS REALIZADAS NO NÍVEL DE SERVIDOR.
 SERVER_PRINCIPAL_NAME AS [LOGIN], --LOGON ATUAL. PERMITE VALOR NULO
 OBJECT_NAME,
 STATEMENT AS [CONSULTA EXECUTADA],
 FILE_NAME AS [CAMINHO DO ARQUIVO]
FROM
 fn_get_audit_file('C:\Auditoria\TargetFileSSMS_D59592D1-4057-49EB-A421-CF2DF6BB2F47_0_129603920978160000.sqlaudit',DEFAULT,DEFAULT)

16) O resultado da consulta acima foi:

11

Perceba que foram retornados os dados do select, insert, update e delete. Uma parte essencial de qualquer estratégia de segurança de dados é um plano de segurança que possui regras com a capacidade de rastrear quem acessou ou tentou acessar dados, mas para ter essas informações precisamos realizar auditorias regulamente. Isso nos permite a capacidade de detectar prevenir qualquer ação maliciosa ou acessos ilegítimos.

Vimos no decorrer dos artigos como criar uma auditoria nos mais diversos seguimentos, vimos também que o SQL Server Audit, é uma ferramenta poderosa que pode ser utilizada para coletar quase todas as atividades que acontece dentro dos nossos servidores, banco de dados ou um objeto especifico.

Até a próxima pessoal!

Cibelle Castro. 🙂

Um comentário em “Descobrindo Auditoria no SQL SERVER 2008 – Parte 7”

  1. Pingback: Auditoria no SQL Server | Alex Souza

Deixe um comentário