Início / dba / Perguntas / 28339
Accepted
kdis
Asked: 2012-11-08 08:49:07 +0800 CST

Encontrar registros de exclusão ou atualização

  • 772

Tive três incidentes nos últimos meses em que registros em uma tabela foram excluídos ou valores atualizados para zero em uma tabela inteira. Temos uma equipe de quatro pessoas que têm permissão e são responsáveis ​​por atualizar o banco de dados que poderiam ter feito isso. Lamentavelmente, ninguém admitiu ter feito as mudanças.

No futuro, gostaria de poder ter um registro dessas transações. Eu queria saber o que outras pessoas usam para rastrear essas mudanças? Eles usam software que rastreia alterações ou você cria procedimentos armazenados ou arquivos de rastreamento? Se alguém tiver isso configurado em suas instalações, gostaria de saber o que eles usam. Os arquivos de rastreamento têm as informações que procuro, como nome de login, número da máquina e a instrução sql, portanto, ele me fornecerá as informações se eu as configurar com antecedência.

Tenho cópias do banco de dados e dos logs de transação quando essas alterações ocorreram. Há algo que eu possa fazer com esses arquivos antigos para ajudar a rastrear o culpado? Desde já agradeço a quem responder. Estamos usando o SQL Server 2005.

sql-server-2005 dml

2 respostas

  1. Best Answer

    fn_dblogé a maneira de olhar para trás, como disseram os outros comentaristas.

    Permitir apenas que os usuários modifiquem os dados por meio de procedimentos armazenados é uma ótima maneira de evitar que isso aconteça em primeiro lugar, pois você pode adicionar lógica para impedir que os usuários modifiquem registros em massa que não deveriam ou garantir que eles forneçam valores corretos para atualizações. Isso pode significar que você precisaria fazer alterações no aplicativo, dependendo de como eles estão acessando os dados no momento. O que pode ou não ser possível para você.

    Se você não puder fazer isso, uma maneira rápida e simples com o SQL 2005 será usar um gatilho para fazer alguma auditoria DML no nível da tabela. (SQL Server 2008 em diante tem ferramentas de auditoria incorporadas).

    Uma solução simples para o seu problema pode ser:

    
drop table audit_test
go
create table audit
(
uname varchar(50),
[date] datetime,
what nvarchar(4000),
host varchar(50),
)
go 

create trigger ddlcheck on tbl_example 
for update, delete
as
declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp

    Isso será acionado sempre que uma consulta tentar atualizar a tabela ou excluir da tabela. Ele usa DBCC inputbuffer( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) para obter o comando emitido. Isso fornece uma tabela preenchida com todas as instruções de atualização e exclusão emitidas em uma tabela específica. Além disso, registra quem emitiu declaração, onde e quando foi.

    Agora, isso pode ser um monte de dados de registro em uma tabela ocupada, então isso pode ser restrito apenas para capturar consultas 'ruins' (digamos, aquelas que atualizam/excluem mais de 1000 linhas) alterando o gatilho para:

    
create trigger ddlcheck on tbl_example 
for update, delete
as
declare @cnt integer
select @cnt=count(1) from deleted
if @cnt>1000
begin
  declare @tbltmp table(eventtype nvarchar(30),para smallint, strsql nvarchar(4000))
  insert into @tbltmp exec ('dbcc inputbuffer('+@@spid+')')
  insert into audit_test select SUSER_NAME(), GETDATE(),  strsql ,  HOST_NAME() from @tbltmp
end

    Isso registra menos dados, mas o gatilho ainda precisa 'avaliar' para cada operação, portanto, pode ter um impacto no desempenho que precisará ser medido e testado. Isso também perderá as ações se o usuário enviar muitas declarações individuais, ou seja;

    não vai pegar:

    
delete from tbl_example where id=1
delete from tbl_example where id=2
.....
delete from tbl_exampe where id=1000

    Vai pegar

    
delete from tbl_example where id>0 and id<1001

    Espero que isso seja de alguma ajuda para o futuro.

    • 5

  2. Usamos ApexSQL Log para auditar nossos bancos de dados de produção. Ele pode mostrar quem e quando excluiu ou atualizou os registros. Ele também rastreia inserções e instruções Create/alter/drop para objetos de dados.

    É bom que você tenha cópias do banco de dados e dos logs de transações quando essas alterações ocorreram, pois o ApexSQL Log pode ler essas transações dos logs de transações. Se você planeja usá-lo para rastrear alterações no futuro, seus bancos de dados devem estar no modelo de recuperação total, pois somente assim você pode ter certeza de que o log de transações on-line ou um backup de log de transações contém todas as transações que você deseja ler. Compactamos nossos backups de log de transações para economizar espaço

    Temos um trabalho noturno agendado para ler os backups do log de transações das últimas 24 horas e exportar as transações para um arquivo HTML. Se precisarmos de alguma informação, verifico o arquivo e não me preocupo se os backups do log de transações forem excluídos (excluímos após 30 dias)

    • 1

relate perguntas