监控数据库DDL操作日志

背景

为了监控好生产环境下各个数据库服务器上DDL操作日志，便于运维工程师管控好风险，我们有必要关注当前实例下的所有的DDL操作以及对应的IP和hostname。

测试环境

Microsoft SQL Server 2012 - 11.0.2218.0 (X64) 
Jun 12 2012 13:05:25 
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

操作步骤

第一步.在监控库中新建DDL监控表用来存放DDL监控日志记录
1 --新建监控库，如果已存在该数据库，可以不执行
2 USE master;
3 IF DB_ID('azure_monitor') IS NOT NULL
4     DROP DATABASE azure_monitor;
 1 CREATE DATABASE azure_monitor
 2 ON
 3 --请根据实际情况选择监控库的存放路径
 4 (   NAME = azure_monitor,
 5     FILENAME = 'd:\azure_monitor.mdf',
 6     FILEGROWTH = 50MB
 7 )
 8 LOG ON
 9 (   NAME = azure_monitor_log,
10     FILENAME = 'd:\azure_monitore_log.ldf',
11     FILEGROWTH = 50MB
12 );
1 USE master;
2 ALTER DATABASE azure_monitor SET RECOVERY SIMPLE;
 
 1 USE [azure_monitor];
 2 
 3 
 4 CREATE TABLE [dbo].[monitor_DatabaseLog]
 5 (
 6     [DatabaseLogID] [INT] IDENTITY(1, 1) NOT NULL,
 7     [PostTime] [DATETIME] NOT NULL,
 8     [DatabaseUser] [sysname] NOT NULL,
 9     [LoginName] [sysname] NOT NULL,
10     [Event] [sysname] NOT NULL,
11     [databasename] [sysname] NULL,
12     [Schema] [sysname] NULL,
13     [Object] [sysname] NULL,
14     [TSQL] [NVARCHAR](MAX) NOT NULL,
15     [XmlEvent] [XML] NOT NULL,
16     [IP] [NVARCHAR](32) NULL,
17     [hostname] [NVARCHAR](100) NULL,
18     CONSTRAINT [PK_DatabaseLog_DatabaseLogID]
19         PRIMARY KEY NONCLUSTERED ([DatabaseLogID] ASC)
20         WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
21                  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
22                  ALLOW_PAGE_LOCKS = ON
23              ) ON [PRIMARY]
24 ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY];
25 
26 GO

 

第二步.新建实例级别的触发器
 1 CREATE TRIGGER [ddlDatabaseTriggerLog]
 2 ON ALL SERVER
 3 WITH EXECUTE AS 'sa'  --根据实际情况选择
 4 FOR DDL_DATABASE_LEVEL_EVENTS, CREATE_DATABASE, DROP_DATABASE, ALTER_DATABASE, CREATE_LOGIN
 5 AS
 6 BEGIN
 7     SET NOCOUNT ON;
 8 
 9     DECLARE @data XML;
10     DECLARE @LoginName sysname;
11     DECLARE @databasename sysname;
12     DECLARE @schema sysname;
13     DECLARE @object sysname;
14     DECLARE @eventType sysname;
15     DECLARE @ip VARCHAR(32) =
16             (
17                 SELECT client_net_address
18                 FROM sys.dm_exec_connections
19                 WHERE session_id = @@SPID
20             );
21 
22     DECLARE @hostname NVARCHAR(100) = HOST_NAME();
23 
24     SET @data = EVENTDATA();
25     SET @LoginName
26         = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
27     SET @databasename
28         = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname');
29     SET @eventType
30         = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'sysname');
31     SET @schema = @data.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname');
32     SET @object = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname');
33 
34     IF @object IS NOT NULL
35         PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema + '.'
36               + @object;
37     ELSE
38         PRINT '  ' + @eventType + ' - ' + @databasename + '.' + @schema;
39 
40     IF @eventType IS NULL
41         PRINT CONVERT(NVARCHAR(MAX), @data);
42     ---检查写入的日志记录对应的库名是否正确
43     INSERT [azure_monitor].[dbo].[monitor_DatabaseLog]
44     (   [PostTime],
45         [DatabaseUser],
46         [LoginName],
47         [Event],
48         [databasename],
49         [Schema],
50         [Object],
51         [TSQL],
52         [XmlEvent],
53         [ip],
54         [hostname]
55     )
56     VALUES
57     (   GETDATE(),
58         CONVERT(sysname, CURRENT_USER),
59         CONVERT(sysname, @LoginName),
60         @eventType,
61         CONVERT(sysname, @databasename),
62         CONVERT(sysname, @schema),
63         CONVERT(sysname, @object),
64         @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),
65         @data,
66         @ip,
67         @hostname
68     );
69 END;

 

监控效果

 

后记

  1. 所有的人员登陆都已提前开设好各自的登陆用户；
  2. 严格隔离区分不同的人员之间操作权限；
  
---清空记录存储过程
CREATE PROCEDURE [dbo].[usp_PurgeOldData_databaselog] ( @PurgeWaits SMALLINT )
AS
    BEGIN;      
        IF @PurgeWaits IS NULL
            BEGIN;
                RAISERROR(N'Input parameters cannot be NULL', 16, 1);
                RETURN;
            END;
        DELETE  FROM [dbo].[monitor_DatabaseLog]
        WHERE   [PostTime] < GETDATE() - @PurgeWaits;
    END;

GO

 

参考

 

posted @ 2017-08-09 08:11  jil.wen  阅读(1979)  评论(0)  编辑  收藏  举报