Auditoria no SQL Server (Server Audit)

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…”

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

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

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

Visualizando os logs

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

E os dados visualizados ficam assim:
SQL Server - View audit logs SQL Query

Obrigado e até a próxima!

SQL, sql server, sql server 2008, sql server 2008 R2, Oracle, Oracle Database, Oracle 11g, Oracle 10g, Oracle 12c, MySQL, Firebird, Consultoria, Consultor, Programador, Programação. Desenvolvedor, Analista de Sistemas, DBA, Criação de website, Criação de Sistema Web, Vitória, Vila Velha, Guarapari, Espírito Santo, ES, Consultoria SQL em VItória, Treinamento, Curso, Prestação de serviço, prestar serviço, freelancer, freela, banco de dados, consultoria em banco de dados, consultor de banco de dados

4 comentários em “Auditoria no SQL Server (Server Audit)

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

Deixe uma resposta