使用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,
    [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]

go

set ansi_padding off
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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'
go

exec 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 on
go

set quoted_identifier on
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会报错。
##############################################################################################*/
create trigger [serverdbtrigger_dba_protectobjects]
on all server
for drop_table,drop_procedure,drop_view,drop_function, 
create_table,create_procedure,create_view,create_function,
alter_procedure,alter_view,alter_table,alter_function,rename

as
  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 try
begin catch
    print '@objectname:'+@objectname
    print '@objecttype:'+@objecttype
    print error_message()
    rollback transaction
end catch

set ansi_nulls off


go

set ansi_nulls off
go

set quoted_identifier off
go

enable trigger [serverdbtrigger_protectobjects] on all server
go

 

四. 补充

创建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%'