使用Server Trigger保护重要的数据库对象

2019-01-02 14:16:04来源:cnblogs.com作者:东山絮柳仔人点击

分享

一 .Server Trigger的简单介绍

在SQL Server数据库中,Server Trigger 是一种特殊类型的存储过程它可以对特定表、视图或存储中的必然事件自动响应,不由用户调用。创建触发器时对其进行定义,以便在对特定的数据库对象作特定类型的修改时执行,根据触发器定义的动作做出反应。

其主要被用在保持数据库对象的完整性方面。例如,防止数据库中已建好的表和存储过程被更改或删除。此外还可以 进行更改历史记录的追踪,查看表或存储被修改的记录。

Server Trigger比Database Trigger所管控的范围更广,可以管控Server下的所有Database的对象。

二. 主要表 及创建脚本

表Protected_Objects ,主要用来存储被保护的数据库对象,例如 表和存储过程。 字段 ActiveFlag设置为Y时有效,N是无效。

有新的数据库对象创建,最自动Insert一笔数据。

CREATE TABLE [dbo].[Protected_Objects](    [ServerIP] [varchar](100) NULL,    [ServerName] [varchar](100) NULL,    [DBName] [varchar](100) NULL,    [ObjName] [varchar](100) NULL,    [Objtype] [varchar](100) NULL,    [Creator] [varchar](100) NULL,    [ActiveFlag] [varchar](10) NULL,    [TransDateTime] [datetime] NULL) ON [PRIMARY]GO

表DBTrigger_Log,主要存储数据库对象变动记录。

CREATE TABLE [dbo].[DBTrigger_Log](    [ServerIP] [varchar](20) NULL,    [ServerName] [varchar](50) NULL,    [DBName] [varchar](100) NULL,    [ObjectName] [varchar](100) NULL,    [ObjectType] [varchar](100) NULL,    [EventType] [varchar](100) NULL,    [HostName] [varchar](128) NULL,    [AppName] [varchar](128) NULL,    [EventData] [xml] NULL,    [TransDateTime] [datetime] NULL,    [Flag] [int] NULL DEFAULT ((0))) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOSET ANSI_PADDING OFFGOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'ServerIP'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'服务器名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'ServerName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'数据库名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'DBName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对象名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'ObjectName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'对象类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'ObjectType'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'事件类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'EventType'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'终端机器名' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'HostName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'名称' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'AppName'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'触发事件xml' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'EventData'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发生时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'TransDateTime'GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否上传' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'DBTrigger_Log', @level2type=N'COLUMN',@level2name=N'Flag'GO

三. 创建Server Trigger的脚本

