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

概述一 .Server Trigger的简单介绍 在SQL Server数据库中，Server Trigger 是一种特殊类型的存储过程，它可以对特定表、视图或存储中的必然事件自动响应，不由用户调用。创建 一 .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,ServernameDBnameObjnameObjtypeCreatorActiveFlag10) TransDateTimedatetime] ) ON PRIMARY]GO

 

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

 

DBTrigger_Log20) 50) ObjectnameObjectTypeEventTypeHostname128) AppnameEventDataxmlFlagintNulL DEFAulT ((0))) ] TEXtimage_ON GOSET ANSI_padding OFFEXEC sys.sp_addextendedproperty @name=N'MS_Description',@value服务器IP@level0typeSCHEMA@level0name@level1typetable@level1name@level2typeColUMN@level2name'服务器名称数据库名称对象名称对象类型事件类型终端机器名名称触发事件xml发生时间是否上传GO

 

三. 创建Server Trigger的脚本

 

USE master]/****** Object:  DdlTrigger [ServerDBTrigger_ProtectObjects]    Script Date: 2018/12/27 13:36:00 ******/SET ANSI_NulLS ONSET QUOTED_IDENTIFIER GO###########################################################################################*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会报错。##############################################################################################TRIGGER ServerDBTrigger_DBA_ProtectObjectsON 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)    @Servername 50@Appname nvarchar(128@Hostname @DBname 100@Objectname @ObjectType @EventType @ObjectAction @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)'))    @EventData varchar(MAXSELECT @EventData=CONVERT(VARCHAR(MAX),1)">@DBname= SUBSTRING (@EventData,CHARINDEX(<Databasename>@EventData)+14,1)"></Databasename>--14@Objectname<Objectname>12,1)"></Objectname>12@ObjectType<ObjectType></ObjectType>@ObjectAction<EventType>11,1)"></EventType>11@EventTypeSUBSTRING(----------0003 end ---------------    @HostnameHOST_name(),1)">@AppnameAPP_name()    SELECT  @Servername = @@SERVERname    @ServerIP MIN(LOCAL_NET_ADDRESS) FROM SYS.DM_EXEC_CONNECTIONS WHERE LOCAL_NET_ADDRESS IS NOT NulL    IF EXISTS(top 1 Objname FROM Protected_Objects WITH(NolOCK) WHERE Servername @@SERVERname AND DBname@DBname AND Objname@Objectname AND ActiveFlagY'BEGIN        IF (liKE TMP%')OR (@ObjectAction liKE 'ALTER%' AND */[_]%)         BEGIN            INSERT  INTO  DBTrigger_Log                                ( ServerIP,Servername,DBname,Objectname,ObjectType,EventType,Hostname,Appname,TransDateTime)                        VALUES  ( @ServerIP,1)">@Servername,1)">@DBname,1)">@Objectname,1)">@ObjectType,1)">@EventType,1)">@Hostname,1)">@Appname,1)">GETDATE())         END        ELSE          BEGIN                      ---------------0001 start ---            ())                        Rollback TRANSACTION            ---------------end --------            -----------0002 begin ----------------                          @Subject AS 200)            @Body nvarchar(@SPname )                        @Subject ServerDBTrigger-重要!;ServerIP:' + @ServerIP                        @SPname ''                        @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>'                         @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>'                        <tr bgcolor=#ffaa11><td>'CAST(NVARCHAR(50))</td><td>50)) +20))SUBSTRING(REPLACE(500)),1)">CHAR(0),1)">''),1,1); Font-weight: bold">500)100),1)">GETDATE(),1); Font-weight: bold">21)</td></tr></table>'            @BODYREPLACE(@BODY,1)">'''',1)">'')            IF ' '')<>''                BEGIN                    @AllEmailToAddress 3000)''                    @AllEmailCcAddress @Allprofile_name 100)@AllEmailToAddress收件人的地址'                    @AllEmailCcAddress抄送人的地址'                    1 @Allprofile_name=name FROM msdb.dbo.sysmail_profile ORDER BY profile_ID                    EXEC msdb..sp_send_dbmail @profile_name @Allprofile_name      profile 名称 ,1)">@recipIEnts   =  @AllEmailToAddress          收件人邮箱 ,1)">@copy_recipIEnts@AllEmailCcAddress@subject      @Subject                    邮件标题 ,1)">@body         @BODY                       邮件内容 ,1)">@body_format  =  HTML'                      邮件格式 ,1)">@file_attachmentsEND                   -----------    0002 end ------------                     END                END    --新建对象自动塞入保护表    ELSE     PRINT 3         DELETE @Objectname        IF @ObjectAction Create%AND @Appname %Microsoft sql Server Management Studio%'             NOT '        BEGIN              Protected_Objects                        () )            ())        END         ELSE IF  '  '            ())        END     END TRY CATCH    PRINT @Objectname:+@Objectname    @ObjectType:@ObjectType    PRINT ERROR_MESSAGE()    RolLBACK TRANSACTION CATCHOFFGOENABLE ServerDBTrigger_ProtectObjectsGO

 

四. 补充

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

INSERT  Protected_Objects(DBname,Objname,ObjType,Creator,ActiveFlag,TransDateTime)SELECT XXXXXXCASE xtype WHEN U' THEN tablePProcedureFNFunctionTFVVIEwVARCHAR(10),1)">DATEADD(DAY,1); Font-weight: bold">46,1)">GETDATE()),1); Font-weight: bold">120)FROM XXXXXXX].dbo.sysobjects WHERE xtype IN ()      AND  name '     AND name unuse%'

 

总结

以上是内存溢出为你收集整理的使用Server Trigger保护重要的数据库对象全部内容，希望文章能够帮你解决使用Server Trigger保护重要的数据库对象所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。