Artigo SQL Magazine 73 - Usando CDC e Trigger em Auditorias no SQL Server 2008

Nesse artigo será demonstrado o recurso Change Data Capture (CDC) presente no MS SQL Server 2008 e como utilizar Trigger para registrar as alterações ocorridas nas tabelas. Abordaremos a melhor forma de utilizá-los em auditorias no banco de dados.

Para identificar as alterações ocorridas nas tabelas, fornecendo condições de comparação e recuperação de dados que foram excluídos e/ou alterados. Possibilita identificar também a inclusão de novos registros nas tabelas.

Quando se deseja armazenar históricos das alterações ocorridas nos dados das tabelas. Esse recurso permite o registro das atividades ocorridas nas tabelas das bases de dados, fornecendo informações importantes para uma auditoria.

Será abordado nesse artigo o Change Data Capture (CDC), recurso que permite o registro de todas as modificações ocorridas nos dados das tabelas. Será demonstrada também a utilização de trigger para o registro das alterações nos dados das tabelas.

O CDC foi implementado no SQL Server com o objetivo de identificar os dados que sofreram alterações. Sua principal utilização é no processo de ETL (Extração Transformação e Carga), processo responsável por efetuar a carga de dados em DW (Data Warehouse).

A sintaxe utilizada nos exemplos de Trigger é do SQL Server, mas a lógica da utilização de Trigger para armazenar informações sobre alterações nas tabelas pode ser utilizada em outros bancos de dados.

Nesse artigo vamos apresentar dois exemplos, o primeiro utilizando trigger, e o segundo, o recurso CDC.

Exemplo usando Trigger

O SQL Server tem três tipos de trigger: DML, DDL e de Logon. Utilizaremos as triggers DML, que serão disparadas quando ocorrer a execução de instruções DML (insert, delete ou update) nas tabelas em que elas estiverem vinculadas.

Em nosso exemplo criaremos uma base de dados chamada Teste, uma tabela na qual conterá os dados de exemplo, que chamaremos de Cliente, uma tabela para registrar as alterações, que chamaremos de Cliente_Log, e três triggers que serão responsáveis por atualizar a tabela Cliente_Log sempre que ocorrer alterações nos dados da tabela Cliente (Figura 1).

Para criar o ambiente necessário para o exemplo, abra o Microsoft SQL Server Management Studio (SSMS) e efetue o logon no SQL Server. Feito isso, teremos uma tela semelhante a da Figura 2.

Criaremos inicialmente uma base de dados chamada Teste e duas tabelas chamadas Cliente e Cliente_Log (Listagem 1).

A tabela Cliente é a nossa tabela principal, Cliente_Log é uma tabela que contém todos os campos da tabela principal, mais quatro campos adicionados no final. Esses campos serão utilizados para guardar informações sobre as operações ocorridas na tabela principal, são eles:

• Log Usuário – contém o usuário que estava conectado na instância do SQL Server quando ocorreu a operação;
• Log Data – data e hora que ocorreu a operação;
• Log Operação – tipo da operação que ocorreu, podendo ser Insert, Delete ou Update;
• Log ID – identificador sequencial.

use master
  go
   
  -- Criar a base de dados
  create database Teste
  go
  use Teste
  go
   
   
  -- Criar a tabela Cliente
  CREATE TABLE dbo.Cliente
  (
        ID_Cliente int NOT NULL IDENTITY (1, 1),
        Nome varchar(50) NOT NULL,
        Idade int NOT NULL
  ) 
  GO
  ALTER TABLE dbo.Cliente -- Adiciona ID_Cliente como chave primaria
        ADD CONSTRAINT PK_Cliente_1 PRIMARY KEY CLUSTERED (ID_Cliente)
  GO
         
   
  -- Criar a tabela Cliente_Log
  CREATE TABLE dbo.Cliente_Log
  (
        ID_Cliente int NOT NULL,
        Nome varchar(50) NOT NULL,
        Idade int NOT NULL,
        Log_Usuario varchar(30) NOT NULL,
        Log_Data datetime NOT NULL,
        Log_Operacao char(1) NOT NULL,
        Log_ID int NOT NULL IDENTITY (1, 1)
  ) 
  GO
  ALTER TABLE dbo.Cliente_Log -- Adiciona LOG_ID como chave primaria
        ADD CONSTRAINT PK_Cliente_Log_1 PRIMARY KEY CLUSTERED (LOG_ID)
  GO

