Data

5 abr, 2018

SQL Server: auditando dados com tabelas temporais

Publicidade

Auditoria de dados é um assunto cada vez mais quente. Saber quando, onde e quem alterou um registro é de importância vital para muitos tipos de sistemas.

O SQL Server oferece um recurso bastante simples e prático de manter essa auditoria: o uso de tabelas temporais. Este recurso surgiu com o SQL Server 2016, mas pouca gente conhece e/ou utiliza essa funcionalidade.

Como funcionam

No passado, após criar uma tabela, era necessário definir uma segunda versão da tabela para manter o histórico das mudanças, e então construir uma série de gatilhos (“triggers”) para transferir os dados da tabela principal para a histórica. Isso poderia ser bastante trabalhoso, dependendo da quantidade de tabelas que se precisasse auditar.

Com o uso da tabela temporal, ao se criar uma tabela que precisa de auditoria, é criada automaticamente uma tabela histórica que armazena todas as alterações dos registros e todos os mecanismos que irão movimentar os dados. Tudo controlado pelo próprio SGBD.

Uma vez criada a tabela temporal, todas as alterações de dados, isto é, UPDATEs e DELETEs, serão registradas na tabela histórica.

Sintaxe

A definição de uma tabela temporal exige a inclusão de alguns campos e argumentos específicos. É obrigatório que este tipo de tabela possua uma chave primária e alguns outros requisitos, como mostra o modelo a seguir:

USE [BasePrincipal]
GO  
CREATE TABLE [NomeDaTabela] ( 
    [CampoChave] [TipoDeDados] NOT NULL PRIMARY KEY                 -- obrigatório
    , ............                                                  -- lista de campos
    , SysStartTime DateTime2 GENERATED ALWAYS AS ROW START NOT NULL -- obrigatório
    , SysEndTime   DateTime2 GENERATED ALWAYS AS ROW END NOT NULL   -- obrigatório
    , PERIOD       FOR SYSTEM_TIME (SysStartTime,SysEndTime)        -- obrigatório
    ) 
WITH (SYSTEM_VERSIONING = ON  (HISTORY_TABLE = [TabelaHistorica])); -- obrigatório
GO

Os campos SysStartTime e SysEndTime são obrigatórios porque é através deles que identificamos a versão dos dados que buscamos. Observe que ambos são gerados automaticamente quando se insere cada registro novo. O argumento HISTORY_TABLE é opcional.

Caso ele seja omitido, o SGBD criará uma tabela histórica com o nome dbo.MSSQL_TemporalHistoryFor_@ObjectID, onde @ObjectID é o código da tabela origem, conforme registrado no catálogo do banco de dados (sys.objects).

Exemplo prático

Vejamos um exemplo prático. Vamos construir uma tabela temporal e inserir dados, como mostra o quadro a seguir.

--criando tabela temporal
CREATE TABLE dbo.MinhaTabelaTemporal
	(ID int identity(1,1) primary key	                           
	,A int
	,B int
	,C AS A * B
	,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL
	,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL 
	,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime))        
WITH(SYSTEM_VERSIONING = ON  (HISTORY_TABLE = dbo.MinhaTabelaTemporal_History))

GO

-- inserindo dados
INSERT [dbo].MinhaTabelaTemporal (A,B) VALUES( 5, 2)
INSERT [dbo].MinhaTabelaTemporal (A,B) VALUES( 6, 3)

GO

Agora vejamos os conteúdos da tabela temporal e histórica.

-- lendo dados da tabela temporal
SELECT * FROM [dbo].MinhaTabelaTemporal
ID   A   B   C SysStartTime SysEndTime
1 5 2 10 2018-03-26 20:58:53.5168975 9999-12-31 23:59:59.9999999
2 6 3 18 2018-03-26 20:58:53.5168975 9999-12-31 23:59:59.9999999
-- lendo dados da tabela histórica
SELECT * FROM [dbo]. MinhaTabelaTemporal_History
ID  A   B   C   SysStartTime SysEndTime

Como era de se esperar, a tabela histórica se encontra vazia, pois ela não registra operações de INSERT: apenas UPDATEs e DELETEs.

Agora vamos a outro teste, onde alteramos os dados existentes.

--alterando dados
UPDATE [dbo].MinhaTabelaTemporal SET A= 15 WHERE ID = 1

DELETE FROM [dbo].MinhaTabelaTemporal  WHERE ID = 2

GO

Ao consultarmos novamente as duas tabelas, constatamos que os dados antigos foram gravados na tabela histórica.

-- lendo dados da tabela temporal
SELECT * FROM [dbo].MinhaTabelaTemporal

ID   A   B   C   SysStartTime SysEndTime
1 15 2 30 2018-03-26 21:10:21.7522415 9999-12-31 23:59:59.9999999
-- lendo dados da tabela histórica
SELECT * FROM [dbo]. MinhaTabelaTemporal_History
ID   A   B   C   SysStartTime SysEndTime
1 5 2 10 2018-03-26 21:03:38.9078863 2018-03-26 21:10:21.7522415
2 6 3 18 2018-03-26 21:03:38.9078863 2018-03-26 21:10:21.7526550

Excluindo a tabela temporal

Caso precise excluir a tabela temporal ou queira apenas desabilitar o versionamento temporariamente, será necessário alterar o argumento SYSTEM_VERSIONING.

ALTER TABLE dbo.MinhaTabelaTemporal SET (SYSTEM_VERSIONING = OFF) ;

Uma vez desabilitado o versionamento, poderemos excluir ambas as tabelas, temporal e histórica.

--excluindo tabela temporal	
DROP TABLE [dbo].MinhaTabelaTemporal
GO

DROP TABLE dbo.MinhaTabelaTemporal_History
GO

Caso você mantenha o nome default da tabela histórica, ela será automaticamente excluída quando se apagar a tabela temporal.

Comentários finais

Tabelas temporais são muito úteis e fáceis de implementar, mas você deve ter observado que elas apresentam algumas limitações em termos de auditoria.

Elas registram apenas dados dos campos existentes na tabela. Não se grava, por exemplo, quem foi a pessoa que executou a alteração nos dados. Para contornar essa questão ainda é necessário adaptar a aplicação de modo que ela registre o nome e/ou ID da pessoa que executa as operações auditadas.

De qualquer modo, a facilidade de implementação e facilidade de manutenção, que virtualmente é igual a zero, são grandes atrativos para quem deseja manter um controle detalhado da alteração de dados sensíveis.

Leituras sugeridas