¡Hola, chicos!
¿Cómo estás?

En esta publicación me gustaría demostrar cómo implementar el control de acceso y los registros de auditoría utilizando CLR (C#). Esto resultó ser de gran utilidad durante procesos de auditoría o cuando ocurrió algún problema por mal uso del CLR, especialmente cuando el usuario del servicio SQL Server es administrador de dominio o tiene un nivel de privilegio alto, pudiendo realizar una serie de acciones y terminas sin tener control sobre quién lo usa y qué hace cada persona con el CLR, especialmente cuando se tienen rutinas de manipulación de registros, archivos, etc.

Para mantener el control sobre el uso de esta herramienta, demostraré en este post cómo puedes implementar un control de acceso para todo uso del CLR, de modo que puedas identificar quién ejecutó cada rutina e incluso los parámetros utilizados en cada llamada.

Crear los registros de auditoría de CLR

Para crear este control, primero que nada necesitamos crear la tabla en la base de datos que almacenará los registros para auditar el uso de las funciones SP y CLR.

La estructura que usaré en esta publicación es la siguiente:

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 fuente del método RecordingLogExecucao

Con el siguiente código, puede generar información de registro y guardarla en la base de datos.

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;
    }

}

Usando el método de auditoría en su CLR

Una vez que haya creado el método estático registro de ejecución de registro en una clase en su proyecto SQL CLR, debe usar este método en todos sus procedimientos almacenados y funciones que desee auditar.

Demostraré algunos ejemplos.

Implementación del registro de auditoría en procedimientos almacenados:

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();
        
    }

};

Implementación de funciones de registro de auditoría:
Para implementar la función de grabación de registros en funciones, necesitaremos incluir 2 parámetros especiales en la declaración del objeto SqlFunction, que son DataAccess y SystemDataAccess.

De forma predeterminada, las funciones en CLR no tienen acceso para realizar consultas a la base de datos. Para que esto sea posible, necesitaremos definir el modo de lectura de estas funciones, para que puedan realizar consultas y ejecutar comandos en la base de datos, como lo hice en el siguiente ejemplo.

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 habrás notado, al llamar al método registro de ejecución de registro Necesito informar el nombre del parámetro y sus valores en cada llamada. Esto es especialmente laborioso, especialmente si tienes muchos SP y funciones, pero desafortunadamente no encontré otra alternativa para esto, ni siquiera usando Reflection. Por lo tanto, si tu Procedimiento Almacenado tiene 10 parámetros, deberás ingresarlos uno por uno, y sus respectivos valores al llamar al método. registro de ejecución de registro Cª#.

Vale recordar que, a diferencia de las funciones Transact-SQL, donde no es posible ejecutar comandos y realizar cambios externos a la función, como ejecutar un Procedimiento, cambiar datos de una tabla, etc., en las funciones CLR es posible realizar cualquier tipo de acción, incluidas operaciones con archivos, manipulación de datos, etc., lo que las hace especialmente poderosas y peligrosas, si se usan incorrectamente.

Ver los registros de auditoría

Ahora que implementé el registro de auditoría en todas mis rutinas CLR, ejecutemos algunos objetos y observemos el registro de ejecución generado.

Ejemplos de comandos CLR

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)

Vista de registro

SELECT * FROM dbo.Log_Execucao

¡Eso es todo, amigos!
Espero que te haya gustado este consejo.

Sé que pensaste que fue genial la forma en que manipulé los archivos de una manera muy fácil, intuitiva y rápida, ¿verdad? Si te gustó y quieres saber un poco más sobre cómo realizar operaciones con archivos usando el CLR, mira más en el post SQL Server: cómo enumerar, leer, escribir, copiar, eliminar y mover archivos con CLR (C#).

¡Abrazos!