Array
(
    [0] => stdClass Object
        (
            [Votos_Balanceados] => 1
            [id] => 520520
            [titulo] => Detectar se houve alteração no registro do banco de dados indevidamente
            [dataCadastro] => DateTime Object
                (
                    [date] => 2015-05-21 18:56:41
                    [timezone_type] => 3
                    [timezone] => America/Sao_Paulo
                )

            [isFirstPost] => -1
            [idUsuario] => 378439
            [status] => A
            [isExample] => 
            [NomeUsuario] => Marcos P
            [Apelido] => 
            [Foto] => 378439_20140801115452.png
            [Conteudo] => 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 ! ) )

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

Carlos Eduardo
   - 21 mai 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.

Post mais votado

Marcos P
   - 21 mai 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 !

Carlos Eduardo
   - 25 mai 2015


Citação:
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!

Marcos P
   - 25 mai 2015

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 :

#Código

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 :

#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.