- Auditing in SQL Server (Server Audit)
- How to Create an Audit to Monitor Job Creation, Modification, and Deletion in SQL Server
- How to create an Audit trigger to log object manipulation in SQL Server
- SQL Server - How to implement login auditing and control (Logon Trigger)
- Monitoring DDL and DCL operations using SQL Server's fn_trace_gettable
- Using the standard SQL Server trace to audit events (fn_trace_gettable)
- SQL Server – Permissions and privileges audit trigger at database and instance level (GRANT and REVOKE)
- SQL Server - How to monitor and audit data changes in tables using Change Data Capture (CDC)
- SQL Server 2016 - How to "time travel" using the Temporal Tables feature
- SQL Server - How to use auditing to map actual required permissions on a user
- SQL Server - Trigger to prevent and prevent changes in tables
- SQL Server - How to Create a Data Change History for Your Tables (Audit Logs)
- SQL Server - How to avoid brute force attacks on your database
- SQL Server – Security Checklist – An SP with over 70 security items to validate your database
- SQL Server - How to know the last login date of a user
- SQL Server - How to avoid and protect yourself from Ransomware attacks like WannaCry on your database server
- SQL Server - Watch out for the securityadmin server role! Using elevation of privileges to become sysadmin
- SQL Server – How to avoid SQL Injection? Stop using Dynamic Query like EXEC(@Query). Now.
- SQL Server - Understanding the risks of the TRUSTWORTHY property enabled on a database
- SQL Server - Password Policies, Password Expiration, Mandatory Password Change and Login Blocking after several Attempts
- SQL Server - How to create a login audit using instance logs
Olá pessoal,
Bom dia!
No post de hoje, vou demonstrar como criar um histórico de alterações de dados, seja por INSERT, DELETE ou UPDATE em uma tabela, de forma que você consiga saber qual o usuário e quando uma informação foi alterada e qual era a informação da época. Isso é muito útil para auditoria ou relatórios de BI que precisam obter o cenário da época, como por exemplo, o salário do cliente na época em que ele adquiriu um produto, 5 anos atrás.
Para esse operação, vou criar uma tabela “clone” da tabela original, mas com uma data da atualização, a informação do usuário que realizou a alteração, hostname, operação realizada e vou criar uma trigger na tabela, para que a cada operação de DML grave um registro na tabela de histórico.
Criar o histórico utilizando Trigger ou pelo Sistema?
Antes de apresentar essa solução, gostaria antes de demonstrar alguns pontos de vista sobre a criação de triggers em tabelas para este fim. Por muito tempo, eu fui totalmente contra a criação de triggers em banco de dados, por achar que isso iria deixá-los menos performáticos, um update ou delete muito grande poderia gerar locks em produção, etc e achava que o desenvolvedor que deveria se preocupar, via código-fonte do sistema, com essa parte de logs e não criando triggers no banco de dados.
Isso mudou quando comecei a ver o lado do programador e pensar em rastreabilidade de informações, e o quanto isso pode ajudar outros setores de uma empresa, como Auditoria, BI e Controladoria. Hoje em dia, com a introdução da filosofia DevOPS, o DBA não pode pensar dentro da caixa preta, focando apenas em disponibilidade do banco de dados. É preciso focar no negócio e agregar o máximo de valor que a TI puder para a empresa, mas é claro, sempre na base do possível, não comprometendo a disponibilidade dos recursos de TI.
Diante disso, vou listar as diferenças entre criar esse histórico de dados utilizando trigger ou via sistema:
Gerando histórico através de trigger no banco de dados:
- Uma vez desenvolvida, a implantação do recurso envolve apenas a criação de uma tabela e uma trigger no banco de dados
- Não importa qual rotina ou usuário esteja manipulando a tabela, todas as alterações sempre serão gravadas
- UPDATE, INSERT e DELETE feitos manualmente no banco de dados serão logados e auditados pela trigger, e será gerado histórico para isso
- Tanto o DBA quanto o Desenvolvedor tem visibilidade sobre a existência da rotina e seu código-fonte
- Se for necessário desativar a trigger temporariamente para alguma operação, isso pode ser feito em poucos segundos pelo DBA
- O gerenciamento da rotina de auditoria fica nas mãos do DBA
- Caso a tabela sofra uma grande alteração de dados manual, seja via INSERT, DELETE ou UPDATE, todas as alterações serão gravadas na tabela de histórico, o que pode gerar um volume de gravações na tabela de histórico muito grande e causar lentidão no ambiente. Isso pode ser contornado desativando a trigger enquanto essas alterações em massa são realizadas e ativando novamente ao término
- Caso a alteração seja realizada pelo sistema, e o sistema utilize um usuário fixo, a trigger irá gravar o usuário do sistema, e não o usuário da pessoa que realizou a alteração
Gerando histórico através do sistema:
- A implementação envolve realizar alterações no código-fonte de todos os trechos de código da aplicação e telas que manipulam dados na tabela envolvida (além de arquivos dependentes), onde geralmente existem janelas rígidas para qualquer modificação em sistema
- Apenas as telas que foram alteradas para gravar histórico efetivamente o farão
- UPDATE, INSERT e DELETE feitos manualmente no banco de dados NÃO serão logados e não haverá histórico para essas alterações
- Apenas o desenvolvedor sabe que esse recurso existe e como ele funciona. O DBA geralmente não tem acesso a esse tipo de informação e muito menos, o código-fonte para entender como esse histórico está sendo gerado
- Se for necessário desativar esse recurso temporariamente, o desenvolvedor terá que alterar no código-fonte da aplicação e fazer o deploy em produção, consumindo bastante tempo de duas equipes e com possibilidade de desconectar sessões ativas no servidor de aplicação
- O gerenciamento da rotina de auditoria fica nas mãos do Desenvolvedor
- Caso a tabela sofra uma grande alteração de dados manual, seja via INSERT, DELETE ou UPDATE, o ambiente não será afetado, pois alterações manuais no banco não serão gravadas
- Caso a alteração seja realizada pelo sistema, é possível identificar o usuário logado na aplicação e gravar o login ou até mesmo realizar queries no banco e retornar um Id_Usuario da tabela Usuarios, por exemplo, para gravar na tabela de histórico
Como vocês observaram nos itens citados acima, existem vantagens e desvantagens em cada uma das abordagens. Sendo assim, você deverá decidir qual se encaixa melhor ao seu negócio e à sua infraestrutura.
Criando o processo de auditoria
Para os testes da trigger, vou criar uma tabela de clientes e inserir alguns dados para visualizarmos o log funcionando:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
IF (OBJECT_ID('Testes.dbo.Cliente') IS NOT NULL) DROP TABLE Testes.dbo.Cliente CREATE TABLE Testes.dbo.Cliente ( Id_Cliente INT IDENTITY(1, 1), Name VARCHAR(100), Data_Nascimento DATETIME, Salario FLOAT ) INSERT INTO Testes.dbo.Cliente VALUES ('João', '1981-05-14', 4521), ('Marcos', '1975-01-07', 1478.58), ('André', '1962-11-11', 7151.45), ('Simão', '1991-12-18', 2584.97), ('Pedro', '1986-11-20', 987.52), ('Paulo', '1974-08-04', 6259.14), ('José', '1979-09-01', 5272.13) SELECT * FROM Testes.dbo.Cliente |
Enfim, chegou a hora de criarmos nossa auditoria:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 |
-- Criando a tabela com a mesma estrutura da original, mas adicionando colunas de controle IF (OBJECT_ID('Testes.dbo.Cliente_Log') IS NOT NULL) DROP TABLE Testes.dbo.Cliente_Log CREATE TABLE Testes.dbo.Cliente_Log ( Id INT IDENTITY(1, 1), Dt_Atualizacao DATETIME DEFAULT GETDATE(), [Login] VARCHAR(100), Hostname VARCHAR(100), Operacao VARCHAR(20), -- Dados da tabela original Id_Cliente INT, Name VARCHAR(100), Data_Nascimento DATETIME, Salario FLOAT ) GO IF ((SELECT COUNT(*) FROM sys.triggers WHERE name = 'trgHistorico_Cliente' AND parent_id = OBJECT_ID('Testes.dbo.Cliente')) > 0) DROP TRIGGER trgHistorico_Cliente GO CREATE TRIGGER trgHistorico_Cliente ON Testes.dbo.Cliente -- Tabela que a trigger será associada AFTER INSERT, UPDATE, DELETE AS BEGIN SET NOCOUNT ON DECLARE @Login VARCHAR(100) = SYSTEM_USER, @HostName VARCHAR(100) = HOST_NAME(), @Data DATETIME = GETDATE() IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted)) BEGIN INSERT INTO Testes.dbo.Cliente_Log SELECT @Data, @Login, @HostName, 'UPDATE', * FROM Inserted END ELSE BEGIN IF (EXISTS(SELECT * FROM Inserted)) BEGIN INSERT INTO Testes.dbo.Cliente_Log SELECT @Data, @Login, @HostName, 'INSERT', * FROM Inserted END ELSE BEGIN INSERT INTO Testes.dbo.Cliente_Log SELECT @Data, @Login, @HostName, 'DELETE', * FROM Deleted END END END GO |
E agora vamos simular algumas alterações na base:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
INSERT INTO Testes.dbo.Cliente VALUES ('Bartolomeu', '1975-05-28', 6158.74) UPDATE Testes.dbo.Cliente SET Salario = Salario * 1.5 WHERE Name = 'Bartolomeu' DELETE FROM Testes.dbo.Cliente WHERE Name = 'André' UPDATE Testes.dbo.Cliente SET Salario = Salario * 1.1 WHERE Id_Cliente = 2 UPDATE Testes.dbo.Cliente SET Salario = 10, Name = 'Judas Iscariodes', Data_Nascimento = '06/06/2066' WHERE Id_Cliente = 1 |
Como vocês podem observar, eu estou utilizando o objeto Inserted na parte que trata o UPDATE. Com isso, eu gravo na tabela de histórico as informações novas, que estão sendo atualizadas pelo comando de UPDATE. Caso você queira gravar os valores antigos, que estão sendo substituídos, basta alterar a trigger para utilizar o objeto Deleted.
Alterações na tabela original
Caso vocês precisem incluir mais colunas na sua tabela original, lembrem-se de alterar também a tabela de histórico da sua trigger, uma vez que eu utilizei * na hora de inserir os dados justamente para não precisar ficar digitando o nome das colunas. Uma alternativa a isso, é tirar o * da trigger e especificar manualmente o nome de todas as colunas, que aí não tem problema.
Simulando que você altere a sua tabela original, sem alterar a tabela de histórico, temos essa situação:
Onde foi gerada a mensagem de erro:
Msg 8101, Level 16, State 1, Procedure trgHistorico_Cliente, Line 17
An explicit value for the identity column in table ‘Testes.dbo.Cliente_Log’ can only be specified when a column list is used and IDENTITY_INSERT is ON.
Para corrigir isso, basta adicionar a mesma alteração de estrutura realizada na tabela de log:
Caso você queira identificar a query que acionou a trigger, leia o artigo SQL Server – Como identificar a query que disparou uma trigger de DML numa tabela.
E é isso aí pessoal!
Obrigado pela visita e em caso de dúvidas, deixem aqui nos comentários.
Abraço!
E qual técnica vc utiliza no caso de uma transação que sofre rollback, para que o log não sofra rollback junto?
Olá. Caso se queira criar outros campo para armazenar os valores anterior e também os atualizados, como se faria? Exemplo:
Nome_Anterior; Nome
Data_Nascimento_Anterior; Data_Nascimento
Salario_Anterior; Salário
No caso de inclusão os campos “Anteriores ficariam vazios”. No Caso da atualização todos os estariam preenchidos, o ideal seria preencher somente aqueles campos que sofreram atualização, e na Exclusão somente os campos atuais estriam preenchidos.
É possível se criar uma triggers dessa forma?
Eduardo.
Consegue se salvar o usuário na tabela quente, replicando para a log.
Dirceu, td bem? Cara esse seu site é show de bola!! me ajuda bastante.Parabéns!. Eu implantei essa trigger e gostei mto do resultado, uma informação que precisava alterar seria o usuario, ao inves de apresentar o user do banco, apresentar o user da minha aplicação, eu consigo ajustar isso?
Para gravar as informações que foram substituídas no update devo trocar “INSERTED” por “DELETED” após o from na cláusula update?
Otimo tutorial, parabéns, me ajudou bastante aqui.
Olá, gostei do conteúdo!
Porém tenho uma dúvida!
Eu uso apenas um usuário para conectar no banco de dados do SQL Server
E os usuários são gravados dentro dos bancos de dados, e utilizados apenas na aplicação!
Como posso passar esses dados para a trigger (dados do usuário)?
Se a operação toda acontece no banco de dados, como eu vou passar o login do usuário do ERP??
Desde já agradeço!
Tutorial simples e objetivo!
Muito obrigado!!
Tutorial simples e fácil de compreender. Muito obrigado.
Obrigado pelo feedback, Smaily. Abraço.
Parabéns pelo post. Uma pergunta: Se a tabela que eu for criar a trigger tiver campos text, como eu faço para controlar, uma vez que dentro de uma trigger não é permitido usar esse tipo de campo?
Fernando,
Boa noite.
Primeiramente, obrigado pela visita. Sobre a sua dúvida, recomendo que você passe a utilizar VARCHAR(MAX) ou NVARCHAR(MAX) no lugar de TEXT, conforme orientação da própria Microsoft (referências abaixo), uma vez que os tipos de dados TEXT, NTEXT e IMAGE estão marcados como descontinuados e só estão presentes ainda no SQL 2016 por motivos de retrocompatibilidade, mas podem ser removidos em edições futuras a qualquer momento.
Referências:
– https://msdn.microsoft.com/en-us/library/ms191300.aspx
– https://msdn.microsoft.com/en-us/library/ms187993.aspx
Consegui responder sua pergunta ? Qualquer dúvida, é só falar 🙂
Dirceu bom dia,
Fiz os devidos ajustes conforme orientação, convertendo todos os campos TEXT para VARCHAR e criei uma trigger para fazer alguns testes. Está funcionando certinho.
Percebi que o conteúdo gravado no arquivo de LOG é o novo registro, ou seja com as alterações feitas na tabela, mas não tenho como saber qual campo foi alterado. Existe alguma forma de gravar os dois conteúdos (o registro antigo e o novo) para que eu possa comparar e ver o que foi alterado ou então gravar apenas os campos que sofreram a alteração?
Obrigado mais uma vez
Abraço
Fernando
Já consultou a opção de usar o CDC (Nativo) do SQLServer? Ele pode ser usado em conjunto com os triggers, e permite a consulta que você quer, só que com um pouco mais de trabalho para fazer o select.
Excelente tutorial: https://www.simple-talk.com/sql/learn-sql-server/introduction-to-change-data-capture-cdc-in-sql-server-2008/
Alexandre, fico feliz em ver você aqui. O cdc é uma excelente alternativa ao uso de triggers. Em breve terá um post sobre ele.