Após a criação das tabelas, vamos criar três triggers, uma para cada operação (insert, update e delete), apresentadas na Listagem 2.

Observando o código da Listagem 2, notamos que as diferenças entre cada SP são mínimas, são as instruções indicando quando serão disparadas (after insert, after delete ou after update), a letra indicando qual o tipo de operação (I, U ou D), e a origem das informações (tabela inserted ou deleted). Como já informamos, elas serão disparadas quando operações de insert, update e delete são efetuadas na tabela Cliente.

A instrução after delete presente na trigger tr_d_Cliente_Log indica que essa trigger executará a instrução SQL quando uma operação de delete for executada na tabela Cliente. Dessa mesma forma, as outras triggers serão disparadas quando as demais operações forem executadas, de acordo com a instrução presente em cada uma (after insert e after update).

As instruções SQL presentes nas triggers, quando executadas, inserem registros na tabela Cliente_Log fazendo um Select na tabela inserted ou deleted. Essas tabelas são utilizadas internamente pelo SQL Server para fazer o controle das operações DML, e podem ser referenciadas nas triggers. A tabela inserted contém os registros que estão sendo inseridos na tabela, enquanto deleted armazena os registros que estão sendo apagados.

Analisando a instrução Select da trigger tr_d_Cliente_Log, verificamos que está sendo feita uma consulta nas informações que estavam na tabela Cliente antes de serem apagadas, utilizando a tabela interna deleted. Posteriormente é executada a instrução Insert que insere na tabela Cliente_Log as informações retornadas da consulta da tabela interna deleted.

As instruções presentes nas triggers fazem uso das funções getdate() para obter a data e hora atual, e da system_user para obter o usuário que está conectado na instância do SQL Server.

USE [Teste]
  GO
   
   
  CREATE trigger [dbo].[tr_d_Cliente_Log] ON [dbo].[Cliente]
  after delete 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'D' from deleted
   
  GO
   
   
  CREATE trigger [dbo].[tr_i_Cliente_Log] ON [dbo].[Cliente]
  after insert 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'I' from inserted
   
  GO
   
   
  CREATE trigger [dbo].[tr_u_Cliente_Log] ON [dbo].[Cliente]
  after update 
  as 
        insert  Cliente_Log
        select *,system_user,getdate(),'U' from deleted
   
  GO

Para testar a trigger de insert (tr_i_Cliente_Log), vamos inserir três registros na tabela Cliente, conforme a Listagem 3.

A Figura 3 mostra os registros nas tabelas após a inserção dos três registros na tabela Cliente. Nota-se na tabela Cliente_Log que os campos Log_Usuario, Log_Data e Log_Operação foram preenchidos pela trigger. Esses campos contêm informações importantes sobre o que ocorreu na tabela, podendo contribuir significativamente em um processo de auditoria no banco de dados.

USE Teste
  go
   
  -- Mostra o conteudo das tabelas antes do Insert
  select      * from Cliente
  select      * from Cliente_Log
   
  -- Insere 3 registros
  insert      Cliente (Nome, Idade) values ('Tulio',35)
  insert      Cliente (Nome, Idade) values ('João',25)
  insert      Cliente (Nome, Idade) values ('Maria',15)
   
  -- Mostra o conteudo das tabelas depois do Insert
  select      * from Cliente
  select      * from Cliente_Log

Para exemplificar as operações de exclusão e alteração de registros, execute o código da Listagem 4.

A Figura 4 mostra os registros nas tabelas após a exclusão e alteração dos registros na tabela Cliente.

Observando o campo Log_Operação da tabela Cliente_Log, temos a informação do tipo de operação que ocorreu quando foi inserido o registro, sendo “I” para inserção, “D” para exclusão e “U” para atualização.

