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:
sql-server-sql-server-profile-trace

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-2

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 – ” 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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-3

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-5

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-4

Se voltarmos à janela do SQL Server Profiler, vamos ver que essa tentativa de acesso foi logada:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-6

Ou podemos realizar consultas na tabela em que configuramos onde o Trace deve salvar os resultados:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-7

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:

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-8

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-9

E esse será o resultado da exportação do Trace como script SQL:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-10

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 Utilizando o trace padrão do SQL Server para auditar eventos (fn_trace_gettable) e Monitorando operações de DDL e DCL utilizando a fn_trace_gettable do SQL Server).

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:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-11

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:

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-12

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

sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-13

Uma vez que recuperamos o ID do nosso trace, vamos agora pará-lo e verificar se ele realmente não está mais ativo:
sql-server-sql-server-profile-trace-audit-monitor-access-denied-in-objects-tables-views-stored-procedures-functions-14

E é isso aí, pessoal!
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.

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

Deixe uma resposta