SQL Server – Como auditar erros de permissão em objetos utilizando o SQL Profiler (Trace)

Olá pessoal,
Bom dia!

No post de hoje vou demonstrar como criar um trace no SQL Server utilizando o SQL Profiler para auditar acessos em objetos, seja por algum usuário específico, objeto ou database específico. Isso é especialmente útil para identificar quais usuários possuem acesso a um determinado objeto ou descobrir também quais usuários não possuem acesso a um objeto e mesmo assim, tentam acessá-lo, seja uma tabela, view, stored procedure, etc. Em várias empresas, isso é utilizado para auditar acessos a tabelas críticas e com informações sensíveis, como dados e salários de funcionários, etc.

Utilitário bem conhecido da maioria dos DBA’s SQL Server, mas nem tanto por parte dos desenvolvedores, o SQL Server Profiler tem muitas outras utilidades além de realizar auditorias, principalmente com o crescimento dos sistemas que utilizam ORM’s, como o Hibernate e Entity Framework, para encapsulamento das consultas a bancos de dados. Essas ferramentas são responsáveis por gerar as consultas e queries SQL para o desenvolvedor, que se preocupa apenas na codificação em sua linguagem de programação (Java, C#, etc) e não precisa escrever um linha de código SQL.

Geralmente essas queries não são muito performáticas e o desenvolvedor muitas vezes não sabe como a query foi montada pelo ORM. Quando começam o ocorrer problemas de performance no sistema, o SQL Server Profiler é uma ferramenta poderosa, possibilitando a identificação de qual comando exato o sistema está executando e permitindo que o DBA faça a análise de performance adequada, analise os índices, etc.

Como abrir o SQL Server Profiler pelo Management Studio

Inicie o SQL Server Management Studio e abra o SQL Server Profiler:

Como configurar os eventos e filtros do Trace no SQL Server Profiler

Uma vez aberto, configure a descrição do seu trace, onde os resultados serão salvos (eu prefiro sempre em tabela), e outras configurações que você ache pertinente:

Agora é o momento de selecionarmos o evento que iremos monitorar, que é o “Audit Schema Object Access Event” e selecionarmos as colunas que queremos que sejam gravadas na tabela do Trace:

Para ajustar os filtros de captura do trace, clique no botão “Column Filters” e será aberta uma janela onde será possível realizar os mais diversos tipos de condições para que essa sessão seja auditada. Vou listar os mais comuns:

• ApplicationName: Filtro que já vem ativado por padrão e com a cláusula NOT LIKE “SQL Server Profiler – <hash>” para que a própria sessão do Trace não seja auditada, permite realizar filtros pelo software utilizado para a conexão com o banco de dados, como “Microsoft SQL Server Management Studio – Query” ou “.Net SqlClient Data Provider”.
• DatabaseName: Permite filtrar os databases que podem ou não fazer parte do trace
• DBUsername: Permite filtrar quais usuários SQL Server que podem ou não fazer parte do trace
• HostName: Filtro que permite selecionar quais hostnames (nomes de máquinas) serão ou não auditados
• LoginName: Define se você quer aplicar um filtro no login da sessão (pode ser login do AD ou SQL Server) para que ele faça parte ou não do Trace
• ObjectName: Permite filtrar os objetos (tabelas, views, stored procedures, etc) que podem ou não fazer parte do trace
• SPID: Filtro que permite selecionar quais sessões serão auditadas ou não pelo Trace
• Success: Parâmetro essencial para este post, que define se a sessão conseguiu acessar o objeto (1) ou se não conseguiu devido a erro de permissão (0). Para atingir o objetivo desse post, você deve utilizar a condição Equals to 0

Vale lembrar que ao editar os filtros, caso você queira adicionar mais de um valor nas cláusulas do filtro, basta apertar a tecla “Enter” quando o cursor estiver posicionado na caixa de texto, para que assim, apareça mais caixas de texto abaixo:

Neste exemplo, apliquei o filtro “Exclude rows that do not contain values” nas colunas DatabaseName e DBUsername para que tentativas de acesso indireto, como o SQL Prompt e o Intellisense realizam, evitando que informações não tão interessantes sejam gravadas no log e o deixe muito “poluído”.

Caso você tenha iniciado o Trace e deixa editá-lo, basta parar o Trace utilizando o botão Stop (ou no menu File > Stop Trace) e selecionar a opção do menu “File” > “Properties…”

Agora clique no botão “Run” e o trace será iniciado na instância:

Visualizando os resultados do Trace no SQL Server Profiler

Vou agora utilizar um usuário sem permissão e tentar realizar uma consulta numa tabela que vai gerar um erro de falta de permissão:

Se voltarmos à janela do SQL Server Profiler, vamos ver que essa tentativa de acesso foi logada:

Ou podemos realizar consultas na tabela em que configuramos onde o Trace deve salvar os resultados:

Exportando para arquivo SQL as definições do Trace

O SQL Server Profiler ainda nos dá a opção de exportar as definições e filtros realizados como um arquivo .sql, para que você possa ativar novamente esse trace de forma mais prática e rápida, podendo até automatizar isso em um Job, por exemplo:

E esse será o resultado da exportação do Trace como script SQL:

Vale lembrar que quando você exporta o trace para .sql, exportação dos dados para tabela do banco de dados NÃO é suportada.

Consultando o resultado do Trace ativado por script SQL

Conforme explicado acima, utilizando o script podemos ativar facilmente o trace, mas não é possível exportar os dados para uma tabela, apenas para arquivos Trace (.trc). Felizmente, isso não é um problema, pois podemos facilmente realizar consultas no arquivo de trace utilizando a função fn_trace_gettable (Saiba mais acessando os posts Using the standard SQL Server trace to audit events (fn_trace_gettable) e Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable).

Primeiramente, abrimos o arquivo SQL gerado no SQL Server Management Studio e alteramos o nome do arquivo de Trace “InsertFileNameHere” para um caminho acessível pelo seu servidor, conforme fiz no exemplo abaixo:

Depois, vamos tentar consultar a tabela e visualizar novamente a mensagem de acesso negado. Agora que a mensagem foi logada no nosso arquivo de trace, realizamos a consulta:

Identificando e parando um evento de trace

Caso você opte por iniciar os eventos de trace utilizando o script SQL, será muito importante conseguir identificar se o trace está ativo e saber como pará-lo. Para fazer isso, basta executar a query abaixo

Uma vez que recuperamos o ID do nosso trace, vamos agora pará-lo e verificar se ele realmente não está mais ativo:

And that's it, folks!
A ideia era criar um post específico sobre auditoria de acesso a objetos a acabou virando um mini-tutorial do SQL Server Profiler.
Espero que tenham gostado do post e até a próxima.