Como identificar o usuário através de uma conexão genérica?
Uma questão que aflige muitos desenvolvedores e DBAs, é reconhecer qual o usuário está executando uma determinada operação no banco de dados, quando se utiliza uma conexão genérica com o SQL Server (integrada ou não). Você também já parou para pensar nesse problema? Como resolveu?
A utilização de uma conexão genérica é muito comum em aplicações ASP.NET ou serviços do Windows que ficam recebendo chamadas e executando procedimentos específicos. O que usualmente fazemos? Configuramos a aplicação para utilizar a identidade do processo em questão para acessar o SQL Server (segurança integrada e uma boa abordagem) ou então o pessoal cria um usuário UsrAplicacao e atribui as permissões adequadas… Tá bom, eu sei que vocês têm aquela(ssssssss) aplicação que usa o SA, mas que tal evitar?
Para evitar esse problema, poderíamos configurar nosso processo para personificar a identidade do usuário que acessa a aplicação, atribuindo as permissões adequadas para cada usuário (ou grupo). Até aqui tudo bem, mas o pessoal acaba não aplicando as permissões corretas ou começam a enfrentar problemas como o double-hop do kerberos ou muitos pools de conexão criados (um por usuário). E por fim, voltam a adotar uma conexão genérica.
Tenho uma conexão genérica, o que eu posso fazer? Normalmente as opções são…
1 – Não implementar nenhuma forma de registro (essa é fácil!)
2 – Utilizar a sua biblioteca de logging e, antes ou depois de cada operação no banco de dados, registrar em uma tabela de log o que foi feito e por quem.
3 – Se você somente utiliza procedimentos armazenados, pode colocar mais um parâmetro em todas as operações e registrar isso em uma tabela de log.
Eu já implementei as três opções em diferentes sistemas que eu trabalhei (sim, as três ;-p), mas sempre encontrei alguma dor de cabeça, pois ficava muito dependente do desenvolvedor ou dava um trabalhão. Então vamos analisar mais uma alternativa que apareceu…
CONTEXT_INFO
Estava preparando um webcast e passei pelo CONTEXT_INFO, de onde tirei a idéia para esse post. O interessante foi que quando pesquisei sobre o assunto, encontrei algumas pessoas que já usavam a abordagem que eu tinha em mente, mas infelizmente não acho que seja algo muito difundido. Veja como funciona e se te agrada…
No SQL Server é possível configurar para uma sessão aberta a informação de contexto, contendo 128 bytes com alguma informação específica. Se você abrir uma conexão com o Management Studio e executar o seguinte script, vai receber NULL como resposta.
SELECT CONTEXT_INFO()
SELECT CAST(CONTEXT_INFO() AS VARCHAR)
Isso mostra que por padrão a informação de contexto para uma sessão vem vazia. Agora, se você executar o script abaixo, o resultado será diferente.
DECLARE @binario BINARY(128)
SET @binario = CAST('Luciano Moreira' AS BINARY(128))
SET CONTEXT_INFO @binario
SELECT CONTEXT_INFO()
SELECT CAST(CONTEXT_INFO() AS VARCHAR)
Aqui veremos que o contexto da sessão atual foi alterado e está registrando o meu nome… hhhuuummmm, sacou né? O interessante é que você não consegue limpar o contexto atual através do SET CONTEXT_INFO NULL, apenas consegue o definir como 0x0, o que é totalmente diferente de NULL (faça o teste e execute novamente os dois selects acima). A única maneira de ter o contexto zerado é abrindo uma nova conexão.
Uma abordagem usando CONTEXT_INFO
Com o que você já sabe até o momento, pode começar a implementar sua solução, que consiste em definir um contexto específico após aberta a conexão com o SQL Server.
Partindo do pressuposto que você configurou sua aplicação ASP.NET com autenticação Windows ( <authentication mode="Windows"/> ), pode utilizar o WindowsIdentity.GetCurrent().Name para identificar quem é o usuário e, antes de executar o comando SQL, definir o contexto:
string comandoContexto = "DECLARE @binario BINARY(128); ";
comandoContexto += "SET @binario = CAST('" + WindowsIdentity.GetCurrent().Name + "' AS BINARY(128)); ";
comandoContexto += "SET CONTEXT_INFO @binario; ";
SqlConnection conexao = new SqlConnection(strConexao);
SqlCommand comando = new SqlCommand(comandoContexto, conexao);
conexao.Open();
comando.ExecuteNonQuery();
comando.CommandText = "INSERT INTO TESTE VALUES ('ZZZZZZZZZZZZ')";
comando.ExecuteNonQuery();
conexao.Close();
No banco de dados, você poderia utilizar uma trigger para registrar quem está fazendo a operação:
CREATE TRIGGER trgI_Teste
ON Teste
FOR INSERT
AS
BEGIN
DECLARE @Usr VARCHAR(200)
SELECT @Usr = CAST(CONTEXT_INFO() AS VARCHAR)
INSERT INTO Audit VALUES ('Insert na tabela teste', @Usr)
END
É claro que essa abordagem ainda dá muito trabalho, pois você teria que colocar isso em toda chamada que fizesse, mas você pode melhorar isso. Como o seu projeto possui uma camada de persistência com funções auxiliares para gerenciar a conexão/comandos, minha sugestão é que você coloque a definição do contexto em um único lugar, de forma transparente para o programador que já está acostumado a usar sua biblioteca. Por exemplo…
Supondo que em sua empresa o padrão de trabalho seja algo do tipo…
using (SqlConnection conexao = AcessoDados.NovaConexao())
{
…
AcessoDados.ExecutarComando(comando, conexao);
}
Aqui você pode incluir o código que define o contexto da conexão no método NovaConexao, mas temos o problema de deixá-la aberta, ou antes de executar o comando no método ExecutarComando. Dessa forma, supondo que sua aplicação atual não faça nenhum controle do usuário e tenha uma biblioteca de acesso a dados, você poderia incluir de forma transparente a informação de contexto, sem gerar retrabalho de codificação.
Sobre a criação de triggers, sei que isso pode gerar um impacto de desempenho se não for bem utilizada, mas coloca o controle de registrar as ações na mão do DBA, não do desenvolvedor, que normalmente vê essa história de audit somente como um trabalho extra, que vai levar mais tempo para codificar. Já vi projetos onde o logging manual somente funciona no início do projeto e, quando o prazo aperta, ninguém se lembra disso.
Refletindo sobre a solução
Ainda não considero essa abordagem uma solução ideal, mas uma vez o contexto definido de forma transparente (em um lugar centralizado, sem impactar centenas de linhas de código), podemos usar a informação dentro de procedimentos ou triggers, sem necessitar passarmos essa informação através de parâmetros ou outros meios.
Outro ponto bacana é que, sendo um campo binário, eu posso passar qualquer coisa no contexto, seja a identidade de um usuário Windows, a identidade de uma autenticação Forms ou outra coisa qualquer, que pode me trazer uma aplicabilidade interessante no dia-a-dia.
Também gosto dessa abordagem por deixar o controle de logging mais focado nas mãos dos DBAs. Em contrapartida, não vejo necessidade de criarmos triggers em todas as tabelas do banco de dados, mas sim naquelas que são mais sensíveis e precisam de um acompanhamento mais detalhado.
Outro detalhe que precisamos levar em conta é o pool de conexões, já que tenho conexões que ficam abertas e são reutilizadas. Por padrão o ADO.NET executa um Connection Reset que, segundo a documentação, deixa o estado da conexão de acordo com um novo login, mas se você usar uma string de conexão com “Connection Reset = false” imagino que pode encontrar problemas. Obs: nos meus testes eu não consegui reproduzir um cenário aonde uma nova conexão vinha com o contexto preenchido (mesmo com reset = false), mas ainda acredito que eu não testei o bastante. :-)
Change Tracking e o contexto
Essa história toda começou quando eu estava brincando com o Change Tracking (CT) e notei que ao executar a função CHANGETABLE ela retornava uma coluna chamada SYS_CHANGE_CONTEXT, do tipo VARBINARY(128). Sugestivo? MUITO!
Particularmente, eu esperava que ao registrar uma alteração na tabela de change tracking, o SQL Server automaticamente pegasse o contexto. Isso seria muito melhor do que usar triggers ou SPs, pois além de não necessitar codificá-las, o CT “injeta” os comandos que registram as alterações de forma transparente, o que me parece mais eficiente e menos problemático.
PORÉM, não é assim que funciona (que droga!). A documentação mostra que precisamos executar nosso comando de INSERT, DELETE ou UPDATE com a cláusula WITH, conforme exemplo abaixo:
DECLARE @originator_id varbinary(128);
SET @originator_id = CAST('MyApplicationID' AS varbinary(128));
WITH CHANGE_TRACKING_CONTEXT (@originator_id)
UPDATE Employees
SET Salary = 50000
WHERE EmpID = 1
Trabalhando dessa forma eu perco a generalização criada na minha camada de acesso a dados (que automaticamente definia o contexto), além de exigir do desenvolvedor que ele saiba quais são as tabelas com change tracking para adicionar o WITH.
Minha expectativa era de habilitar o tracking do contexto automaticamente e, caso ele seja diferente de NULL, o código injetado pelo CT armazenaria o conteúdo na tabela de controle das alterações. No exemplo do BOL, falou o trecho em negrito…
ALTER DATABASE AdventureWorks
SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 2 DAYS, AUTO_CLEANUP = ON, TRACK_CONTEXT = ON)
Conclusão
Acho que o CONTEXT_INFO pode nos ajudar em diversos cenários e, mesmo com alguns ajustes e controles manuais, ainda parece uma alternativa interessante para aplicações que utilizam conexões genéricas.
Ainda pretendo refinar um pouco a aplicabilidade do contexto, ver se existe alguma saída automática com o change tracking, alguns blockers e, quem sabe, até ver como isso ficaria com o LINQtoSQL e Entity Framework.
Gostou? Odiou? Sabe como melhorar? Entre em contato.
[]s
Luciano Caixeta Moreira
luciano.moreira@microsoft.com
===============================================
This post is provided "AS IS" and confers no right
===============================================
Comments
Anonymous
February 12, 2009
PingBack from http://www.clickandsolve.com/?p=7076Anonymous
February 12, 2009
Olá Luti, Legal essa estrutura CONTEXT_INFO. Com ela podemos definir qualquer estrutura de dados (até 128 bytes) e vincularmos a conexão. Uma outra forma de identificar a aplicação que está conectada utilizando um usuário genérico é definir na connection string da aplicação o parâmetro Application Name com o nome da aplicação. Posteriormente essa informação pode ser utilizada em uma sessão de profiling e também poder ser acessada através da DMVs. Valeu pela dica Luti! -GeorgeAnonymous
February 12, 2009
Oi George, excelente feedback! Eu havia esquecido de listar essa possibilidade. Contudo existe um probleminha aqui, se você ficar abrindo a conexão cada hora com um application name diferente, serão criados (por padrão) diversos pools de conexão, então você vai acabar não se beneficiando tanto desse recurso. Uma maneira interessante que eu vejo para se utilizar o Application Name, seria mudá-lo de acordo do contexto da aplicação (moduloImportante, reporting, moduloBasico, etc.) e usar o Resource Governor para gerenciar os recursos disponíveis. Gravei um webcast sobre o assunto, se interessar... []s LutiAnonymous
February 12, 2009
The comment has been removed