MS SQL數(shù)據(jù)庫(kù)的DDL操作：創(chuàng)建，修改，刪除表(或存儲(chǔ)過(guò)程)等監(jiān)控方案

K谷appv2.0 官方安卓版

• 類型：社交聊天大�。�13.5M語(yǔ)言：中文 評(píng)分：4.4
• 標(biāo)簽：

立即下載

有時(shí)候，一個(gè)數(shù)據(jù)庫(kù)有多個(gè)帳號(hào)，包括數(shù)據(jù)庫(kù)管理員，開(kāi)發(fā)人員，運(yùn)維支撐人員等，可能有很多帳號(hào)都有比較大的權(quán)限，例如DDL操作權(quán)限(創(chuàng)建，修改，刪除存儲(chǔ)過(guò)程，創(chuàng)建，修改，刪除表等），賬戶多了，管理起來(lái)就會(huì)相當(dāng)麻煩，容易產(chǎn)生混亂，如果數(shù)據(jù)庫(kù)管理員不監(jiān)控?cái)?shù)據(jù)庫(kù)架構(gòu)變更的話，就不知道誰(shuí)對(duì)數(shù)據(jù)庫(kù)架構(gòu)做了啥改動(dòng)（此處改動(dòng)僅僅只DDL操作），尤其有時(shí)候，有些開(kāi)發(fā)人員可能不按規(guī)章制度辦事，繞過(guò)或忘了通知發(fā)布人員或DBA，直接去生產(chǎn)機(jī)做一些DDL操作，那么我們就需要對(duì)數(shù)據(jù)庫(kù)架構(gòu)某些更改的事件進(jìn)行監(jiān)控，如果能夠監(jiān)控并留下證據(jù)，這樣既可以讓DBA或相關(guān)管理人員知曉這些變更，有效管理數(shù)據(jù)庫(kù)，也可以避免出現(xiàn)問(wèn)題，出現(xiàn)扯皮現(xiàn)象，最后DBA成了背黑鍋的。

下面就是一個(gè)解決上述問(wèn)題的方案，我們通過(guò)創(chuàng)建一個(gè)表DatabaseLog和DDL觸發(fā)器來(lái)解決問(wèn)題，首先在msdb數(shù)據(jù)庫(kù)里面新建一個(gè)表DatabaseLog，用來(lái)保存DDL觸發(fā)器獲取的信息。其中DDL觸發(fā)器主要通過(guò)EVENTDATA()函數(shù)返回有關(guān)服務(wù)器或數(shù)據(jù)庫(kù)事件的信息。

SQL Code 1

USE msdb;

GO

CREATE TABLE [dbo].[DatabaseLog]

(

    [DatabaseLogID]   [int]    IDENTITY(1,1) NOT NULL,

    [PostTime]        [datetime] NOT NULL,

    [DatabaseUser]    [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [LoginName]       [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ClientHost]      [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Event]           [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [Schema]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [Object]          [sysname] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [TSQL]            [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,

    [XmlEvent]        [xml] NOT NULL,

CONSTRAINT [PK_DatabaseLog_DatabaseLogID] PRIMARY KEY NONCLUSTERED

(

    [DatabaseLogID] ASC

  )WITH (PAD_INDEX= OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

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'

GO

例如，我要監(jiān)控?cái)?shù)據(jù)庫(kù)MyAssistant的DDL操作，那么我們首先在“數(shù)據(jù)庫(kù)郵件”里面創(chuàng)建一個(gè)配置名為“ DataBase_DDL_Event”的配置文件（profile name）,這個(gè)就不多講了，不知道配置的，自己先練練手把，假如我需要讓數(shù)據(jù)庫(kù)把監(jiān)控到DDL操作變動(dòng)相信信息發(fā)送到我的郵箱 *****@***.com(用你自己的郵箱替代)，那么只需要修改下面代碼的郵箱和profile_name即可。

SQL Code 2

USE MyAssistant;

GO

CREATE TRIGGER [DTG_DatabaseDdlTriggerLog]

ON DATABASE

FOR DDL_DATABASE_LEVEL_EVENTS

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @data XML;

    DECLARE @schema sysname;

    DECLARE @object sysname;

    DECLARE @eventType sysname;

    DECLARE @tableHTML  NVARCHAR(MAX) ;

    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 [msdb].[dbo].[DatabaseLog]

        (

        [PostTime],

        [DatabaseUser],

        [LoginName],

        [ClientHost],

        [Event],

        [Schema],

        [Object],

        [TSQL],

        [XmlEvent]

        )

    VALUES

        (

        GETDATE(),

        CONVERT(sysname, CURRENT_USER),

        @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(max)'),

        CONVERT(sysname, HOST_NAME()),

        @eventType,

        CONVERT(sysname, @schema),

        CONVERT(sysname, @object),

        @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)'),

        @data

        );

    SET @tableHTML =   

    N'<H1>DDL Event</H1>' +     

    N'<table border="0">' +     

    N'<tr><th>Post Time</th><th>User</th><th>Login</th><th>ClientHost</th>' +     

    N'<th>TSQL</th><th></tr>' +     

    CAST(( SELECT

    td = PostTime,       '',                     

    td = DatabaseUser, '',        

    td = LoginName, '',     

    td = ClientHost, '',         

    td = TSQL, ''               

    FROM msdb.dbo.DatabaseLog               

    WHERE DatabaseLogID =(select max(DatabaseLogID) from msdb.dbo.DatabaseLog)               

    FOR XML PATH('tr'), TYPE     ) AS NVARCHAR(MAX) ) +     N'</table>' ;

    EXEC msdb.dbo.sp_send_dbmail     

             @profile_name = 'DataBase_DDL_Event',

        @recipients='***@***.com',     

        @subject = 'DDL Event - DataBase MyAssistant',     

        @body = @tableHTML,   

        @body_format = 'HTML' ;

END;

GO

接下來(lái)我們來(lái)測(cè)試一下，假如一個(gè)用戶Test登錄數(shù)據(jù)庫(kù)，一不小心刪除了一個(gè)Test的表，如下圖一所示，那么我將收到一封郵件，提示我用戶Test在那臺(tái)客戶端主機(jī)執(zhí)行了啥DDL操作(如下圖二所示)，當(dāng)然郵件的樣式、排版有興趣的可以去美化一下。