- 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
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:
Visualizar todas os tipos de auditorias a nível de servidorApó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!
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 🙂