SQL Server模式审计？

Question

我们有一个SQL Server 2008企业版数据库，其中包含两个不同的模式：一个是我们维护的锁定模式，另一个是我们允许外部开发团队添加和修改以满足其自身需求的开放模式。通常情况下，这对我们来说还可以，但是有一个特别的团队喜欢把它搞得一团糟，这影响了其他所有人。因此有两个问题：

1. 事后看来，我希望我们从一开始就建立了一个强大的系统，但我们没有，只有默认安装。即使只是简单地记录“用户XYZ在2012年7月12日上午9:00更改了ABC过程”，是否有内置于SQL Server并默认启用的跟踪功能，我们可以利用它，如果有的话在哪里/如何使用？
2. 就长期解决方案而言，您会推荐什么？我已经稍微研究了一下DDL触发器，这似乎是一个有前途的选项。如果您使用过这种方法，能否分享一下它的工作原理以及您可以做些什么？

谢谢。

- snappymcsnap

---

针对http://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/，此文章介绍了如何使用SQL Server DDL触发器来跟踪所有数据库更改。 - Aaron Bertrand

Answer 1

我有一个系统，使用DDL触发器来处理这种情况。对于我的需求来说，它足够好用了。它最初是在Sql Server 2005上开发的，现在运行在Sql Server 2008R2系统上。它与Aaron Bertrand评论中提到的链接所描述的那个很相似。

创建一个类似于这个的表格。

CREATE TABLE [dbo].[SchemaLog](
    [SchemaLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTimeUtc] [datetime] NOT NULL,
    [DatabaseUser] [nvarchar](128) NOT NULL,
    [Event] [nvarchar](128) NOT NULL,
    [Schema] [nvarchar](128) NULL,
    [Object] [nvarchar](128) NULL,
    [TSQL] [nvarchar](max) NOT NULL,
    [XmlEvent] [xml] NOT NULL,
 CONSTRAINT [PK_SchemaLog_1] PRIMARY KEY CLUSTERED 
(
    [SchemaLogID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

请确保每个人都有该表的插入权限，然后创建一个类似以下示例的DDL触发器。

CREATE TRIGGER [ddlDatabaseTriggerLog] ON DATABASE  FOR DDL_DATABASE_LEVEL_EVENTS AS  
BEGIN     
    SET NOCOUNT ON;     
    DECLARE @data XML;     
    DECLARE @schema sysname;     
    DECLARE @object sysname;     
    DECLARE @eventType sysname;     
    SET @data = EVENTDATA();     
    SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');     
    SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');     
    SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')      
    IF @object IS NOT NULL         
        PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;     
    ELSE         
        PRINT '  ' + @eventType + ' - ' + @schema;     

    IF @eventType IS NULL         
        PRINT CONVERT(nvarchar(max), @data);     

    INSERT [dbo].[SchemaLog]          (         
        [PostTimeUtc]
    ,          [DatabaseUser]
    ,          [Event]
    ,          [Schema]
    ,          [Object]
    ,          [TSQL]
    ,          [XmlEvent]         )      
    VALUES          (         
        GETUTCDATE()
    ,          CONVERT(sysname, CURRENT_USER)
    ,          @eventType
    ,          CONVERT(sysname, @schema)
    ,          CONVERT(sysname, @object)
    ,          @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
    ,          @data         ); 

END;

选择更改顺序的视图

create view SchemaLogOrdered
as
SELECT top 10000 *
FROM            dbo.SchemaLog
ORDER BY  SchemaLogID DESC

- JSR

---

有没有办法通过这个获取计算机名或主机名？我期望从一个通用的SQL登录中获得更新，因此我们将无法确定真正的来源。我猜想这是不可能的，因为它没有随XML数据一起发送。 - Bret

Answer 2

以下是Redgate选项。

1）最简单的方法是安装DLM Dashboard开始审计模式更改。这是一个免费工具，使用DDL触发器警报和记录所有更改，并包括您要求的信息。

2）如Andy Davies所提到的，正确的方法是以与应用程序代码相同的方式开始源控制模式。完成后，您可以通过将数据库纳入持续集成和发布管理实践来提高数据库生命周期管理成熟度。

- David Atkinson

Answer 3

对于第二个问题，您可以考虑使用触发器。在以下示例中，使用SQL Server的EVENTDATA()函数捕获了触发器触发的事件信息。该SQL脚本创建了一个DDL触发器，用于捕获数据库级别上的CREATE、ALTER和DROP事件（尽管可以在服务器级别上创建触发器，以捕获服务器上所有数据库的事件；应使用ON ALL SERVER选项，而不是ON DATABASE）。

CREATE TRIGGER Audit_DDL ON DATABASE
FOR CREATE_TABLE , ALTER_TABLE , DROP_TABLE
AS
DECLARE
@event xml;
SET @event = EVENTDATA(
                  );
INSERT INTO Audit_DDL_Events
VALUES( REPLACE( CONVERT( varchar( 50
                             ) , @event.query( 'data(/EVENT_INSTANCE/PostTime)'
                                             )
                    ) , 'T' , ' '
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/LoginName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/UserName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/DatabaseName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/SchemaName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/ObjectName)'
                                    )
           ) , 
    CONVERT( varchar( 150
                    ) , @event.query( 'data(/EVENT_INSTANCE/ObjectType)'
                                    )
           ) , 
    CONVERT( varchar( max
                    ) , @event.query( 'data(/EVENT_INSTANCE/TSQLCommand/CommandText)'
                                    )
           )
  );

还需要创建一个适当的存储表来存储EVENTDATA XML中的审计数据：

CREATE TABLE Audit_DDL_Events( DDL_Event_Time datetime , 
                           DDL_Login_Name varchar( 150
                                                 ) , 
                           DDL_User_Name varchar( 150
                                                ) , 
                           DDL_Database_Name varchar( 150
                                                    ) , 
                           DDL_Schema_Name varchar( 150
                                                  ) , 
                           DDL_Object_Name varchar( 150
                                                  ) , 
                           DDL_Object_Type varchar( 150
                                                  ) , 
                           DDL_Command varchar( max
                                              )
                         );

- Marko Krstic

Answer 4

你可以考虑将你的数据库放入源代码控制中。也许让每个外部团队都从你的数据库中获取一个分支或叉。这样可以通过提交来审计更改，并能够选择要合并和/或审核/编辑这些更改后再合并。

可以看看 http://www.red-gate.com/products/sql-development/sql-source-control/ 这个可用的产品。

结合像 Github 或 Bitbucket 这样的解决方案，可以通过互联网更直接地向外部贡献者开放你的源代码控制库。

- Andy Davies