Note que a tabela Cliente_Log contém todos os registros que estavam antes das operações de exclusão e atualização na tabela Cliente. Por exemplo, na tabela Cliente temos o nome “Fulana”, se olharmos na tabela Cliente_Log, consta o nome “Maria”, ou seja, o registro antes da operação de atualização.

Vale ressaltar que quando utilizamos triggers para inserir informações em tabelas estamos utilizando mais recursos do servidor, isto é, uma operação que iria inserir um registro na base de dados, nesse caso, passa a inserir dois registros. Por isso, é importante fazer uma análise de quais tabelas são realmente importantes e necessitam de uma tabela de Log.

USE Teste
  go
   
  -- Mostra o conteudo das tabelas antes do Delete e Update
  select      * from Cliente
  select      * from Cliente_Log
   
  -- Excluie um registro
  delete      Cliente where ID_Cliente = 2
   
  -- Altera um registro
  update      Cliente set Nome = 'Fulana' where ID_Cliente = 3
   
  -- Mostra o conteudo das tabelas depois do Delete e Update
  select      * from Cliente
  select      * from Cliente_Log

Exemplo usando o CDC

O Change Data Capture (CDC) utiliza os log (arquivos .ldf) para identificar as alterações ocorridas nas tabelas. Quando uma operação DML é executada, essas informações são salvas primeiramente nos arquivos de log e posteriormente na tabela de origem.

Através de um processo de captura, as informações sobre as alterações ocorridas na tabela que estão nos arquivos de log são lidas e salvas também em tabelas de “histórico do CDC” (Figura 5).

Em nosso exemplo sobre o CDC, iremos criar uma base chamada TesteCDC, adicionar uma tabela chamada Fornecedor, habilitar a base para utilização do CDC e incluir a tabela Fornecedor no processo de CDC.

Para criar o ambiente necessário para o exemplo, abra o SQL Server Management Studio e efetue o logon no SQL Server. Feito isso, teremos uma tela semelhante a da Figura 2.

Agora criaremos uma base chamada TesteCDC e a tabela Fornecedor, como apresenta o código da Listagem 5.

use master
  go
   
  -- Criar a base de dados
  create database TesteCDC
  go
  use TesteCDC
  go
   
   
  -- Criar a tabela Fornecedor
  CREATE TABLE dbo.Fornecedor
  (
        ID_Fornecedor int NOT NULL IDENTITY (1, 1),
        Nome varchar(50) NOT NULL,
        Telefone int NOT NULL
  ) 
  GO
  ALTER TABLE dbo.Fornecedor -- Adiciona chave primaria
  ADD CONSTRAINT PK_Fornecedor_1 PRIMARY KEY CLUSTERED (ID_Fornecedor)
  GO

Após a criação da base de dados e da tabela, usaremos a stored procedure sp_cdc_enable_db para habilitar a base de dados para utilizar o CDC, e a SP sp_cdc_enable_table para incluir a tabela no processo CDC (Listagem 6).

use TesteCDC
  go
   
  -- Habilitando a base de dados
  exec sys.sp_cdc_enable_db
  go
   
   
  -- Adicionando a tabela
  exec sp_cdc_enable_table
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @role_name = 'CDC_Role',
        @supports_net_changes = 1
  Go

Para verificar quais bases de dados estão com o recurso CDC habilitado, podemos fazer uma consulta na tabela sys.Database, buscando pelo campo is_cdc_enabled igual a 1. Para verificar quais tabelas estão incluídas no CDC, devemos consultar a tabela sys.Tables, buscando pelo campo is_tracked_by_cdc igual a 1. A Listagem 7 mostra as duas consultas, tendo o resultado mostrado na Figura 6.

use master
  go
   
  select      name,
              is_cdc_enabled
  from        sys.databases
  where       is_cdc_enabled = 1
   
   
  -- Tabela
  use TesteCDC
  go
   
  select      name,
              is_tracked_by_cdc
  from        sys.tables
  where       is_tracked_by_cdc = 1

