Hello everybody!
How are you ?
In this post I would like to demonstrate how to implement access control and audit logs in using CLR (C #). This has proven to be very useful during auditing processes or when there is a problem due to misuse of the CLR, especially where the SQL Server service user is domain admin or has a high privilege level and can perform a number of actions and you end up not doing so. taking control of who you use and what each person is doing with the CLR, especially when you have record handling routines, files, etc.
In order to maintain control over the use of this tool, I will demonstrate in this post how you can implement access control of all CLR usage so that you can identify who performed each routine and even the parameters used in each call.
Creating the CLR Audit Logs
To create this control, first of all we need to create the table in the database that will keep the logs for auditing the use of SP's and CLR functions.
The structure I will use in this post is this:
|
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) |
Record method source codeLogExecution
Using the code below, you can generate log information and write to the database.
|
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; } } |
Using the auditing method in your CLR
Once you have created the static method writesLogExecution In a class of your SQL CLR project, you should use this method in all of your Stored Procedures and functions you want to audit.
I will demonstrate some examples.
Implementing audit logging in 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(); } }; |
Implementing the audit log in roles:
To implement the log write feature, we will need to include 2 special parameters in the declaration of the SqlFunction object, which is DataAccess and SystemDataAccess.
By default, functions in the CLR do not have access to perform database queries. To make this possible, we will need to set the read mode for these functions so that they can query and execute commands in the database, as I did in the example below.
|
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())); } }; |
As you may have noticed, in the method call writesLogExecution I need to inform the name of the parameter and its values in each call. This is especially laborious, especially if you have a lot of SP's and functions, but unfortunately, I couldn't find any alternative for that, not even using Reflection. Therefore, if your Stored Procedure has 10 parameters, you will need to inform them one by one, and their respective values in the method call. writesLogExecution in C #.
It is worth remembering that, unlike Transact-SQL functions, where it is not possible to execute commands and make any external changes to the function, such as performing a Procedure, changing table data, etc., in CLR functions it is possible to perform any kind of action, including file operations, data manipulation, etc., which makes them especially powerful and dangerous if misused.
Viewing audit logs
Now that I have implemented the audit log in all my CLR routines, let's run some objects and look at the generated execution log.
CLR Command Examples
|
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) |
Log Preview
|
1 |
SELECT * FROM dbo.Log_Execucao |
That's it folks!
I hope you enjoyed this tip.
I know you thought it was really cool how I manipulated the files very easily, intuitively and quickly right? If you liked it and want to know a little more about how to perform file operations using CLR, see more in the post. SQL Server - How to list, read, write, copy, delete and move files with CLR (C #).
Hugs!
