Auditoria no SQL Server (Server Audit)

Visualizações: 14.529 views

Neste artigo

Esse post é a parte 1 de 21 da série Segurança e Auditoria

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

Tempo de Leitura: 4 minutos

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 servidor

Nome do grupo de ação	Descrição
APPLICATION_ROLE_CHANGE_PASSWORD_GROUP	Esse evento é gerado sempre que uma senha é alterada por uma função de aplicativo. Equivalente à Classe de evento Audit App Role Change Password.
AUDIT_CHANGE_GROUP	Esse evento é gerado sempre que uma auditoria é criada, modificada ou excluída. Esse evento é gerado sempre que qualquer especificação de auditoria é criada, modificada ou excluída. Qualquer alteração em uma auditoria é auditada nessa auditoria. Equivalente à Classe de evento Audit Change Audit.
BACKUP_RESTORE_GROUP	Esse evento é gerado sempre que um comando de backup ou restauração é emitido. Equivalente à Classe de evento Audit Backup/Restore.
BROKER_LOGIN_GROUP	Esse evento é gerado para relatar mensagens de auditoria relacionadas à segurança de transporte do Service Broker. Equivalente à Classe de evento Audit Broker Login.
DATABASE_CHANGE_GROUP	Esse evento é gerado quando um banco de dados é criado, alterado ou descartado. Esse evento é gerado sempre que um banco de dados é criado, alterado ou descartado. Equivalente à Classe de evento Audit Database Management.
DATABASE_MIRRORING_LOGIN_GROUP	Esse evento é gerado para relatar mensagens de auditoria relacionadas à segurança de transporte de espelhamento de banco de dados. Equivalente à Classe de evento Audit Database Mirroring Login.
DATABASE_OBJECT_ACCESS_GROUP	Esse evento é gerado sempre que objetos de banco de dados, como tipo de mensagem, assembly, contrato, são acessados. Ele é gerado para qualquer acesso a qualquer banco de dados. Observação Isso poderia gerar potencialmente grandes registros de auditoria. Equivalente à Classe de evento Audit Database Object Access.
DATABASE_OBJECT_CHANGE_GROUP	Esse evento é gerado quando uma instrução CREATE, ALTER ou DROP é executada em objetos de banco de dados, como esquemas. Ele é gerado sempre que um objeto de banco de dados é criado, alterado ou descartado. Observação Isso poderia gerar volumes muito grandes de registros de auditoria. Equivalente à Classe de evento Audit Database Object Management.
DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP	Esse evento é gerado quando há uma alteração de proprietário para objetos dentro do escopo do banco de dados. Ele é gerado para qualquer alteração de propriedade de objeto em qualquer banco de dados no servidor. Equivalente à Classe de evento Audit Database Object Take Ownership.
DATABASE_OBJECT_PERMISSION_CHANGE_GROUP	Esse evento é gerado quando GRANT, REVOKE ou DENY é emitido para objetos de banco de dados, como assemblies e esquemas. Ele é gerado para qualquer alteração de permissão de objeto em qualquer banco de dados no servidor. Equivalente à Classe de evento Audit Database Object GDR.
DATABASE_OPERATION_GROUP	Esse evento é gerado quando ocorrem operações em um banco de dados, como um ponto de verificação ou uma notificação de consulta de assinatura. Esse evento é gerado em qualquer operação de banco de dados em qualquer banco de dados. Equivalente à Classe de evento Audit Database Operation.
DATABASE_OWNERSHIP_CHANGE_GROUP	Esse evento é gerado quando a instrução ALTER AUTHORIZATION é usada para alterar o proprietário de um banco de dados e as permissões necessárias para fazer isso estão marcadas. Ele é gerado para qualquer alteração de propriedade de banco de dados em qualquer banco de dados no servidor. Equivalente à Classe de evento Audit Change Database Owner.
DATABASE_PERMISSION_CHANGE_GROUP	Esse evento é gerado sempre que GRANT, REVOKE ou DENY é emitido para uma permissão de instrução por qualquer entidade no SQL Server (Isso se aplica a eventos somente de banco de dados, como conceder permissões em um banco de dados, por exemplo.). Esse evento é gerado para qualquer GDR (alteração de permissão de banco de dados) de algum banco de dados no servidor. Equivalente à Classe de evento Audit Database Scope GDR.
DATABASE_PRINCIPAL_CHANGE_GROUP	Esse evento é gerado quando entidades, como usuários, são criadas, alteradas ou descartadas de um banco de dados. Equivalente à Classe de evento Audit Database Principal Management. (Também equivalente à classe de evento Audit Add DB Principal, que ocorre nos procedimentos armazenados sp_grantdbaccess, sp_revokedbaccess, sp_addPrincipal e sp_dropPrincipal substituídos.) Esse evento é gerado sempre que uma função de banco de dados é adicionada ou removida por meio dos procedimentos armazenados sp_addrole e sp_droprole. Esse evento é gerado sempre que qualquer entidade de banco de dados é criada, alterada ou descartada de qualquer banco de dados. Equivalente à Classe de evento Audit Add Role.
DATABASE_PRINCIPAL_IMPERSONATION_GROUP	Esse evento é gerado quando há uma operação de representação no escopo do banco de dados, como EXECUTE AS <entidade> ou SETPRINCIPAL. Ele é gerado para representações realizadas em qualquer banco de dados. Equivalente à Classe de evento Audit Database Principal Impersonation.
DATABASE_ROLE_MEMBER_CHANGE_GROUP	Esse evento é gerado sempre que um logon é adicionado ou removido de uma função de banco de dados. Esta classe de evento é gerada para os procedimentos armazenados sp_addrolemember, sp_changegroup e sp_droprolemember. Esse evento é gerado em qualquer alteração do membro da função Banco de dados em qualquer banco de dados. Equivalente à Classe de evento Audit Add Member to DB Role.
DBCC_GROUP	Esse evento é gerado sempre que uma entidade emite algum comando DBCC. Equivalente à Classe de evento Audit DBCC.
FAILED_LOGIN_GROUP	Indica que uma entidade tentou efetuar logon no SQL Server e falhou. Os eventos nessa classe são gerados por novas conexões ou por conexões reutilizadas de um pool de conexões. Equivalente à Classe de evento Audit Login Failed.
FULLTEXT_GROUP	Indica que ocorreu um evento de texto completo. Equivalente à Classe de evento Audit Fulltext.
LOGIN_CHANGE_PASSWORD_GROUP	Esse evento é gerado sempre que a senha de um logon for alterada pela instrução ALTER LOGIN ou pelo procedimento armazenado sp_password. Equivalente à Classe de evento Audit Login Change Password.
LOGOUT_GROUP	Indica que uma entidade efetuou logoff no SQL Server. Os eventos nessa classe são gerados por novas conexões ou por conexões reutilizadas de um pool de conexões. Equivalente à Classe de evento Audit Logout.
SCHEMA_OBJECT_ACCESS_GROUP	Esse evento é gerado sempre que uma permissão de objeto é usada no esquema. Equivalente à Classe de evento Audit Schema Object Access.
SCHEMA_OBJECT_CHANGE_GROUP	Esse evento é gerado quando uma operação CREATE, ALTER ou DROP é executada em um esquema. Equivalente à Classe de evento Audit Schema Object Management. Esse evento é gerado em objetos de esquema. Equivalente à Classe de evento Audit Object Derived Permission. Esse evento é gerado sempre que algum esquema de qualquer banco de dados é alterado. Equivalente à Classe de evento Audit Statement Permission.
SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP	Esse evento é gerado quando as permissões para alterar o proprietário do objeto de esquema (como uma tabela, um procedimento ou uma função) são verificadas. Isso ocorre quando a instrução ALTER AUTHORIZATION é usada para atribuir um proprietário a um objeto. Esse evento é gerado para qualquer alteração de propriedade de esquema em qualquer banco de dados do servidor. Equivalente à Classe de evento Audit Schema Object Take Ownership.
SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP	Esse evento é gerado sempre que uma concessão, negação ou revogação é executada com relação a um objeto de esquema. Equivalente à Classe de evento Audit Schema Object GDR.
SERVER_OBJECT_CHANGE_GROUP	Esse evento é gerado para operações CREATE, ALTER ou DROP em objetos de servidor. Equivalente à Classe de evento Audit Server Object Management.
SERVER_OBJECT_OWNERSHIP_CHANGE_GROUP	Esse evento é gerado quando o proprietário é alterado para objetos no escopo do servidor. Equivalente à Classe de evento Audit Server Object Take Ownership.
SERVER_OBJECT_PERMISSION_CHANGE_GROUP	Esse evento é gerado quando GRANT, REVOKE ou DENY é emitido para uma permissão de objeto de servidor em qualquer entidade no SQL Server. Equivalente à Classe de evento Audit Server Object GDR.
SERVER_OPERATION_GROUP	Esse evento é gerado quando são usadas operações de auditoria de segurança, como alterar configurações, recursos, acesso externo ou autorização. Equivalente à Classe de evento Audit Server Operation.
SERVER_PERMISSION_CHANGE_GROUP	Esse evento é gerado quando GRANT, REVOKE ou DENY é emitido para permissões no escopo do servidor, como criar um logon. Equivalente à Classe de evento Audit Server Scope GDR.
SERVER_PRINCIPAL_CHANGE_GROUP	Esse evento é gerado quando as entidades de servidor são criadas, alteradas ou descartadas. Equivalente à Classe de evento Audit Server Principal Management. Esse evento é gerado quando uma entidade emite os procedimentos armazenados sp_defaultdb ou sp_defaultlanguage ou instruções ALTER LOGIN. Equivalente à Classe de evento Audit Addlogin. Esse evento é gerado nos procedimentos armazenados sp_addlogin e sp_droplogin. Também equivalente a Classe de evento Audit Login Change Property. Esse evento é gerado para os procedimentos armazenados sp_grantlogin, sp_revokelogin ou sp_denylogin. Equivalente à Classe de evento Audit Login GDR.
SERVER_PRINCIPAL_IMPERSONATION_GROUP	Esse evento é gerado quando há uma representação dentro do escopo do servidor, como EXECUTE AS <logon>. Equivalente à Classe de evento Audit Server Principal Impersonation.
SERVER_ROLE_MEMBER_CHANGE_GROUP	Esse evento é gerado sempre que um logon é adicionado ou removido de uma função de servidor fixa. Esse evento é gerado para os procedimentos armazenados sp_addsrvrolemember e sp_dropsrvrolemember. Equivalente à Classe de evento Audit Add Login to Server Role.
SERVER_STATE_CHANGE_GROUP	Esse evento é gerado quando o estado do serviço do SQL Server é modificado. Equivalente à Classe de evento Audit Server Starts and Stops.
SUCCESSFUL_LOGIN_GROUP	Indica que a entidade efetuou logon com êxito no SQL Server. Os eventos nessa classe são gerados por novas conexões ou por conexões reutilizadas de um pool de conexões. Equivalente à Classe de evento Audit Login.
TRACE_CHANGE_GROUP	Esse evento é gerado para todas as instruções que verificam a permissão ALTER TRACE. Equivalente à Classe de evento Audit Server Alter Trace.

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).

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

