Hey Guys!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE TABLE dbo.Log_Execucao ( Id_Log BIGINT IDENTITY(1, 1) NOT NULL, Dt_Log DATETIME DEFAULT GETDATE() NOT NULL, Ds_Procedure VARCHAR(200) NOT NULL, Ds_Parametros VARCHAR(MAX) NULL, Ds_Usuario VARCHAR(100) NULL, Ds_Hostname VARCHAR(100) NULL, Ds_Programa VARCHAR(500) NULL ) CREATE CLUSTERED INDEX CI_Log_Execucao ON dbo.Log_Execucao(Dt_Log) ALTER TABLE dbo.Log_Execucao ADD CONSTRAINT [PK_Log_Execucao] PRIMARY KEY NONCLUSTERED(Id_Log) |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
public static bool gravaLogExecucao(string parametros = "") { var stackTrace = new StackTrace(); // Evita gravar chamadas objetos dependentes (Ex: Evita gravar a chamada de uma função que é chamada por uma SP) if (stackTrace.FrameCount >= 4) return false; try { string nomeServidor; var dadosUsuarios = new string[4]; // Utiliza a conexão de contexto para recuperar os dados do usuário que está chamando as rotinas do CLR using (var conn = new SqlConnection("context connection=true")) { conn.Open(); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "SELECT @@SERVERNAME AS instancia, suser_name() AS usuario, host_name() AS maquina, APP_NAME() AS programa"; using (var dr = cmd.ExecuteReader()) { dr.Read(); nomeServidor = dr["instancia"].ToString(); dadosUsuarios.SetValue(dr["instancia"].ToString(), 0); dadosUsuarios.SetValue(dr["usuario"].ToString(), 1); dadosUsuarios.SetValue(dr["maquina"].ToString(), 2); dadosUsuarios.SetValue(dr["programa"].ToString(), 3); } } var partes = nomeServidor.Split('\\'); if (partes.Length > 1) { if (string.Equals(partes[0], partes[1], StringComparison.CurrentCultureIgnoreCase)) nomeServidor = partes[0]; } } // Recupera o objeto que foi utilizado utilizando técnicas de Reflection (stackTrace) var objeto = stackTrace.GetFrame(1).GetMethod().Name; var servidorAtual = nomeServidor; var stringConexao = "data source=" + servidorAtual + ";initial catalog=CLR;Application Name=SQLCLR;persist security info=False;Enlist=False;packet size=4096;user id='seu_usuario';password='sua_senha'"; // Agora utiliza o usuário do CLR, que vai ter permissões de escrita na tabela de log using (var conexao = new SqlConnection(stringConexao)) { conexao.Open(); using (var comando = new SqlCommand("INSERT INTO dbo.Log_Execucao (Ds_Procedure, Ds_Parametros, Ds_Usuario, Ds_Hostname, Ds_Programa) VALUES (@Ds_Procedure, @Ds_Parametros, @Ds_Usuario, @Ds_Hostname, @Ds_Programa)", conexao)) { comando.Parameters.Add(new SqlParameter("@Ds_Procedure", SqlDbType.VarChar, 200)).Value = objeto; comando.Parameters.Add(new SqlParameter("@Ds_Parametros", SqlDbType.VarChar, -1)).Value = parametros; comando.Parameters.Add(new SqlParameter("@Ds_Usuario", SqlDbType.VarChar, -1)).Value = dadosUsuarios[1]; comando.Parameters.Add(new SqlParameter("@Ds_Hostname", SqlDbType.VarChar, -1)).Value = dadosUsuarios[2]; comando.Parameters.Add(new SqlParameter("@Ds_Programa", SqlDbType.VarChar, -1)).Value = dadosUsuarios[3]; comando.ExecuteNonQuery(); return true; } } } catch (Exception e) { return false; } } |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
using System; using System.Data.SqlTypes; using System.IO; public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void stpApaga_Arquivo(SqlString caminho) { // Grava o log de auditoria Utils.gravaLogExecucao($"caminho: {caminho}"); var arquivo = new FileInfo(caminho.Value); if (arquivo.Exists) arquivo.Delete(); } }; |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
using System.Data.SqlTypes; using System.IO; using Microsoft.SqlServer.Server; public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, SystemDataAccess = SystemDataAccessKind.Read )] public static SqlBoolean fncArquivo_Existe(SqlString Ds_Arquivo) { // Grava o log de auditoria Utils.gravaLogExecucao($"Ds_Arquivo: {Ds_Arquivo}"); return (File.Exists(Ds_Arquivo.ToString())); } }; |
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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
EXEC CLR.dbo.stpCria_Diretorio @Ds_Diretorio = N'C:\Teste' -- nvarchar(max) EXEC CLR.dbo.stpCopia_Arquivo @origem = N'C:\Windows\System32\drivers\etc\hosts' , -- nvarchar(max) @destino = N'C:\Teste\hosts' , -- nvarchar(max) @sobrescrever = 1 -- bit EXEC CLR.dbo.stpRenomeia_Arquivo @Caminho_Origem = N'C:\Teste\hosts' , -- nvarchar(max) @Caminho_Destino = N'C:\Teste\hosts.txt' , -- nvarchar(max) @Fl_Sobrescrever = 1 -- bit EXEC CLR.dbo.stpApaga_Arquivo @caminho = N'C:\Teste\hosts.txt' -- nvarchar(max) |
Visualização do log
|
1 |
SELECT * FROM dbo.Log_Execucao |
And that's it, folks!
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!
Portuguese 
English