主页 / dba / 问题 / 9960
Accepted
Lumpy
Asked: 2011-12-31 08:02:39 +0800 CST

数据变更审计计划

  • 772

我的公司正在从分布式 Access 数据库模型转变为使用集中式 SQL 数据库。数据表的设计使得所有表都有修改日期。在讨论中有人建议，因为我们将在每个表上创建一个触发器来处理修改日期，也许我们应该让触发器也将一些信息记录到审计表中。

这是设置审计的最佳方式以便我们可以跟踪谁在更改信息还是有更好的方式？欢迎链接到有关该主题的文章。

对于审计，我正在寻找捕获表名、列名、修改日期、行 ID 和进行更改的人的用户名。是否有任何我没有想到的信息应该被捕获，以帮助我避免未来的陷阱？

sql-server audit

4 个回答

  1. Best Answer

    不要费心在每个字段的基础上跟踪审计数据。拥有一个与主表具有相同结构并在表上跟踪时间戳和用户信息的审计表要容易得多。

    这种架构有几个优点：

    • 您可以轻松创建跨主表和审计表的视图，以显示包括当前版本在内的完整历史记录。
    • 审计触发器的实现相对简单——事实上，如果你有大量的表，你可以编写一个实用程序从系统数据字典中生成它们。
    • 审计表可以放在单独的磁盘上，以减少主表在磁盘上的 I/O 负载。
    • 重建记录的原样版本非常容易，而不必反向应用字段级更改列表。

    审计信息应至少包括：创建/更改记录的用户、创建/更改的日期/时间、更改的性质（插入、更新、删除）。如果您可以选择这样做，您可能希望使用逻辑删除（即“已删除”标志）。否则，您需要从会话中捕获用户和日期/时间并将它们放在删除记录中，可能连同记录的最后状态。如果这在连接中不可用（通常是在 N 层应用程序中用户未在数据库级别被模拟的情况），那么您需要找到其他方法来获取它。

    • 7

  2. 如果您使用的是 SQL Server 2008 Enterprise Edition 或更新版本，您还可以考虑使用Change Data Capture，它：

    • 对系统的影响比触发器低；
    • 涵盖您提到的场景；
    • 审计逻辑已被证明内置于 SQL Server 中（而不是您必须自己实现的触发器）。
    • 3

  3. 遗憾的是，CDC 无法作为替代品。

    CDC 在捕获数据变化时不包括 WHO；只有什么和什么时候。如果您不需要知道是谁做出了改变，那么 CDC 将发挥作用，但如果 WHO 与什么和什么时候一样重要，那么可悲的是，CDC 达不到要求；很短。

    • 1

  4. 您可以通过审计触发器执行此操作，审计触发器提供审计数据更改作为对以下问题的回答：

    1. 谁更改了数据？
    2. 发生变化的日期和时间是什么时候？
    3. 使用哪个客户端软件访问数据？
    4. 如果请求是数据修改（UPDATE 语句），更改前后的数据值是多少？

    这意味着当触发触发器时，所有这些信息都必须从数据库或 SQL Server 实例中收集并存储在审计存储库中。例如，当记录插入 Currency.Sales 表时触发触发器是使用以下 SQL

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

    然后，触发器必须插入执行 INSERT 的计算机名、插入记录的人的用户名和用于插入记录的应用程序的名称。触发器利用内置的 SQL Server 函数来获取这些值：

    • HOST_NAME() 返回工作站名称
    • APP_NAME() 返回当前会话中应用程序的名称
    • SUSER_SNAME() – 返回登录名
    • GETDATE() – 返回当前连接的数据库的系统时间戳

    这些值存储在用作审计数据存储库的预定义表中

    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'
);
    • 0

相关问题