使用 SQL Server 在 Web 应用程序中进行审计跟踪
我们正在使用 asp.net 和 sql server 开发一个 Web 应用程序。我们需要对该应用程序进行审核跟踪。据我了解，审计跟踪基本上是针对数据库上的所有插入、更新和删除，对吧？现在解决这个问题的方法是，我在数据库中有一个审计跟踪表，该表在每次插入、更新或删除被触发后填充（在 DAL 中手动编写脚本）。然而，任何直接从 SQL Management studio 触发的数据库更改都不会被记录（出于显而易见的原因：P）。
为了满足这一点，我可以创建一个触发器来处理所有事情。我还做了一些谷歌搜索，发现 SQL Server 有能力进行审计跟踪。然而，使用触发器的问题是我不会获取登录网站的用户信息。我会得到 sql 用户，但我不会对此大惊小怪，我担心网站用户。
我想出的解决方案是 a) 从我的 Web 应用程序中获取审核跟踪并设置触发器。在审计报告中，我仅显示来自 Web 应用程序的审计日志和来自 SQL Server 的审计日志。 这种方法的明显问题是：开销。每次数据库更改时写入两组不同的表。
b) 我在每个数据库表上都有一个名为 UserId 的列。然后我创建一个触发器来捕获所有数据库更改。我在更改（插入、更新、删除）的每个表上传递此 userId，并从触发器中获取此 id。 明显的挫折：每个表中都有不必要的 userid 列，
我对这篇长帖子表示歉意。基本上，我需要一个审核日志来记录所有数据库更改（包括直接破解数据库），但同时为我提供从 Web 应用程序进行的那些数据库更改的用户登录信息。
将不胜感激这方面的任何意见。
非常感谢谢
舒
We are developing a web application using asp.net and sql server. We are required to do an Audit trail for the application. As I understand this, an audit trail would basically be for all the Inserts, Updates and Deletes on the data base right? Now the way to approach this is that I have an Audit Trail Table in the DB that populates after every insert,update or delete is fired (Manually writing the script within the DAL). However any DB changes directly fired from SQL Management studio will NOT be recorded (for obvious reasons :P).
To cater for that I could create a trigger and that takes care of everything. I also did some googling and found out that SQL server has the ability to do audit trail. However the problem with using triggers is that I will NOT get the user information that logged in the website. I will get the sql user but I dont give two hoots about that, I am concerned about the website user.
A solution that I figured out was either
a) Have an audit trail from my web application and also have trigger set up. On the audit report, I simply show an audit log from web application and and audit log from sql server.
Obvious problems with this approach: over head. Writing to two different sets of tables on EVERY DB CHANGE.
b) I have a column called UserId ON EVERY DB TABLE. Then I create a trigger to capture all the DB changes. I pass this userId on every table I change (insert,update,delete) and the get this id from the trigger.
Obvious setbacks: unneccesary userid column in every table
I do appologize for the long post. Basically I need an audit log that does log all the db changes (including direct hack to db) but at the same times gives me user login information for those db changes that were made FROM the web application.
Will appreciate any input in this regard.
Many thanks
xeshu
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
通过 SQL 管理工作室或其他方式直接对数据库执行 SQL 查询，对数据库进行合法更改的可能性有多大。我建议假设数据中的所有数据都是通过您的应用程序输入的，并且在您的 BL 层中进行审核。然后，您可以简单地将数据库的访问权限限制为受信任的用户。最终必须有一个或多个用户有权更改数据库模式，如果这些用户想要绕过审核，他们可以简单地禁用触发器或伪造审核跟踪。如果有合法理由对数据库运行直接 SQL 查询，例如不频繁地从其他系统导入数据等，那么您可以将此活动限制为受信任的用户，并确保他们的导入脚本正确填充审核表。无论如何，任何会给 DBA 或可信用户带来过多工作量的内容都应该内置到应用程序中。
How likely is it that there are going to be legitimate changes made to the DB by directly executing SQL queries against the database either through SQL management studio or otherwise. I would recomend assuming that all data in the data is entered via your application and have the auditing in your BL layer. You can then simply restrict access to the database to trusted users. Ultimately there has to be one or more users with permsion to alter the database schema, if those users wanted to bypass the auditing they could simply disable the triggers or fake the audit trail. If there are ever legitimate reasons for running direct SQL queries against the DB, e.g. infrequent data imports from other systems etc, then you can limit this acitivity to the trusted users and ensure their import scripts correctly populate the audit table. Anything that would put too much workload on your DBAs or whoever the trusted users are should be built into the appllication anyhow.
经过一番谷歌搜索后，我认为这是合适的方法：通用审计表
Audit_Table (
ID，
表名,
RecordId，（链接到相关记录）
修改者，
修改于，
类型（I、U 或 D）
)
Audit_Details_Table (
ID，
审核ID,
字段名称,
旧值，
新价值）
After some googling this is the approach that I think would be appropriate: Generic Audit Table
Audit_Table (
Id,
TableName,
RecordId, (link to the record in question)
ModifiedBy,
ModifiedOn,
Type (I, U or D)
)
Audit_Details_Table (
Id,
AuditId,
FieldName,
OldValue,
NewValue )
听起来你的路线是对的。但是，您通常不会有一个审计跟踪表，而是每个表都有一个审计表。因此，对于 TableA 中的行的每次修改，都会向 TableA_Audit 添加一个新行，其中包含 TableA 中的新状态以及日期和用户名称。
触发器通常用于此目的，但如果您要存储网络应用程序的用户名，我不知道如何将此数据传递到触发器中（其他人可以帮忙吗？）在这种情况下，我可能会想使用存储的程序。对于每个表，都有存储过程来插入、更新和删除行。这些存储过程将各自调用另一个存储过程，该存储过程将行插入审计表中。这样，您可以轻松地将 Web 应用程序用户名传递给存储过程，该存储过程将该行插入审计表中。显然，缺点是必须为每个表维护一堆存储过程，这可能有点乏味，因为您必须确保它们全部与表（以及应用程序的数据访问层）保持同步，因为不可避免地需要架构更改。
请注意，您不需要在每个表中都有用户名列，只需在每个审核表中都需要。
希望其中一些有用。
干杯
大卫
It sounds like you're on the right lines. However, you would generally not have a single audit trail table, but rather an audit table for every table. Thus, for every modification to a row in TableA, a new row is added to TableA_Audit containing the new state in TableA, plus the date and the name of the user.
A trigger is normally used for this, but if you're storing the web app's username, I don't know how to pass this data into a trigger (can anyone else help?) In this case, I might be tempted to use stored procedures. For each table, have stored procedures to insert, update and delete rows. These stored procedures would each call another stored procedure which inserts the row into the audit table. This way, you easily pass in the web app username to the stored procedure which inserts the row into the audit table. Obviously the downside is to have to maintain a bunch of stored procedures for each table, which can be a bit tedious as you have to ensure they all keep in step with the tables (and the application's data access layer) as schema changes are inevitably required.
Note that you don't need a Username column in every table, only in every audit table.
Hope some of that was of use.
Cheers
David
我同意其他海报的观点。底线是，如果您想存储网络应用程序用户的用户名（即您的自定义身份验证），那么触发器将无法帮助您审核正在发生的情况。 - 警告，除非您可以使用集成身份验证
如果您还想使用审计跟踪来监视用户的活动量，那么这一点非常重要。解决方案是通过存储过程执行所有 DDL，并在这些存储过程中添加审核逻辑（如果您希望所有日志记录都用 T-SQL 编写）。或者，从应用程序中执行此操作，并查看可用于 ASP.Net 的众多日志记录库之一，例如 NLog 。
I agree with both the other posters. Bottom line is that if you want to store your web app user's username (ie. your custom authentication) then triggers are NOT going to help you audit what's going on. - Caveat unless you can use Integrated Authentication
This is really important if you want to also use the audit trails for monitoring volumes of activity by user for example. The solution to this is to either perform all DDL via Stored Procedures and within those stored procedures add in your audit logic (if you want all of the logging written in T-SQL). Alternatively do it from the application and look at one of the many logging libraries available for ASP.Net such as NLog.