Auditoria no SQL Server (Server Audit)

Post Views 16,254 views

Neste artigo

Esse post é a parte 1 de 21 da série Security and Auditing

Reading time 4 minutes

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

Shaista De Oliveira Soares Coelho disse:

July 16, 2024 às 14:35

Excelente artigo, ajudou muito!!

Responder
sandro disse:

December 3, 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:
  
  December 3, 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:
    
    January 3, 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:

November 8, 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:
  
  November 9, 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:

August 11, 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:
  
  November 9, 2016 às 09:11
  
  Valeu, Luiz Vitor! Obrigado pela informação 🙂
  
  Responder

Subscribe to blog by email

Blog Views

Microsoft MVP

Post Archive

Categories

Recent posts

Auditing in SQL Server (Server Audit)