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),EVENT_TIME,103) AS [DATA], –DATA EM QUE A AÇÃO FOI ACIONADA
CONVERT(VARCHAR(12),EVENT_TIME,114) AS [HORA], –HORA EM QUE A AÇÃO FOI ACIONADA
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. 🙂

Anúncios

One thought on “Descobrindo Auditoria no SQL SERVER 2008 – Parte 7

Deixe um comentário

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