当前位置：文江博客话题详情

如何为数据库中的所有表设置数据库审计规范

发布于 2024-09-26 23:52:24 字数 111 浏览 1 评论 0原文

我需要创建一个审计来跟踪数据库中所有表的所有 CRUD 事件，现在我在数据库中有超过 100 个表，有没有办法创建包含数据库中所有表的规范？

PS：我使用的是SQL Server 2008

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

舟遥客 2024-10-03 23:52:24

我也有同样的问题。答案实际上比预期的更简单，并且不需要自定义 C# 应用程序来生成大量 SQL 来覆盖所有表。下面的 SQL 示例。重要的一点是为 INSERT/UPDATE/DELETE 指定数据库和公共。

USE [master]
GO

CREATE SERVER AUDIT [CancerStatsAudit]
TO FILE 
(   FILEPATH = N'I:\CancerStats\Audit\'
    ,MAXSIZE = 128 MB
    ,MAX_ROLLOVER_FILES = 64
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '5a0a18cf-fe42-4171-ad01-5e19af9e27d1'
)
ALTER SERVER AUDIT [CancerStatsAudit] WITH (STATE = ON)
GO

USE [CancerStats]
GO

CREATE DATABASE AUDIT SPECIFICATION [CancerStatsDBAudit]
FOR SERVER AUDIT [CancerStatsAudit]
ADD (INSERT ON DATABASE::[CancerStats] BY [public]),
ADD (UPDATE ON DATABASE::[CancerStats] BY [public]),
ADD (DELETE ON DATABASE::[CancerStats] BY [public]),
ADD (EXECUTE ON DATABASE::[CancerStats] BY [public]),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

注意：审核 INSERT、UPDATE 和 DELETE 不需要 DATABASE_OBJECT_CHANGE_GROUP 和 SCHEMA_OBJECT_CHANGE_GROUP - 请参阅下面的附加说明。

附加说明：

上面的示例还包括 DATABASE_OBJECT_CHANGE_GROUP 和 SCHEMA_OBJECT_CHANGE_GROUP。包含这些内容是因为我的要求还包括跟踪数据库对象上的 CREATE/ALTER/DROP 操作。值得注意的是，这些文档是错误的。
https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

上面的页面状态 DATABASE_OBJECT_CHANGE_GROUP 跟踪 CREATE、UPDATE并删除。这是不正确的（我在 SQL Server 2016 中测试过），仅跟踪 CREATE，请参阅：
https://connect.microsoft.com/SQLServer/feedback/details/370103/database-object-change-group-audit-group-does-not-audit-drop-proc

其实就是为了跟踪创建、更新、删除、使用 SCHEMA_OBJECT_CHANGE_GROUP。尽管上面的 learn.microsoft.com 文档页面表明这只适用于架构，但它实际上也适用于架构内的对象。

I had the same question. The answer is actually simpler than expected and doesn't need a custom C# app to generate lots of SQL to cover all the tables. Example SQL below. The important point was to specify database and public for INSERT/UPDATE/DELETE.

USE [master]
GO

CREATE SERVER AUDIT [CancerStatsAudit]
TO FILE 
(   FILEPATH = N'I:\CancerStats\Audit\'
    ,MAXSIZE = 128 MB
    ,MAX_ROLLOVER_FILES = 64
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    ,AUDIT_GUID = '5a0a18cf-fe42-4171-ad01-5e19af9e27d1'
)
ALTER SERVER AUDIT [CancerStatsAudit] WITH (STATE = ON)
GO

USE [CancerStats]
GO

CREATE DATABASE AUDIT SPECIFICATION [CancerStatsDBAudit]
FOR SERVER AUDIT [CancerStatsAudit]
ADD (INSERT ON DATABASE::[CancerStats] BY [public]),
ADD (UPDATE ON DATABASE::[CancerStats] BY [public]),
ADD (DELETE ON DATABASE::[CancerStats] BY [public]),
ADD (EXECUTE ON DATABASE::[CancerStats] BY [public]),
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

NB: DATABASE_OBJECT_CHANGE_GROUP and SCHEMA_OBJECT_CHANGE_GROUP are not needed for auditing INSERT, UPDATE and DELETE - see additional notes below.

Additional notes:

The example above also includes the DATABASE_OBJECT_CHANGE_GROUP and the SCHEMA_OBJECT_CHANGE_GROUP. These were included since my requirement was to also track CREATE/ALTER/DROP actions on database objects. It is worth noting that the documentation is wrong for these.
https://learn.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions

The above page states DATABASE_OBJECT_CHANGE_GROUP tracks CREATE, UPDATE and DELETE. This is not true (I've tested in SQL Server 2016), only CREATE is tracked, see:
https://connect.microsoft.com/SQLServer/feedback/details/370103/database-object-change-group-audit-group-does-not-audit-drop-proc

In fact, to track CREATE, UPDATE, DELETE, use SCHEMA_OBJECT_CHANGE_GROUP. Despite the above learn.microsoft.com documentation page suggesting this only works for schemas, it actually works for objects within the schema as well.

回复收藏 0 原文