Log de auditoria com o Entity Framework 6

Uma das novidades no Entity Framework 6 é a possibilidade de interceptar os comandos SQL que são gerados pela ferramenta de forma bem mais simples que nas versões anteriores. Isso permite, entre outras coisas, efetuar um log de tudo o que é enviado para o banco de dados, facilitando a depuração das aplicações.

Mas hoje vou mostrar uma outra coisa bem interessante que podemos fazer ao interceptar o SQL do Entity Framework: log de auditoria. Ou seja, ter a rastreabilidade do que cada usuário fez no sistema, em determinado momento. A ideia central aqui é, para cada atualização de um registro no banco de dados (seja uma inserção, alteração ou exclusão), armazenar essa versão junto do usuário e data e hora de modificação.

Vamos olhar a tabela da imagem abaixo, People (à esquerda). Ela possui as colunas Id, FirstName, LastName e Age. Até aí, nada de mais. Agora vamos ver uma outra tabela de mesmo nome, só que criada em um schema diferente (hist). Ela possui as mesmas colunas, com os mesmos tipos, só que temos mais três colunas adicionais: Operation, para armazenar o tipo de operação que o registro recebeu (inserção, alteração e exclusão), Date, para registrar a data e hora da modificação, e User, para conter o login do usuário que efetuou a operação.

Para entender melhor, vamos dar uma olhada no exemplo de dados que essas tabelas possuem.


A tabela People do schema hist possui todas as versões de registros da tabela People. Com isso, podemos ver que no dia 20/11/2013, o usuário fabiogouw incluiu uma pessoa chamada "Celes Chere". Em 26/11/2013, o usuário matheussolino alterou a idade dessa pessoa para 17 anos. Então, o usuário fabiogouw excluiu essa pessoa em 01/12/2013. Apesar dessa pessoa não estar mais na tabela People, temos todo o rastreamento da sua existência no sistema. Com isso, podemos identificar o responsável por uma ação no sistema (imagine um sistema que calculou errado a folha de pagamentos de uma empresa; com o log de auditoria, podemos identificar quem foi o usuário responsável pelo erro).

Esse tipo de log não é nenhuma novidade. Há muito tempo essa estratégia é usada com o uso de triggers, por exemplo. Entretanto, iremos usar um comando novo nas últimas versões do SQL Server, o OUTPUT. Este comando direciona o resultado dos comandos de INSERT, UPDATE e DELETE (os registros afetados) de forma que podemos tanto enviá-los para a aplicação, como se fosse um comando SELECT, ou mesmo reutilizar esse retorno para inserir novamente no banco de dados. E é essa última forma que iremos utilizar neste exemplo.

Quando temos um comando de UPDATE, como abaixo, a ideia é inserir a cláusula de OUTPUT no meio do comando SQL.

Antes:
UPDATE [dbo].[People]

SET [Age] = @0

WHERE ([Id] = @1)


Depois:
UPDATE [dbo].[People]

SET [Age] = @0

 OUTPUT inserted.*, @histOperation, @histDate, @histUser into [hist].People

WHERE ([Id] = @1)


A cláusula OUTPUT redireciona o resultado dos registros atualizados (tabela virtual inserted) para dentro da tabela [hist].People. Justo desses registros também são passados os dados de auditoria, usuário, operação e data e hora, como parâmetros do comando.

A versão 6 do Entity Framework trouxe a classe DbInterception, que permite incluir classes que interceptam os comandos gerados por essa ferramenta. Essas classes devem implementar ou a interface IDbCommandInterceptor ou a interface IDbCommandTreeInterceptor. A primeira nos permite manipular o objeto DbCommand gerado pelo Entity Framework. A segunda permite manipular o objeto que armazena os dados do comando que será gerado (a árvore de expressões que será futuramente transformada em um comando SQL). Hoje iremos criar uma classe a partir de IDbCommandInterceptor, para que possamos alterar o objeto DbCommand, inserindo a cláusula OUTPUT e passando os parâmetros adicionais necessários.

using System;
using System.Data;
using System.Data.Common;
using System.Data.Entity.Infrastructure.Interception;
using System.Linq;
using System.Text.RegularExpressions;

namespace EF6AuditInterception
{
    public class AuditInterceptor : IDbCommandInterceptor
    {
        private delegate DbCommand ChangeCommandDelegate(DbCommand command, string tableName);

        private string[] _tables;

        public AuditInterceptor(params string[] tables)
        {
            _tables = tables;
        }

        public void NonQueryExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            //throw new NotImplementedException();
        }

        public void NonQueryExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
        {
            command = HandleAuditInjection(command);
        }