Quando executamos a SP sp_cdc_enable_db para habilitar a base de dados para utilização do CDC, são criados os seguintes objetos no SQL Server: um Schema chamado cdc, um usuário chamado cdc e cinco tabelas de sistemas que conterão informações sobre o CDC (Figura 7).

Quando executamos a SP sp_cdc_enable_table para incluir a tabela no CDC, é criada uma nova tabela de sistema com o nome da tabela de origem contendo no final a identificação _CT. Essa tabela contém todos os campos da tabela de origem mais cinco campos de controle, conforme a Figura 8. Em nosso exemplo também será criada, se não existir, uma Role chamada CDC_Role, que será responsável por permitir o acesso às informações nas tabelas _CT quando o usuário não tiver permissão de db_owner.

Ao executar a SP sp_cdc_enable_table pela primeira vez, serão criados dois JOBs (Figura 9). No nosso exemplo, o primeiro é chamado cdc_dbo_TesteCDC_capture, o qual será iniciado junto com a instância do SQL Server e será responsável pela leitura das informações sobre alterações que foram registradas nos arquivos de log. As alterações identificadas serão salvas na tabela de sistema que foi criada com a identificação _CT. O segundo JOB criado é o cdc_TesteCDC_cleanup, responsável por apagar as informações antigas das tabelas _CT. Por default, esse JOB é executado diariamente às 2:00 hs, e a cada execução são apagados os registros das tabelas _CT que tenham mais de 72 horas (antigos). Caso seja necessário manter por mais tempo as informações nas tabelas _CT, podemos alterar o tempo de retenção utilizando a SP sp_cdc_change_job. O tempo máximo permitido é de 100 anos, o valor para o parâmetro da SP chamado retention tem que ser informado em minutos. A Listagem 8 mostra a alteração para 100 anos. Uma boa prática é manter um período pequeno (um mês, dependendo da quantidade de transações) e criar uma rotina para descarte dos registros antigos, salvando-os em outro SQL Server ou em arquivos texto (.txt), para o caso de ser necessária a consulta dessas informações no futuro.

use TesteCDC
  go
   
  exec sp_cdc_change_job @job_type='cleanup', @retention=52494800
  go

Após habilitar a base de dados para utilizar o CDC e incluir a tabela de exemplo Fornecedor, iremos inserir três registros nesta tabela e depois selecionar os registros nas tabelas Fornecedor e cdc.dbo_Fornecedor_TC para mostrar o que ocorreu (veja o código na Listagem 9).

A Figura 10 mostra o resultado obtido após a execução do código da Listagem 9.

Para realizar consultas nas tabelas de sistemas terminadas em _CT podemos também utilizar a função (FN) cdc.fn_get_net_changes_dbo_Fornecedor. Essa função é criada no momento em que adicionamos a tabela Fornecedor no CDC. A Listagem 10 demonstra a utilização da FN.

USE TesteCDC
  go
   
  -- Mostra o conteudo das tabelas antes do Insert
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT
   
  -- Insere 3 registros
  insert      Fornecedor(Nome, Telefone) values ('Tulio',1111)
  insert      Fornecedor (Nome, Telefone) values ('João',2222)
  insert      Fornecedor (Nome, Telefone) values ('Maria',3333)
   
  -- Mostra o conteudo das tabelas depois do Insert
  -- Esperar 5 segundos antes de executar essa parte
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT

use TesteCDC
  go
   
  declare @from_lsn binary(10),
              @to_lsn binary(10)
              
  set @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Fornecedor') -- Menor data
  set @to_lsn   = sys.fn_cdc_get_max_lsn() -- Maior data
   
  select * from
   cdc.fn_cdc_get_net_changes_dbo_Fornecedor(@from_lsn, @to_lsn, 'all'); 

Se observarmos a Figura 10, veremos que o primeiro resultado (tabela Fornecedor) retornou os registros que foram inseridos. O segundo resultado retornou o mesmo conteúdo da tabela Fornecedor mais algumas informações adicionais. Dentre estas informações, as mais importantes são __$start_lsn, que contém a data e a hora que a operação foi executada, e __$operation, que contém o tipo de operação.

