Visualizações: 16.988 views
Tempo de Leitura: 4 minutosOlá 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).
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:
|
|
-- 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 🙂