USE [master]GO/****** Object:  DdlTrigger [ServerDBTrigger_ProtectObjects]    Script Date: 2018/12/27 13:36:00 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/*###########################################################################################*Program*:            <DB Trigger>*Description*:        <Protect SQL key objects>*programer*:         <>*Date*:             2015-12-03---0001  2015-12-03  11:12      第一阶段期间只保留修改记录,暂时不阻止(不RollBack) ---0002  2015-12-03  15:32      增加发邮件的功能.  ---0003  2015-12-04  14:20      出现set ANSI_PADDING Off后,还有(如果是script出来的表,其中有索引约束等,---                             需要Alter表时,就会报错。)代码时,@xEvent.query会报错。##############################################################################################*/CREATE TRIGGER [ServerDBTrigger_DBA_ProtectObjects]ON ALL SERVERFOR DROP_TABLE,DROP_PROCEDURE,DROP_VIEW,DROP_FUNCTION, CREATE_TABLE,CREATE_PROCEDURE,CREATE_VIEW,CREATE_FUNCTION,ALTER_PROCEDURE,ALTER_VIEW,ALTER_TABLE,ALTER_FUNCTION,RENAMEAS  SET NOCOUNT ON ;  BEGIN TRY    DECLARE @ServerIP varchar(20)    DECLARE @ServerName varchar(50)    DECLARE @AppName nvarchar(128)    DECLARE @HostName nvarchar(128)    DECLARE @DBName varchar(100)    DECLARE @ObjectName varchar(100)    DECLARE @ObjectType varchar(100)    DECLARE @EventType varchar(100)    DECLARE @ObjectAction varchar(100)    DECLARE @xEvent XML    SET @xEvent = EVENTDATA()    ----------------------0003 start ----------    --Set @DBName=CONVERT(VARCHAR(100),@xEvent.query('data(/EVENT_INSTANCE/DatabaseName)'))    --Set @ObjectName=CONVERT(VARCHAR(100),@xEvent.query('data(/EVENT_INSTANCE/ObjectName)'))    --Set @ObjectType=CONVERT(VARCHAR(100),@xEvent.query('data(/EVENT_INSTANCE/ObjectType)'))    --Set @ObjectAction=CONVERT(VARCHAR(100),@xEvent.query('data(/EVENT_INSTANCE/EventType)'))    DECLARE @EventData varchar(MAX)    SELECT @EventData=CONVERT(VARCHAR(MAX),@xEvent)    SET @DBName= SUBSTRING (@EventData, CHARINDEX('<DatabaseName>',@EventData)+14, CHARINDEX('</DatabaseName>',@EventData)-CHARINDEX('<DatabaseName>',@EventData)-14)    SET @ObjectName= SUBSTRING (@EventData, CHARINDEX('<ObjectName>',@EventData)+12, CHARINDEX('</ObjectName>',@EventData)-CHARINDEX('<ObjectName>',@EventData)-12)    SET @ObjectType= SUBSTRING (@EventData, CHARINDEX('<ObjectType>',@EventData)+12, CHARINDEX('</ObjectType>',@EventData)-CHARINDEX('<ObjectType>',@EventData)-12)    SET @ObjectAction= SUBSTRING (@EventData, CHARINDEX('<EventType>',@EventData)+11, CHARINDEX('</EventType>',@EventData)-CHARINDEX('<EventType>',@EventData)-11)    SET @EventType=SUBSTRING(@EventData, CHARINDEX('<EventType>',@EventData)+11, CHARINDEX('</EventType>',@EventData)-CHARINDEX('<EventType>',@EventData)-11)    ------------0003 end ---------------    SELECT @HostName=HOST_NAME(),@AppName=APP_NAME()    SELECT  @ServerName = @@SERVERNAME    SELECT  @ServerIP = MIN(LOCAL_NET_ADDRESS) FROM SYS.DM_EXEC_CONNECTIONS WHERE LOCAL_NET_ADDRESS IS NOT NULL    IF EXISTS(SELECT TOP 1 ObjName FROM Protected_Objects WITH(NOLOCK) WHERE ServerName = @@SERVERNAME AND DBName=@DBName AND ObjName=@ObjectName AND ActiveFlag='Y')    BEGIN        IF (@ObjectName LIKE 'TMP%')OR (/*@ObjectAction LIKE 'ALTER%' AND */@ObjectName LIKE '[_]%')         BEGIN            INSERT  INTO  DBTrigger_Log                                ( ServerIP ,ServerName ,DBName ,ObjectName ,ObjectType,EventType ,HostName , AppName ,[EventData] ,TransDateTime)                        VALUES  ( @ServerIP ,@ServerName , @DBName , @ObjectName ,@ObjectType ,@EventType,@HostName ,@AppName ,@EventData , GETDATE())         END        ELSE          BEGIN                      -----------------0001 start ---            INSERT  INTO  DBTrigger_Log                                ( ServerIP ,ServerName ,DBName ,ObjectName ,ObjectType,EventType ,HostName , AppName ,[EventData] ,TransDateTime)                        VALUES  ( @ServerIP ,@ServerName , @DBName , @ObjectName ,@ObjectType ,@EventType,@HostName ,@AppName ,@EventData , GETDATE())                        --Rollback TRANSACTION            -----------------end --------            -------------0002 begin ----------------                          DECLARE @Subject AS nvarchar(200)            DECLARE @Body AS nvarchar(MAX)            DECLARE @SPName AS nvarchar(MAX)                        SET @Subject = 'ServerDBTrigger-重要!;ServerIP:' + @ServerIP                        SET @SPName = ''                        SET @Body = '<html><body>Dear All,<br> <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ServerName:' + @ServerName+ ' ; ServerIP:' + @ServerIP+ '上的object已被改动,请及时检查!!!                       <br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;You can get detail information from DBA_DBTrigger_Log. <br><br><table border=1 bgcolor=#aaff11>'                         SET @Body = @Body+ '<tr bgcolor=#ffaa11><td>ServerName</td><td>ServerIP</td><td>DBName</td><td>EventType</td><td>ObjectName</td><td>ObjectType</td><td>HostName </td><td>TransDateTime</td></tr>'                        SELECT  @SPName = @SPName + '<tr bgcolor=#ffaa11><td>'+ CAST(@ServerName AS NVARCHAR(50))+ '</td><td>'+ CAST(@ServerIP AS NVARCHAR(50))+ '</td><td>' + CAST(@DBName AS NVARCHAR(50)) + '</td><td>'+ CAST(@EventType AS NVARCHAR(50))+ '</td><td>'+CAST(@ObjectName AS NVARCHAR(50))+ '</td><td>'+ CAST(@ObjectType AS NVARCHAR(20))+ '</td><td>'+ SUBSTRING(REPLACE(CAST(@HostName AS varchar(500)), CHAR(0), ''), 1, 500)+ '</td><td>'+ CONVERT(varchar(100), GETDATE(), 21)+ '</td></tr>'                        SET @Body = @Body + @SPName + '</table>'            SET @BODY=REPLACE(@BODY,'''','')            IF REPLACE(@BODY,' ','')<>''                BEGIN                    DECLARE @AllEmailToAddress varchar(3000)=''                    DECLARE @AllEmailCcAddress varchar(3000)=''                    DECLARE @Allprofile_name varchar(100)=''                    SELECT @AllEmailToAddress='收件人的地址'                    SELECT @AllEmailCcAddress='抄送人的地址'                    SELECT TOP 1 @Allprofile_name=NAME FROM msdb.dbo.sysmail_profile ORDER BY profile_id                    EXEC msdb..sp_send_dbmail @profile_name = @Allprofile_name     -- profile 名称                      ,@recipients   =  @AllEmailToAddress         -- 收件人邮箱                      ,@copy_recipients=@AllEmailCcAddress                     ,@subject      =  @Subject                   -- 邮件标题                      ,@body         =  @BODY                      -- 邮件内容                      ,@body_format  =  'HTML'                     -- 邮件格式                      ,@file_attachments=''                END                   -------------    0002 end ------------                     END                END    ----新建对象自动塞入保护表    ELSE     BEGIN        --PRINT 3         DELETE FROM Protected_Objects WHERE ServerName = @@SERVERNAME AND DBName=@DBName AND ObjName=@ObjectName        IF @ObjectAction LIKE 'Create%' AND @AppName LIKE '%Microsoft SQL Server Management Studio%'             AND @ObjectName NOT LIKE '[_]%' AND @ObjectName NOT LIKE 'TMP%'        BEGIN             INSERT  INTO Protected_Objects                        VALUES  ( @ServerIP, @ServerName, @DBName, @ObjectName,@ObjectType, @HostName, 'Y', GETDATE() )            INSERT  INTO  DBTrigger_Log                                ( ServerIP ,ServerName ,DBName ,ObjectName ,ObjectType,EventType ,HostName , AppName ,[EventData] ,TransDateTime)                        VALUES  ( @ServerIP ,@ServerName , @DBName , @ObjectName ,@ObjectType ,@EventType,@HostName ,@AppName ,@EventData , GETDATE())        END         ELSE IF  @ObjectAction NOT LIKE 'Create%' AND @AppName LIKE '%Microsoft SQL Server Management Studio%'  AND @ObjectName NOT LIKE '[_]%'            BEGIN            INSERT  INTO  DBTrigger_Log                                ( ServerIP ,ServerName ,DBName ,ObjectName ,ObjectType,EventType ,HostName , AppName ,[EventData] ,TransDateTime)                        VALUES  ( @ServerIP ,@ServerName , @DBName , @ObjectName ,@ObjectType ,@EventType,@HostName ,@AppName ,@EventData , GETDATE())        END    END     END TRYBEGIN CATCH    PRINT '@ObjectName:'+@ObjectName    PRINT '@ObjectType:'+@ObjectType    PRINT ERROR_MESSAGE()    ROLLBACK TRANSACTIONEND CATCHSET ANSI_NULLS OFFGOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOENABLE TRIGGER [ServerDBTrigger_ProtectObjects] ON ALL SERVERGO

四. 补充

创建Server Trigger 后,此时表Protected_Objects是空的,没有被保护的数据库对象。我们可以将数据库下面的对象批量插入。例如,我们将数据库XXXX下除 _和unuse开头之外的所有对象批量插入。其脚本如下:

INSERT INTO Protected_Objects(DBName,ObjName,ObjType,Creator,ActiveFlag,TransDateTime)SELECT 'XXXXXX',Name, CASE xtype WHEN 'U' THEN 'Table' WHEN 'P' THEN 'Procedure' WHEN 'FN' THEN 'Function' WHEN 'TF' THEN 'Function' WHEN 'V' THEN 'View' END,    HOST_NAME(),'Y' ,CONVERT(VARCHAR(10),DATEADD(DAY,-46,GETDATE()),120)FROM [XXXXXXX].dbo.sysobjects WHERE xtype IN ('U','P','FN','V','TF')      AND  name NOT LIKE '[_]%'     AND name NOT LIKE 'unuse%'

相关文章

    无相关信息

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台