使用Server Trigger保护重要的数据库对象
程序员文章站
2022-07-06 12:00:41
一 .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, [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> servername:' + @servername+ ' ; serverip:' + @serverip+ '上的object已被改动,请及时检查!!! <br> 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%'
上一篇: 日期计数器:C++实现