代码改变世界

MS SQL监控数据库的DDL操作

2013-04-27 23:35 潇湘隐者 阅读(...) 评论(...) 编辑 收藏

    前言: 有时候,一个数据库有多个帐号,包括数据库管理员,开发人员,运维支撑人员等,可能有很多帐号都有比较大的权限,例如DDL操作权限(创建,修改,删除存储过程,创建,修改,删除表等),账户多了,管理起来就会相当麻烦,容易产生混乱,如果数据库管理员不监控数据库架构变更的话,就不知道谁对数据库架构做了啥改动(此处改动仅仅只DDL操作),尤其有时候,有些开发人员可能不按规章制度办事,绕过或忘了通知发布人员或DBA,直接去生产机做一些DDL操作,那么我们就需要对数据库架构某些更改的事件进行监控,如果能够监控并留下证据,这样既可以让DBA或相关管理人员知晓这些变更,有效管理数据库,也可以避免出现问题,出现扯皮现象,最后DBA成了背黑锅的。

  下面就是一个解决上述问题的方案,我们通过创建一个表DatabaseLog和DDL触发器来解决问题,首先在msdb数据库里面新建一个表DatabaseLog,用来保存DDL触发器获取的信息。其中DDL触发器主要通过EVENTDATA()函数返回有关服务器或数据库事件的信息。

SQL Code 1
  1. USE msdb;
  2.  
  3. GO
  4.  
  5.  
  6. CREATE TABLE [dbo].[DatabaseLog]
  7.  
  8. (
  9.  
  10.     [DatabaseLogID]   [int]    IDENTITY(1,1) NOT NULL,
  11.  
  12.     [PostTime]        [datetime] NOT NULL,
  13.  
  14.     [DatabaseUser]    [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  15.  
  16.     [LoginName]       [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  17.  
  18.     [ClientHost]      [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  19.  
  20.     [Event]           [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  21.  
  22.     [Schema]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  23.  
  24.     [Object]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  25.  
  26.     [TSQL]            [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  27.  
  28.     [XmlEvent]        [xml] NOT NULL,
  29.  
  30. CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED
  31.  
  32. (
  33.  
  34.     [DatabaseLogID] ASC
  35.  
  36.   )WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
  37.  
  38. ) ON [PRIMARY]
  39.  
  40.  
  41. GO
  42.  
  43. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key for DatabaseLog records.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseLogID'
  44.  
  45. GO
  46.  
  47. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The date and time the DDL change occurred.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'PostTime'
  48.  
  49. GO
  50.  
  51. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The user who implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'DatabaseUser'
  52.  
  53. GO
  54.  
  55. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The login which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'LoginName'
  56.  
  57. GO
  58.  
  59. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The client machine on which implemented the DDL change.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'ClientHost'
  60.  
  61. GO
  62.  
  63. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The type of DDL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Event'
  64.  
  65. GO
  66.  
  67. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The schema to which the changed object belongs.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Schema'
  68.  
  69. GO
  70.  
  71. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The object that was changed by the DDL statment.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'Object'
  72.  
  73. GO
  74.  
  75. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The exact Transact-SQL statement that was executed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'TSQL'
  76.  
  77. GO
  78.  
  79. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'The raw XML data generated by database trigger.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'COLUMN',@level2name=N'XmlEvent'
  80.  
  81. GO
  82.  
  83. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Audit table tracking all DDL changes made to the database. Data is captured by the database trigger ddlDatabaseTriggerLog.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog'
  84.  
  85. GO
  86.  
  87. EXEC sys.sp_addextendedproperty@name=N'MS_Description', @value=N'Primary key (nonclustered) constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DatabaseLog', @level2type=N'CONSTRAINT',@level2name=N'PK_DatabaseLog_DatabaseLogID'
  88.  
  89.  
  90. GO

例如,我要监控数据库MyAssistant的DDL操作,那么我们首先在“数据库邮件”里面创建一个配置名为“ DataBase_DDL_Event”的配置文件(profile name),这个就不多讲了,不知道配置的,自己先练练手把,假如我需要让数据库把监控到DDL操作变动相信信息发送到我的邮箱 *****@***.com(用你自己的邮箱替代),那么只需要修改下面代码的邮箱和profile_name即可。

SQL Code 2
  1. USE MyAssistant;
  2. GO
  3.  
  4. CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]
  5. ON DATABASE
  6. FOR DDL_DATABASE_LEVEL_EVENTS
  7. AS
  8. BEGIN
  9.     SET NOCOUNT ON;
  10.  
  11.     DECLARE @data XML;
  12.     DECLARE @schema sysname;
  13.     DECLARE @object sysname;
  14.     DECLARE @eventType sysname;
  15.     DECLARE @tableHTML  NVARCHAR(MAX) ;
  16.     
  17.     SET @data = EVENTDATA();
  18.     SET @eventType = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
  19.     SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
  20.     SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname')
  21.  
  22.     IF @object IS NOT NULL
  23.         PRINT '  ' + @eventType + ' - ' + @schema + '.' + @object;
  24.     ELSE
  25.         PRINT '  ' + @eventType + ' - ' + @schema;
  26.  
  27.     IF @eventType IS NULL
  28.         PRINT CONVERT(nvarchar(max), @data);
  29.  
  30.     INSERT [msdb].[dbo].[DatabaseLog]
  31.         (
  32.         [PostTime],
  33.         [DatabaseUser],
  34.         [LoginName],
  35.         [ClientHost],
  36.         [Event],
  37.         [Schema],
  38.         [Object],
  39.         [TSQL],
  40.         [XmlEvent]
  41.         )
  42.     VALUES
  43.         (
  44.         GETDATE(),
  45.         CONVERT(sysname, CURRENT_USER),
  46.         @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'),
  47.         CONVERT(sysname, HOST_NAME()),
  48.         @eventType,
  49.         CONVERT(sysname, @schema),
  50.         CONVERT(sysname, @object),
  51.         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
  52.         @data
  53.         );
  54.  
  55.     SET @tableHTML =   
  56.     N'<H1>DDL Event</H1>' +     
  57.     N'<table border="0">' +     
  58.     N'<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>' +     
  59.     N'<th>TSQL</th><th></tr>' +     
  60.     CAST(( SELECT
  61.     td = PostTime,       '',                     
  62.     td = DatabaseUser, '',        
  63.     td = LoginName, '',     
  64.     td = ClientHost, '',         
  65.     td = TSQL, ''               
  66.     FROM msdb.dbo.DatabaseLog               
  67.     WHERE DatabaseLogID =(select max(DatabaseLogID) from msdb.dbo.DatabaseLog)               
  68.     FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ;
  69.  
  70.     EXEC msdb.dbo.sp_send_dbmail     
  71.              @profile_name = 'DataBase_DDL_Event',
  72.         @recipients='***@***.com',     
  73.         @subject = 'DDL Event - DataBase MyAssistant',     
  74.         @body = @tableHTML,   
  75.         @body_format = 'HTML' ;
  76. END;
  77.  
  78.  
  79.  
  80. GO

接下来我们来测试一下,假如一个用户Test登录数据库,一不小心删除了一个Test的表,如下图一所示,那么我将收到一封邮件,提示我用户Test在那台客户端主机执行了啥DDL操作(如下图二所示),当然邮件的样式、排版有兴趣的可以去美化一下。

 

clipboard

clipboard[1]