Plano de auditoria de alteração de dados

Não se preocupe em rastrear dados de auditoria por campo. É muito mais fácil ter uma tabela de auditoria com a mesma estrutura da tabela principal e registro de data e hora e informações do usuário rastreadas na tabela.

Essa arquitetura tem algumas vantagens:

• Você pode facilmente criar uma exibição na tabela principal e na tabela de auditoria para mostrar um histórico completo, incluindo as versões atuais.
• É relativamente simples implementar os gatilhos de auditoria - na verdade, se você tiver um grande número de tabelas, poderá escrever um utilitário para gerá-las a partir do dicionário de dados do sistema.
• As tabelas de auditoria podem ser colocadas em um disco separado para reduzir a carga de E/S no disco com as tabelas principais.
• É muito fácil reconstruir uma versão atual do registro, em vez de aplicar uma lista de alterações no nível do campo ao contrário.

As informações de auditoria devem incluir pelo menos: usuário que criou/alterou o registro, data/hora da criação/alteração, natureza da alteração (inserir, atualizar, excluir). Você pode querer usar a exclusão lógica (ou seja, um sinalizador 'Excluído') se tiver a opção de fazê-lo. Caso contrário, você precisa capturar o usuário e a data/hora da sessão e colocá-los no registro de exclusão, provavelmente junto com o último estado do registro. Se isso não estiver disponível na conexão (geralmente o caso em aplicativos de N camadas em que o usuário não está sendo representado no nível do banco de dados), você precisará encontrar outra maneira de obtê-lo.

Se estiver usando o SQL Server 2008 Enterprise Edition ou mais recente, você também pode considerar o uso do Change Data Capture , que:

• tem um impacto menor no sistema do que os gatilhos;
• cobre os cenários que você mencionou;
• a lógica de auditoria é comprovadamente incorporada ao SQL Server (em vez de gatilhos, que você mesmo teria que implementar).

Infelizmente, o CDC não funcionará como um substituto.

O CDC não inclui a OMS ao capturar alterações de dados; apenas o quê e o quando. Se você não precisa saber quem fez a alteração, o CDC funcionará, mas se a OMS for tão importante quanto o quê e quando, infelizmente, o CDC ficará aquém; muito curto.

Você pode fazer isso por meio de gatilhos de auditoria que fornecem alterações de dados de auditoria como respostas para as seguintes perguntas:

1. Quem mudou os dados?
2. Qual foi a data e a hora em que ocorreu uma alteração?
3. Qual software cliente foi usado para acessar os dados?
4. Caso a solicitação fosse modificação de dados (a instrução UPDATE), qual era o valor dos dados antes e depois da alteração?

Isso significa que quando um gatilho é disparado, todas essas informações devem ser coletadas de um banco de dados ou instância do SQL Server e armazenadas em um repositório de auditoria. Por exemplo, um gatilho disparado quando um registro é inserido na tabela Currency.Sales é criado usando o método seguinte SQL

CREATE TRIGGER Sales.tr_i_AUDIT_Currency
ON Sales.Currency
    FOR INSERT
    NOT FOR REPLICATION
AS

Em seguida, a trigger deve inserir o nome do computador onde INSERT foi executado, o nome do usuário da pessoa que inseriu o registro e o nome do aplicativo utilizado para inserir o registro. Os gatilhos aproveitam as funções integradas do SQL Server para obter estes valores:

• HOST_NAME () retorna o nome da estação de trabalho
• APP_NAME() retorna um nome do aplicativo na sessão atual
• SUSER_SNAME() – retorna o nome de login
• GETDATE() – retorna o registro de data e hora do sistema para o banco de dados atualmente conectado

Esses valores são armazenados em uma tabela predefinida usada como um repositório de dados de auditoria

INSERT
INTO AdventureWorks2012.dbo.AUDIT_LOG_TRANSACTIONS
(
     TABLE_NAME,
     TABLE_SCHEMA,
     AUDIT_ACTION_ID,
     HOST_NAME,
     APP_NAME,
     MODIFIED_BY,
     MODIFIED_DATE,
     [DATABASE]
)
VALUES(
'Currency',
'Sales',
2, --   ACTION ID For INSERT
CASE
WHEN
       LEN(HOST_NAME())
       <
       1 THEN ' '
    ELSE HOST_NAME()
END,
CASE
WHEN
       LEN(APP_NAME())
       <
       1 THEN ' '
    ELSE APP_NAME()
END,
SUSER_SNAME(),
GETDATE(),
'AdventureWorks2012'
);