Clique no banner para conhecer e adquirir o meu treinamento de Bancos de Dados no Azure

Auditing in SQL Server (Server Audit)

Post Views 16,098 views
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…”

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

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!