- Auditoria no SQL Server (Server Audit)
- Como criar uma auditoria para monitorar a criação, modificação e exclusão de Jobs no SQL Server
- Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server
- SQL Server – Como implementar auditoria e controle de logins (Trigger de Logon)
- Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server
- Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable)
- SQL Server – Trigger de auditoria de permissões e privilégios a nível de database e instância (GRANT e REVOKE)
- SQL Server – Como monitorar e auditar alterações de dados em tabelas utilizando Change Data Capture (CDC)
- SQL Server 2016 – Como “viajar no tempo” utilizando o recurso Temporal Tables
- SQL Server – Como utilizar auditoria para mapear permissões necessárias reais em um usuário
- SQL Server – Trigger para prevenir e impedir alterações em tabelas
- SQL Server – Como criar um histórico de alterações de dados para suas tabelas (logs para auditoria)
- SQL Server – Como evitar ataques de força bruta no seu banco de dados
- SQL Server – Checklist de Segurança – Uma SP com mais de 70 itens de segurança para validar seu banco de dados
- SQL Server – Como saber a data do último login de um usuário
- SQL Server – Como evitar e se proteger de ataques de Ransomware, como WannaCry, no seu servidor de banco de dados
- SQL Server – Cuidado com a server role securityadmin! Utilizando elevação de privilégios para virar sysadmin
- SQL Server – Como evitar SQL Injection? Pare de utilizar Query Dinâmica como EXEC(@Query). Agora.
- SQL Server – Entendendo os riscos da propriedade TRUSTWORTHY habilitada em um database
- SQL Server – Políticas de Senhas, Expiração de Senha, Troca de Senha Obrigatória e Bloqueio de Login após N tentativas
- SQL Server – Como criar uma auditoria de logins utilizando os logs da instância
Olá Pessoal!
Boa noite!
Neste post vou demonstrar como criar auditorias de servidor (Server Audit) no SQL Server. Este recurso é muito interessante para auditar ações DDL e/ou DML (Database Audit Specification) realizadas por um usuário ou aplicação ou mesmo a própria instância (Server Audit Specification).
Criando uma auditoria via interface (GUI)
Abra o SQL Server Management Studio, selecione a categoria Security > Audits > Clique com o botão direito > Selecione a opção “New Audit…”
Quando você for definir a auditoria, os dados gravados poderão ser salvos de 3 maneiras:
- File: É gerado um arquivo físico no disco contendo os dados coletados pela auditoria
- Security Log: Os dados coletados pela auditoria ficam armazenados no log de segurança do servidor
- Application Log: Os dados coletados pela auditoria ficam armazenados no log de aplicação do servidor
Neste post, vou escolher gravar em arquivo físico no disco. Selecionando essa opção, você ainda pode especificar o nome do arquivo onde ele será salvo, o tamanho máximo e escolher se você já deseja pré-reservar esse espaço máximo no disco (ou seja, caso você escolha o tamanho máximo de 10 GB, o SQL Server já irá criar esse arquivo com os 10 GB de espaço alocados)
Após a criação da Auditoria, você deverá habilitá-la clicando com o botão direito sobre ela no Object Explorer e depois selecionando a opção “Enable Audit”
Criando uma auditoria a nível de servidor (Server Audit Specification)
Agora que o objeto de auditoria foi criado, podemos criar o objeto de definição de auditoria (Server Audit Specification).
Selecione a instância e selecione a categoria Security > Server Audit Specification > Clique com o botão direito > Selecione a opção “New Server Audit Specification”
Nesta tela, você poderá definir o que deseja monitorar, conforme o print abaixo:
Após escolher os tipos da auditoria (neste exemplo, quero auditar a criação/exclusão de databases), clique em OK e habilite a especificação de auditoria:
Criando uma auditoria a nível de database (Database Audit Specification)
Um recurso muito legal e que pode, em várias ocasiões, substituir o uso de triggers, é a criação de rotinas de auditorias a nível de database. Neste exemplo, vou criar uma auditoria para gravar alterações realizadas em uma tabela específica.
Para a criação de uma auditoria a nível de database, selecione a instância e depois o banco de dados que será auditado, expanda a categoria Security, selecione a sub-categoria Database Audit Specifications, clique com o botão direito e selecione a opção “New Database Audit Specification”
Nesta tela podemos definir o que queremos auditar nesse database.
Na configuração acima, defini para auditar as seguintes operações:
– DELETEs realizados no database Testes pelo usuário “Usuario_Teste”
– INSERTs realizados na tabela Clientes feitas por qualquer usuário.
– INSERTs em qualquer objeto do schema dbo, feitas por qualquer usuário.
Após realizar a configuração, lembre-se de ativar a auditoria clicando com o botão direito sobre ela e selecionando a opção “Enable Database Audit Specification” e sua auditoria está ativada!
Visualizando os dados coletados
Para visualizar os dados que foram coletados pela Auditoria, basta clicar com o botão direito sobre a Auditoria (não é a Specification) e selecionar a opção “View Audit Logs”
Criando uma auditoria via linha de comando
Após todo esse post explicando como criar auditorias de database e servidor utilizando as telas do SQL Server Management Studio, vou explicar rapidamente como criar essas mesmas auditorias via linha de comando, que é uma forma muito mais prática para replicar em vários servidores e fixa mais o conhecimento do DBA.
Com os comandos abaixo, já crio a auditoria (e ativo), a auditoria a nível de servidor e database (ambas já ativadas também).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
USE [master] GO CREATE SERVER AUDIT [Auditoria_Arquivo] TO FILE ( FILEPATH = N'C:\' ,MAXSIZE = 100 MB ,MAX_ROLLOVER_FILES = 2147483647 ,RESERVE_DISK_SPACE = OFF ) WITH ( QUEUE_DELAY = 1000 ,ON_FAILURE = CONTINUE ) GO ALTER SERVER AUDIT Auditoria_Arquivo WITH (STATE = ON) CREATE SERVER AUDIT SPECIFICATION [Criação de Banco de Dados] FOR SERVER AUDIT [Auditoria_Arquivo] ADD (DATABASE_CHANGE_GROUP) WITH (STATE = ON) GO USE [Testes] GO CREATE DATABASE AUDIT SPECIFICATION [Audita_DML] FOR SERVER AUDIT [Auditoria_Arquivo] ADD (DELETE ON DATABASE::[Testes] BY [Usuario_Teste]), ADD (INSERT ON SCHEMA::[dbo] BY [public]), ADD (INSERT ON OBJECT::[dbo].[Clientes] BY [public]) WITH (STATE = ON) GO |
Visualizando os logs
Utilizando queries simples, podemos facilmente ler os dados monitorados e trabalhar com essas informações:
|
1 2 3 4 5 6 7 |
-- Retorna as informações de um arquivo específico SELECT * FROM Sys.fn_get_audit_file('C:\Auditoria_Arquivo_75804C51-45C9-423C-B570-B24202AD13D3_0_130899437085570000.sqlaudit',default,default) -- Retorna as informações de todos os arquivos SELECT event_time,action_id,server_principal_name,statement,* FROM Sys.fn_get_audit_file('C:\*.sqlaudit',default,default) |
E os dados visualizados ficam assim:
Obrigado e até a próxima!
Portuguese 
English
Excelente artigo, ajudou muito!!
Ola, vendo os logs da auditoria (inserts, deletes, updates) só aparece as variáveis @ e não os conteúdos, como chegam no server. Algum parâmetro?
Sandro, o audit não captura o conteúdo mesmo não.. A ideia é só auditar o que foi feito e por quem.. Para criar um histórico de alterações, você deve implementar trigger de auditoria ou cdc (nesse mesmo artigo tem link para esses 2 que citei)
Uma pena Dirceu , porque saber o que foi alterado em termos de dados é basico em qq auditoria, mas valeu. Outra pergunta, tem como saber se foi o usuario do banco que a aplicacao usa ou uma pessoa com conhecimento deste usuario e senha que acessou uma maquina e rodou o comando via query analiser ?
É possível exibir o IP ou nome da máquina cliente que executou a operação no servidor?
Olá, bom dia. Não é possível com SQL Audit, mas você pode conseguir essa informação utilizando o SQL Profiler (Trace). Dá uma olhada nesse post que eu demonstro como utilizar esse recurso: http://www.dirceuresende.com/blog/sql-server-como-auditar-erros-de-permissao-em-objetos-utilizando-o-sql-profiler-trace/
Excelente Post Dirceu! Muito simples e prático. Segui o passo a passo e criei uma auditoria em poucos minutos. Apenas complementando o post, algumas edições do SQL Server (Bussiness Intelligence, Standard, Web e Express) não suportam a auditoria em nível de database, somente a nível de servidor. Segue abaixo um link como referência. Abraço
https://www.logbinder.com/Content/Blog.aspx?p=d1f5efd3-d0a8-4c3e-82a5-6c8fc7e7f672
Valeu, Luiz Vitor! Obrigado pela informação 🙂