        public void ReaderExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
            //throw new NotImplementedException();
        }

        public void ReaderExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
        {
            command = HandleAuditInjection(command);
        }

        public void ScalarExecuted(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            //throw new NotImplementedException();
        }

        public void ScalarExecuting(System.Data.Common.DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
        {
            //throw new NotImplementedException();
        }

        private DbCommand HandleAuditInjection(System.Data.Common.DbCommand command)
        {
            string tableName = GetTableName(command);
            if (_tables.Contains(tableName))
            {
                var del = GetChangeCommandDelegate(command);
                command = del(command, tableName);
            }
            return command;
        }

        private ChangeCommandDelegate GetChangeCommandDelegate(DbCommand command)
        {
            string commandText = command.CommandText;
            if (commandText.StartsWith("INSERT"))
                return ChangeInsertCommand;
            if (commandText.StartsWith("UPDATE"))
                return ChangeUpdateCommand;
            if (commandText.StartsWith("DELETE"))
                return ChangeDeleteCommand;
            return ChangeCommandWithDefault;
        }

        private DbCommand ChangeCommandWithDefault(DbCommand command, string tableName)
        {
            return command;
        }

        private DbCommand ChangeUpdateCommand(DbCommand command, string tableName)
        {
            int indexOfWhere = command.CommandText.IndexOf("WHERE");
            command = ReformatCommandText(command, indexOfWhere, "U", tableName);
            return command;
        }

        private DbCommand ChangeInsertCommand(DbCommand command, string tableName)
        {
            int indexOfWhere = command.CommandText.IndexOf("VALUES");
            command = ReformatCommandText(command, indexOfWhere, "I", tableName);
            return command;
        }

        private DbCommand ChangeDeleteCommand(DbCommand command, string tableName)
        {
            int indexOfWhere = command.CommandText.IndexOf("WHERE");
            command = ReformatCommandText(command, indexOfWhere, "D", tableName);
            return command;
        }

        private DbCommand ReformatCommandText(DbCommand command, int insertionIndex, string operation, string tableName)
        {
            string tableVersion = operation == "D" ? "deleted" : "inserted";
            command.CommandText = command.CommandText.Insert(insertionIndex,
                string.Format(" output {0}.*, @histOperation, @histDate, @histUser into [hist].{1} ", tableVersion, tableName));
            command = AddParameter(command, "@histOperation", DbType.String, operation);
            command = AddParameter(command, "@histDate", DbType.DateTime, DateTime.Now);
            command = AddParameter(command, "@histUser", DbType.String, Environment.UserName);
            return command;
        }

        private static Regex _tableNameRegex = new Regex(@"\[(.*?)\]");

        private string GetTableName(DbCommand command)
        {
            var matches = _tableNameRegex.Matches(command.CommandText);
            return matches.Count >= 2 ? matches[1].Groups[1].Value : string.Empty;
        }

        private DbCommand AddParameter(DbCommand command, string parameterName, DbType dbType, object value)
        {
            var param = command.CreateParameter();
            param.ParameterName = parameterName;
            param.DbType = dbType;
            param.Value = value;
            command.Parameters.Add(param);
            return command;
        }
    }
}


A interface IDbCommandInterceptor possui vários métodos onde podemos alterar o conteúdo do DbCommand, seja antes dele ser executado ou depois. Os métodos que terminam com Executing permitem interceptar o objeto DbCommand antes da sua execução, o que nos deixa alterá-lo. Já os métodos que terminam com Executed rodam após a execução do comando SQL. Com isso, podemos alterar o retorno que os componentes receberão, implementar cache, etc.

No código de exemplo, vejam os métodos NonQueryExecuting e ReaderExecuting. Eles são os pontos de alteração do comando SQL, feita pelo método HandleAuditInjection. Este último método primeiro identifica se a tabela que está sendo manipulada consta na lista de tabelas que devem ter log de auditoria (informação recebida no construtor da classe). Se constar, então identificamos qual comando que está sendo executado: se for um INSERT, UPDATE ou DELETE, encaminhamos o comando para o respectivo tratamento, onde identificamos em qual ponto do comando devemos incluir o comando OUTPUT. Se for INSERT ou UPDATE, então pegamos os registros da tabela virtual inserted. Se for DELETE, então pegamos da deleted. O ponto que inserimos o OUTPUT varia também: quando é UPDATE ou DELETE, colocamos antes do WHERE; no caso do INSERT, fica antes do VALUES. O horário da alteração é obtido simplesmente chamando DateTime.Now, e o usuário que faz a alteração através de Environment.UserName (para nosso exemplo, isso basta, mas em uma aplicação web, geralmente pegamos essa informação do token de autenticação).

Para configurar o Entity Framework para utilizar essa classe de interceptação, basta incluir uma instância da classe criada no objeto DbInterception.

using System.Data.Entity.Infrastructure.Interception;

namespace EF6AuditInterception
{
    class Program
    {
        static void Main(string[] args)
        {
            DbInterception.Add(new AuditInterceptor("People"));
            using (var context = new PersonDbContext())
            {
                var person = new Person() { FirstName = "Celes", LastName = "Chere" };
                context.People.Add(person);
                context.SaveChanges();
                person.Age = 17;
                context.SaveChanges();
                context.People.Remove(person);
                context.SaveChanges();
                var person2 = new Person() { FirstName = "Locke", LastName = "Cole" };
                context.People.Add(person2);
                context.SaveChanges();
            }
        }
    }
}


Uma coisa para se notar é que quando se configura uma classe para intercepção, ela atinge todas as operações que o Entity Framework faz a partir, pois ela é global. Pessoalmente eu acho isso ruim, deveria ser possível configurar por DbContext, mas isso pode ser uma funcionalidade futura. Ou como o Entity Framework tem código aberto, qualquer um pode sugerir essa alteração.

Com essa abordagem, conseguimos fazer a auditoria das alterações que ocorrem no sistema, mantendo a rastreabilidade do que ocorre.

[]'s

Comentários

  1. Olá, devo criar então duas Models em tabelas distintas? não entendi essa parte.

    ResponderExcluir
    Respostas
    1. Olá, Oliveira. Não, no exemplo a tabela de histórico não é controlada diretamente pelo EF, ela é criada manualmente pelo desenvolvedor e todo o log é colocado nela através da alteração dos comandos SQL na classe AuditInterceptor.

      Excluir

Postar um comentário

Postagens mais visitadas deste blog

Trocando configurações padrão do Live TIM

Uma proposta de Clean Architecure com Modelo de Atores

Testes automatizados em sistemas autenticados com certificados digitais, usando Selenium e PhantomJS