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…”
SQL Server - Server Audit 2
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)
SQL Server - Create Audit
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”
SQL Server - Enable Audit
SQL Server - Enable Audit 2
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”
SQL Server - Server Audit
Nesta tela, você poderá definir o que deseja monitorar, conforme o print abaixo:
SQL Server - Server Audit Specification
Visualizar todas os tipos de auditorias a nível de servidor
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.
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.
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.
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.
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.
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.
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.
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 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.
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.
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:
SQL Server - Server Audit Specification Enable
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”
SQL Server - Database Audit
Nesta tela podemos definir o que queremos auditar nesse database.
SQL Server - Database New Audit
SQL Server - Database Audit Specification
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”
SQL Server - View Audit Logs
SQL Server - Database Audit Specification View Log
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
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)
E os dados visualizados ficam assim: SQL Server - View audit logs SQL Query
Obrigado e até a próxima!
Dirceu Resende
Arquiteto de Banco de Dados e BI · Microsoft MVP · MCSE, MCSA, MCT, MTA, MCP.
Comentários (0)
Carregando comentários…