USE [master]

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

)

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)

USE [Testes]

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)

Visualizando os logs

Utilizando queries simples, podemos facilmente ler os dados monitorados e trabalhar com essas informações:

-- 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)

-- 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!

sandro disse:

3 de dezembro de 2019 às 19:03

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?

Responder
- Dirceu Resende disse:
  
  3 de dezembro de 2019 às 19:43
  
  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)
  
  Responder
  - sandro servino disse:
    
    3 de janeiro de 2020 às 19:56
    
    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 ?
    
    Responder
. disse:

8 de novembro de 2016 às 10:03

É possível exibir o IP ou nome da máquina cliente que executou a operação no servidor?

Responder
- Dirceu Resende disse:
  
  9 de novembro de 2016 às 09:10
  
  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/
  
  Responder
Luiz Vitor França Lima disse:

11 de agosto de 2016 às 22:12

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

Responder
- Dirceu Resende disse:
  
  9 de novembro de 2016 às 09:11
  
  Valeu, Luiz Vitor! Obrigado pela informação 🙂
  
  Responder

Assinar blog por e-mail

Visualizações do Blog

Power Alerts

Curso Adm de Bancos no Azure

Curso Segurança de SQL Server

Minhas Certificações

Arquivo de Posts

Categorias

Posts recentes

Auditoria no SQL Server (Server Audit)