EF和SQL哪个更适合审计数据变更？

Question

要求似乎很简单：数据变化时，审计变化。

以下是方程中的一些重要部分：

1.我的应用程序中的数据跨越多个表（一些交叉引用表）。

2.我的DTO很深，具有条件填充的导航属性。

3.加载时，我使用其“原始值”复制原始DTO。

4.当请求保存时，原始DTO包含更改。

5.理想情况下，外键将读取像有用的文本而不是ID号码。

与TFS'酷历史功能不同，由于许多相关表和条件子实体，我的历史记录似乎更加复杂。

我看到三种可能性（到目前为止）：

1.我可以使用C#反射对象并创建一个之前/之后的记录。

2.我可以在SQL 2008R2中使用触发器捕获更改并合并之前/之后的记录。

3.我可以存储原始的之前/之后的对象，并让SQL 2008R2解析它们。

请注意：现在，对我来说，SQL 2008R2的CDC选项似乎太重了。我确实正在寻找可以构建的东西，但我承认我的思想现在是开放的。

我的问题

在我开始构建之前： 其他人如何处理复杂的EF DTO审计？

是否有一个低技术解决方案可用？

提前感谢。

以下是已经在StackOverflow上提出的相关但不完全相关的问题：使用MVC和Entity Framework实现审计日志/更改历史记录，在SQL Server中创建数据审计，如何审计Entity Framework中的多对多关系，维护跨多个表拆分的实体的审计日志，Linq to SQL审计追踪/审计日志：我应该使用触发器还是doddleaudit？，这些都没有提供答案。

- Jerry Nixon

---

不确定为什么您没有将 SQL Server 的本地审计功能列为选择之一。 - Craig Stuntz

抱歉，@Craig，你对我的数据库管理员做出了错误的假设。这超出了他们目前的技能范围，并且要求IT从解决方案团队中要求这一点将会造成噩梦（和无尽的延迟）。这只是现实情况，这就是为什么我排除它的原因。 - Jerry Nixon

好的，即使他们还不知道，至少已经有记录了。 - Craig Stuntz

Answer 1

如果审计是真正的要求，我会选择触发器解决方案...因为其他方法有几个“缺点”:

- “盲目”于应用程序之外发生的任何更改 - 如果您进行一些代码更改并忘记添加审计代码，则审计跟踪会出现“盲点”

基于触发器的解决方案可以得到保障，只有特定用户才能查看审计数据...

我通常使用Oracle，但根据我的经验，在这种情况下：只允许应用程序通过视图进行SELECT权限，任何插入/删除/更新都应通过存储过程完成，并且审计跟踪应通过触发器完成...

- Yahia

---

非常准确。此外，触发器可以使用SUSER_SNAME()函数获取正在登录并更改数据行的用户ID，而SQL Server内置的审计功能却无法做到这一点。 - HardCode

不好意思，@HardCode（和Yahia），触发器存在问题。想象一下在交叉引用表的另一侧创建了一个新的数据记录。它是在交易中创建的交叉记录之前，这意味着触发器无法将插入的记录与父记录相关联 - 审计将成为孤儿。记住，在交易中操作非常重要，因为删除的表在范围内。你如何解决这个问题？ - Jerry Nixon

@Jerry Nixon - 如果您的数据模型建立得当，您可以很好地关联审计条目...如果需要，您总是可以使用所描述的SP方法来丰富审计...我们在非常复杂的模型中使用了上述方法，从未遇到您所描述的问题（或者我可能不完全理解您的意思？）... - Yahia

不，@Yahia，那并不是真的。表上的触发器是不够的。如果您有复杂的关系，并在同一事务中更新它们（如使用EF），则根本无法有效地进行审计。也许Oracle处理事务的方式不同，但是在SQL Server中，触发器解决方案无法将一个表中的更改与相关表中的更改相关联，特别是如果事务包括对树中任何其他表的删除语句。CDC可能有效，但是不能使用触发器。 - Jerry Nixon

Answer 2

触发器解决方案，优点：

1. 无法绕过审计

触发器解决方案，缺点：

1. 无法审计非 SQL 数据
2. 无法在插入时审计复杂对象

实体框架，优点：

1. 可以审计所有内容
2. 可以审计任何状态下的复杂对象

实体框架，缺点：

1. 可以被绕过（例如直接到 SQL）
2. 需要原始值的副本

我的选择是实体框架。使用 STE 使其更容易。

无论哪种方式，您都必须自己编写代码。

- Jerry Nixon

Answer 3

我最近在Entity Framework上实现了一个审计日志管理器。当我实例化我的审计管理器时，我反射所有的实体类，并存储属性信息。然后在对象上下文SavingChanges事件中，我审计所有的更改。它工作得很好。在外键的情况下，在更改期间，我只是存储它们的Id之前和之后。

这个解决方案的好处是不需要任何额外的编码。一旦你创建了一个日志管理器，你就不必担心添加新的触发器，或者在添加新列时修改触发器。当反映类时，任何对实体类的更改都会自动被捕获。

- mservidio

---

不好意思，@Mservidio，EF存在问题。考虑一下在没有键的情况下进行插入的情况。也许父记录有一个键（或者也许没有），也许一些子记录在插入后还没有被数据库分配键。问题是，您正在创建一个不引用记录的审计记录。让我提出第二个问题，即默认值或触发器影响的值在这种方法中完全被忽略了。 - Jerry Nixon

@Jerry-Nixon 说得好。我大约一个月前编写了我的实体框架，你所说的是正确的。在我的情况下，对于新实体创建来说，不需要Id，因此我们可以在创建时不使用它们。修改和删除需要键。然而，现在你让我想到了我可以跟踪保存的实体，并在保存后进行日志记录，在检索到自动生成的列和计算列之后再进行日志记录。 - mservidio

我现在可以回去重新审查并看看我是否能够做到这一点。当然，这种方法的缺点是日志记录将发生在更改之后，因此如果日志记录存在任何问题，则无法保证始终进行日志记录。因此，如果您需要一个牢固的审计流程，同意这不是更好的方法。如果您只需要不需要在初始创建时使用ID的轻型日志记录，那么它很好。待续...如果我成功将ID保存到EF中并保存更改后提供更新。 - mservidio

我们正在尝试使用DTO的“保存前”版本和“保存后”版本创建解决方案，以从中构建审计记录。这似乎是最有前途的方法。所有我们需要的数据和密钥都存在于这两个版本中。剩下的难点只是这种更改不太安全。我们实际上需要的是“历史记录”，而不是“审计记录”，所以我们很好。我不知道你是否也需要。 - Jerry Nixon

Answer 4

好的，让我们看看。SQL Server审计已经存在，配备了工具，可能已经为您的DBA所知，不会减慢您的应用程序，并且可以跟踪应用程序本身永远不会看到的事件。

另一方面，在EF中自己编写将允许您审计非SQL Server数据源。它也不需要EE。

- Craig Stuntz