主页 / dba / 问题 / 28339
Accepted
kdis
Asked: 2012-11-08 08:49:07 +0800 CST

查找删除或更新记录

  • 772

最近几个月我发生了三起事件，其中表中的记录已被删除或整个表中的值更新为零。我们有一个由四人组成的团队，他们拥有权限并负责更新本可以执行此操作的数据库。令人失望的是，没有人承认进行了这些更改。

展望未来，我希望能够记录这些交易。我想知道其他人用什么来跟踪这些变化？他们是否使用跟踪更改的软件，或者您是否创建存储过程或跟踪文件？如果有人在他们的设施中设置了这个，我想知道他们使用什么。跟踪文件确实有我之后的信息，例如登录名机器号和 sql 语句，所以如果我提前设置它们，它会给我信息。

当这些更改发生时，我有数据库和事务日志的副本。我可以用这些旧文件做些什么来帮助追查罪魁祸首吗？提前感谢任何回复的人。我们使用的是 SQL Server 2005。

sql-server-2005 dml

2 个回答

  1. Best Answer

    fn_dblog正如其他评论员所说，这是向后看的方式。

    只允许用户通过存储过程修改数据是首先防止这种情况发生的好方法，因为您可以添加逻辑来防止用户大量修改他们不应该的记录，或者确保他们必须提供正确的值更新。这可能意味着您需要根据应用程序当前访问数据的方式进行更改。这对你来说可能也可能不可能。

    如果您不能这样做，那么使用 SQL 2005 的一种快速而简单的方法是使用触发器在表级别执行一些 DML 审计。（SQL Server 2008 及更高版本已内置审核工具）。

    您的问题的一个简单解决方案可能是：

    
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

    这将在查询尝试更新表或从表中删除时触发。它使用DBCC inputbuffer( http://msdn.microsoft.com/en-us/library/ms187730(v=sql.90).aspx ) 来获取发出的命令。这为您提供了一个表，其中填充了针对特定表发出的所有更新和删除语句。此外，它还记录了谁发表了声明，以及发表声明的时间和地点。

    现在这可能是繁忙表上的大量日志记录数据，因此可以通过将触发器更改为：

    
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

    这会记录较少的数据，但触发器仍需要对每个操作进行“评估”，因此可能会对性能产生影响，需要对其进行测量和测试。如果用户提交大量单独的语句，这也将错过操作，即；

    不会抓住：

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

    会抓住

    
delete from tbl_example where id>0 and id<1001

    希望这对未来有所帮助。

    • 5

  2. 我们使用ApexSQL Log来审计我们的生产数据库。它可以显示谁以及何时删除或更新了记录。它还跟踪数据对象的插入和创建/更改/删除语句。

    当这些更改发生时，最好有数据库和事务日志的副本，因为 ApexSQL Log 可以从事务日志中读取这些事务。如果您打算在将来使用它来跟踪更改，您的数据库必须处于完全恢复模式，因为只有这样您才能确定在线事务日志或事务日志备份包含您要读取的所有事务。我们压缩事务日志备份以节省空间

    我们安排了一个夜间作业来读取过去 24 小时的事务日志备份并将事务导出到 HTML 文件中。如果我们需要任何信息，我会检查文件并且不担心事务日志备份是否被删除（我们会在 30 天后删除它们）

    • 1

相关问题