Detectar se houve alteração no registro do banco de dados indevidamente

21/05/2015

Boa tarde,

Utilizo o banco de dados SQL Server Express 2005 e estou com a necessidade de detectar se um registro do banco de dados foi manipulado sem passar pelo meu sistema PDV.

Exemplo:
Ao emitir um Cupom Fiscal meu sistema alimenta várias tabelas como, VENDA, PRODUTO, CLIENTE, etc...

Se alguém utilizar alguma ferramenta para manipular os dados/registro do Cupom Fiscal que não seja pelo meu sistema eu tenho que apontar o registro que foi alterado indevidamente.

Como fazer isso via banco de dados?

OBS: Essa é uma exigência pra quem tem a necessidade de homologar seu sistema PAF-ECF.

Carlos Eduardo

Melhor resposta

21/05/2015

Crie um trigger na operação de update da tabela, identificando a data, a hora, o tipo de operação, o usuário e as colunas alteradas.

Qualquer alteração, independentemente da origem, passará pela trigger e será logada.

Trabalhando com as permissões certas, inclusive com o bloqueio de deleção / desabilitação da trigger, é uma solução bastante eficiente !

Marcos P

Responder Citar

Outras Respostas

25/05/2015

Carlos Eduardo

Crie um trigger na operação de update da tabela, identificando a data, a hora, o tipo de operação, o usuário e as colunas alteradas. Qualquer alteração, independentemente da origem, passará pela trigger e será logada. Trabalhando com as permissões certas, inclusive com o bloqueio de deleção / desabilitação da trigger, é uma solução bastante eficiente !


Bom dia Marcos,

Desculpe a demora de lhe responder é por que estou envolvido com vários projetos ao mesmo tempo.

Gostei da ideia da Trigger mas estou com algumas dúvidas para dar inicio a este processo:

1) Inserindo esta Trigger no update das tabelas tanto meu sistema quanto qualquer outro mecanismo que dar um update na tabela ira executar esta Trigger. Minha duvida é como identificar que o registro que foi alterado não passou pelo meu sistema? Sua ideia foi armazenar estes dados alterados em uma outra tabela?

2) Não entendi o que você quis dizer com "Trabalhando com as permissões certas..."

Se você tiver um exemplo dessa Trigger para que eu possa melhor visualizar sua ideia ficarei muito grato!
Responder Citar

25/05/2015

Marcos P

Carlos,

Existem vários modelos de log baseado em trigger, mas todos tem forte relação com o modelo de autenticação no banco de dados e na aplicação !

Como sua aplicação conecta no banco de dados, através de um único usuário ou usuários específicos ?

Quais acessos no nível de administração existem no seu ambiente ? Você tem vários usuários com esse perfil ou apenas um ( root ) ?

Qual o modelo de login na aplicação e como esses logins se relacionam com os usuários no banco ?

Respondendo essas perguntas, fica mais fácil de desenhar algo específico para sua necessidade.

Segue um modelo de trigger de update, com suporte ao log de alterações :

CREATE TRIGGER trCableModemUpdate ON CableModem                                                                                         
    FOR UPDATE                                                                                                                      
    AS
      DECLARE @LogUsuario       char(5)                                                                                                      
      DECLARE @LogData       datetime
      DECLARE @LogResp       char(5)
      DECLARE @LogOper       char(1)
      DECLARE @CodCM         int
      DECLARE @CodCliente    int
      DECLARE @CodContrato   int
      DECLARE @CodTipoPS     int
      DECLARE @EndMac        varchar(17)
      DECLARE @CodTipoCM     int
      DECLARE @EstadoCM      bit
      DECLARE @LocalInstalac char(25)
      DECLARE @ObsCM         varchar(250)
      DECLARE @CodPropCM     int

      -- Seleciona o usuário referente à conexão.              
      SET @LogUsuario = (SELECT Logon  FROM Usuarios WHERE spid = @@SPID)      
                                                                                                                                    
      -- Edição externa a aplicação
      IF @LogUsuario IS NULL  
        SET @LogUsuario = SUSER_SNAME()                                                                                                 
               
      SELECT @CodCM = CodCM from inserted                                                                                                 
      SELECT @CodCliente = CodCliente from inserted                                                                                     
      SELECT @CodContrato = CodContrato from inserted                                                                                     
      SELECT @EstadoCM = EstadoCM FROM inserted                                                                               

      IF UPDATE(CodTipoPS)
        SELECT @CodTipoPS = CodTipoPS from inserted                 
      IF UPDATE(CodTipoCM)                                                                                                             
        SELECT @CodTipoCM = CodTipoCM FROM inserted                                                                                       
      IF UPDATE(EndMac)                                                                                                             
        SELECT @EndMac = EndMac FROM inserted                                                                                       
      IF UPDATE(LocalInstalac)                                                                                                        
        SELECT @LocalInstalac = LocalInstalac FROM inserted                                                                             
      IF UPDATE(ObsCM)                                                                                                            
        SELECT @ObsCM = ObsCM FROM inserted                                                                                     
      IF UPDATE(CodPropCM)                                                                                                           
        SELECT @CodPropCM = CodPropCM FROM inserted                                                          
                                                                                                                                    
      INSERT INTO LogCableModem                                                                                                        
        VALUES(GETDATE() ,@LogUsuario,'U',@CodCM,@CodCliente,@CodContrato,@CodTipoPS,@EndMac,
               @CodTipoCM,@EstadoCM,@LocalInstalac,@RespInc,@DataHoraInc,@ObsCM,
               @CodPropCM )


Nesse ambiente existe apenas um usuário de conexão da aplicação no sql server, múltiplos usuários de administração no servidor e múltiplos usuários específicos na aplicação.

Segue uma rápida explicação :

> LogUsuario, LogData, LogResp e LogOper: são as colunas de controle da operação que está sendo alterado.
> LogOper é o tipo de operação logado ( I - Insert; U - Update; D - Delete )
> As demais colunas, são as colunas de dados com os valores alterados na tabela de origem e que serão inseridas na tabela de log

Para identificar o usuário ( no banco de dados ) relacionado à operação, temos o código :

      -- Seleciona o usuário referente à conexão.              
      SET @LogUsuario = (SELECT Logon  FROM Usuarios WHERE spid = @@SPID)      
                                                                                                                                    
      -- Edição externa a aplicação
      IF @LogUsuario IS NULL  
        SET @LogUsuario = SUSER_SNAME()   


O "select" decorre da arquitetura de autenticação da aplicação, onde, no momento do login no sistema, registra-se o processo de conexão ao banco ( spid ) na tabela de usuários. Uma vez registrado o "spid" daquela conexão, fica fácil identificar o usuário, do lado do sistema, responsável por aquele ajuste na tabela.

Senão existir usuário associado à aplicação, vale o usuário usado para conexão direta no banco ( SUSER_SNAME ).

Isso responde sua primeira pergunta !

A segunda é simples de entender : quem está autorizado, no banco de dados, a excluir ou desabilitar as triggeres ?

Isso precisa ser revisto, pois não faz sentido algum você implementar uma arquitetura dessas e permitir que qualquer usuário baixe a trigger que trata desses logs !

Esse assunto tem várias nuances e se você precisar de ajuda, podemos conversar por skype / gtalk.
Responder Citar