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

SQL Server – Implementando controle de acessos e logs de auditoria na utilização do CLR (C#)

Visualizações: 815 views
Tempo de Leitura: 4 minutos

Olá pessoal!
Como vocês estão ?

Neste post eu gostaria de demonstrar como implementar um controle de acessos e logs de auditoria na utilização do CLR (C#). Isso se demonstrou muito útil durante processos de auditoria ou quando ocorria algum problema por má utilização do CLR, principalmente onde o usuário do serviço do SQL Server é domain admin ou possui um nível de privilégio alto, podendo realizar uma série de ações e você acaba não tendo controle de quem utiliza e o que cada pessoa está fazendo com o CLR, especialmente quando você possui rotinas de manipulação de registro, arquivos, etc.

Visando manter um controle na utilização dessa ferramenta, vou demonstrar neste post, como vocês podem implementar um controle de acessos de toda utilização do CLR, de modo que você possa identificar quem executou cada rotina e até os parâmetros utilizados em cada chamada.

Criando os logs de auditoria do CLR

Para criar esse controle, antes de mais nada precisamos criar a tabela no banco de dados que irá guardar os logs para auditoria na utilização das SP’s e funções do CLR.

A estrutura que vou utilizar nesse post é essa:

Código-fonte do método gravaLogExecucao

Utilizando o código abaixo, você poderá gerar as informações do log e gravar na base de dados.

Utilizando o método de auditoria no seu CLR

Uma vez que você criou o método estático gravaLogExecucao em uma classe do seu projeto SQL CLR, você deverá utilizar esse método em todas as suas Stored Procedures e funções que você deseja auditar.

Vou demonstrar alguns exemplos.

Implementando o log de auditoria em Stored Procedures:

Implementando o log de auditoria em funções:
Para implementar o recurso de gravação de log em funções, precisaremos incluir 2 parâmetros especiais na declaração do objeto SqlFunction, que é o DataAccess e o SystemDataAccess.

Por padrão, as funções no CLR não tem acesso a realizar consultas ao banco de dados. Para que isso seja possível, precisaremos definir o modo de leitura para essas funções, de modo que elas possam realizar consultas e executar comandos no banco de dados, conforme fiz no exemplo abaixo.

Como vocês devem ter notado, na chamada do método gravaLogExecucao eu preciso informar o nome do parâmetro e seus valores em cada chamada. Isso é especialmente trabalhoso, principalmente se você tem muitas SP’s e funções, mas infelizmente, não encontrei outra alternativa para isso, nem utilizando Reflection. Sendo assim, se a sua Stored Procedure tiver 10 parâmetros, você precisará informá-los um a um, e seus respectivos valores na chamada do método gravaLogExecucao no C#.

Vale lembrar que, diferente de funções Transact-SQL, onde não é possível executar comandos e fazer qualquer alteração externa à função, como executar uma Procedure, alterar dados de tabelas, etc, nas funções do CLR é possível realizar qualquer tipo de ação, inclusive operações de arquivos, manipulação de dados, etc, o que as torna especialmente poderosas e perigosas, se utilizadas de forma errada.

Visualizando os logs de auditoria

Agora que já implementei o log de auditoria em todas as minhas rotinas do CLR, vamos executar alguns objetos e observar o log de execução gerado.

Exemplos de comandos do CLR

Visualização do log

É isso aí, pessoal!
Espero que tenham gostado dessa dica.

Sei que vocês acharam bem legal a forma como manipulei os arquivos de forma bem fácil, intuitiva e rápida né? Se vocês gostaram e querem saber um pouco mais sobre como realizar operações com arquivos utilizando o CLR, vejam mais no post SQL Server – Como listar, ler, escrever, copiar, excluir e mover arquivos com o CLR (C#).

Abraços!