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
            [Login] => pe@duosoft.com.br
            [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.