Para visualizar a data que está no campo __$start_lsn podemos fazer uso da função sys.fn_cdc_map_lsn_to_time.

A Tabela 1 mostra o significado dos códigos do campo __$operation.

Para entender melhor o exemplo, vamos alterar um registro e excluir outro (veja a Listagem 11). Na Figura 11 apresentamos os resultados após a alteração e exclusão dos registros. O primeiro resultado mostra a tabela Fornecedor, que contém agora dois registros. Para saber o que ocorreu nessa tabela, recorremos à tabela cdc.dbo_Fornecedor_CT.

Observando cdc.dbo_Fornecedor_CT, temos nos três primeiros registros a informação de inclusão, pois no campo __$operation está presente o código 2. O conteúdo inserido é o que consta nos últimos campos desses registros (ID_Fornecedor, Nome e Telefone).

O quarto e quinto registro da tabela cdc.dbo_Fornecedor_CT contém informações sobre as alterações ocorridas na tabela Fornecedor. O campo __$operation com código 3 informa que o registro contém os dados que existiam na tabela Fornecedor antes da alteração, e no quinto registro (__$operation = 4) os dados após a alteração. O CDC não registra o usuário que executou a instrução DML na tabela.

USE TesteCDC
  go
   
  -- Alteracao
  update dbo.Fornecedor set Nome = 'Tulio Rosa' where ID_Fornecedor = 1
   
  -- Exclusao
  delete dbo.Fornecedor where ID_Fornecedor = 2
   
  -- Mostra o conteudo das tabelas depois da alteracao e exclusao
  -- Esperar 5 segundos antes de executar essa parte
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT

A Listagem 12 mostra como fazer para adicionar o campo Estado na tabela Fornecedor.

Como foi habilitado o CDC na tabela Fornecedor, ao adicionar um novo campo as informações referentes a alterações (instruções DDL) são armazenadas na tabela cdc.ddl_history. A Listagem 13 mostra como efetuar a consulta nessa tabela, e o resultado pode ser conferido na Figura 12.

use TesteCDC
  go
   
  -- Adicionando o campo estado
  alter table dbo.Fornecedor add
        Estado char(2)
  GO

use TesteCDC
  go
   
  select      * from Fornecedor
  select      * from cdc.dbo_Fornecedor_CT
   
  select      OBJECT_NAME(source_object_id) as 'Tabela',
              OBJECT_NAME(OBJECT_ID) as 'Tabela_CT',
              ddl_command as 'DDL',
              ddl_time as 'Data'
  from        cdc.ddl_history

Como pode ser verificado na Figura 12, o novo campo foi adicionado na tabela Fornecedor, mas não foi adicionado na tabela Fornecedor_CT. Para resolver esse problema a forma mais simples é retirar e adicionar novamente a tabela Fornecedor no CDC, conforme a Listagem 14. É importante observar que quando retiramos uma tabela do CDC, a tabela corespondente _CT é apagada, perdendo todo o seu conteúdo. Então, se existe a intenção de manter os dados das tabelas _CT, os registros dessas tabelas precisam ser copiados para outra tabela antes da operação de retirar a tabela do CDC.

use TesteCDC
  go
   
  -- Retirando a tabela
  exec sp_cdc_disable_table 
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @capture_instance = 'all'
  go
   
  -- Adicionando a tabela
  exec sp_cdc_enable_table
        @source_schema = 'dbo',
        @source_name = 'Fornecedor',
        @role_name = 'CDC_Role',
        @supports_net_changes = 1
  go

Para verificar a utilização do novo campo, iremos inserir um registro e alterá-lo, de acordo com a Listagem 15. O novo resultado pode ser conferido na Figura 13.

use TesteCDC
  go
   
  -- Inserindo
  Insert Fornecedor(Nome, Telefone,Estado) values ('Fabiola',5555,'GO')
   
  -- Alteracao
  update dbo.Fornecedor set Estado = 'DF' where ID_Fornecedor = 3

