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

Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable

Post Views 2,404 views
Esse post é a parte 5 de 21 da série Security and Auditing
Reading time 3 minutes

Olá pessoal,
Boa tarde!

Neste post vou demonstrar como identificar os eventos de DDL e DCL que ocorrem na instância SQL Server. Isso nos permite identificar qual o usuário que criou/alterou/apagou um objeto, ou quem adicionou/removeu um usuário em uma role de sistema ou role de database e várias outras coisas sem a necessidade de criar nenhum controle ou monitoramento interno, utilizando apenas o trace padrão do SQL Server, onde iremos consultar seus dados através da função fn_trace_gettable.

Há um tempo atrás eu publiquei aqui o post How to create an Audit trigger to log object manipulation in SQL Server que permitia criar uma server trigger que realiza essa auditoria. Apesar da função fn_trace_gettable retornar boa parte das informações disponíveis nesse controle interno, essa trigger permite fazer o versionamento dos objetos alterados, como procedures, functions, tabelas, etc. Ou seja, caso você queira automatizar o versionamento de código-fonte de objetos no banco de dados, o post mais indicado é o citado logo acima.

Relembrando os conceitos de DDL e DCL

Acredito que esses conceitos já são bem conhecidos pela maioria de vocês, mas vou reforçar o que é cada coisa para não restarem dúvidas:

Operações DDL (Data Definition Language)
Sâo utilizados para definir ou alterar estruturas ou schemas de bancos de dados

CREATE – Utilizado para criar objetos no banco de dados
ALTER – Altera a estrutura de um objeto
DROP – Remove um objeto do banco de dados
TRUNCATE – Apapga todos os registros de uma tabela, incluindo o espaço alocado.
COMMENT – Adiciona comentários em objetos
RENAME – Renomeia um objeto

Operações DCL (Data Control Language)
Sâo utilizados para gerenciamento de permissões e privilégios de usuários em determinados objetos da instância SQL Server

GRANT – Concede acesso para um usuário em um determinado objeto
REVOKE – Remove o acesso de um usuário em um determinado objeto

Identificando os tipos de eventos do trace

Utilizando a query abaixo, podemos identificar rapidamente todos os EventClass que podemos utilizar na função ::fn_trace_gettable:

SQL Server - fn_trace_gettable EventClass

Identificando os eventos de DDL e DCL na instância SQL Server

Com a query abaixo, é possível identificar quem foi a última pessoa a alterar um determinado objeto na instância (Ex: Stored Procedure), quem adicionou um determinado usuário em uma role, quem apagou uma tabela, quem executou um comando de TRUNCATE, etc, e tudo isso sem precisar criar nenhum controle além dos já disponíveis e ativados por padrão no SQL Server.

SQL Server - fn_trace_gettable Audit DDL DCL operations

And that's it, folks!
Obrigado pela visita e até o próximo post.