Para melhorar a visualização do histórico (tabelas _CT), podemos criar uma stored procedure para formatar o resultado destas tabelas. A Listagem 16 mostra o código de criação da SP. Ela recebe como parâmetros o nome da tabela, a data inicial e a data final. Estas datas são referentes ao período em que se deseja visualizar as alterações. A Listagem 17 mostra a sua utilização. Observando o resultado na Figura 14, temos a data em que ocorreu a operação, o tipo da operação e os campos do registro da tabela de origem.

A Tabela 2 mostra o significado dos códigos do campo Operação, da Figura 14.

create proc cdc.s_historico @tabela varchar(100), @data_inicial varchar(23), @data_final varchar(23)
  as
   
  exec
  ('
  select      sys.fn_cdc_map_lsn_to_time(__$start_lsn) as Data,
              case __$operation 
              when 1 then ''D''
              when 2 then ''I''
              when 3 then ''UA''
              when 4 then ''UD''
              end as Operacao,
              *
  into  #temp
  from  cdc.' + @tabela + ' 
  where sys.fn_cdc_map_lsn_to_time(__$start_lsn)
              between ''' + @data_inicial + ''' and ''' + @data_final + ''' 
   
  alter table #temp drop column __$start_lsn
  alter table #temp drop column __$operation
  alter table #temp drop column __$update_mask
  alter table #temp drop column __$seqval
  alter table #temp drop column __$end_lsn
   
  select      *
  from  #temp
  order by Data
  ') 

use TesteCDC
  go
   
  exec cdc.s_historico 'dbo_Fornecedor_CT','2009-11-01','2009-11-30'
  go

Considerações sobre Trigger e CDC

Como podemos observar, tanto a utilização dos recursos de Triggers como CDC inserem registros em tabelas na base de dados, e com isso temos o aumento de utilização de recursos do servidor onde está instalado a instância do SQL Server.

A utilização do CDC é mais vantajosa que as Triggers devido ao fato das atualizações serem buscadas dos arquivos de log, de forma assíncrona, enquanto as Triggers fazem a inclusão dos registros em todas as tabelas de forma síncrona.

Outra vantagem do CDC é que podemos pará-lo em momentos críticos, isto é, onde esteja ocorrendo um grande número de transações (atividade intensa no banco de dados), e voltar a atualizar as tabelas _CT quando a carga no servidor estiver mais baixa, sem perda de informações.

Para suspender temporariamente o CDC, utilizamos a SP sys.sp_cdc_stop_job, e para voltar a utilizar o CDC, utilizamos a SP sys.cdc_start_job. É importante saber que o arquivo de log aumentará enquanto o serviço estiver suspenso, uma vez que as informações sobre as alterações nos dados das tabelas são salvas nos arquivos de log, e ficam disponíveis até o CDC fazer a leitura. Após voltar a utilizar o CDC as informações que estavam nos arquivos de log são liberadas para serem descartadas ou incluídas em backup.

Conclusão

Demonstramos nesse artigo duas formas de armazenar informações sobre as operações (insert, update e delete) que ocorrem nas tabelas, através de Triggers e CDC. Podemos utilizar essas informações para entender o que ocorreu na tabela e quando, o que é muito útil em um processo de auditoria.

Imagine o seguinte cenário: um funcionário da empresa que tenha acesso ao sistema de contas a pagar e receber altera de forma fraudulenta o valor que a empresa tem a receber de um determinado cliente. Através da utilização dos recursos demonstrados nesse artigo é possível identificar quando foi feita a alteração, por quem e ainda teremos condições de corrigir o valor alterado, voltando ao valor inicial.

Para concluir, vale destacar que a utilização dos recursos de triggers ou do CDC aumentarão o consumo de recursos do servidor onde está instalado a instância do SQL Server. Por isso, tenha cuidado ao utilizar estes recursos e evite que o desempenho do banco seja prejudicado.

Confira também

Cursos de SQL e Banco de Dados

Curso

Curso de SQL Server

Curso

Guia Completo de SQL Server

Guia