触发器系列一之DDL触发器

81 阅读 0 评论 54 点赞

我是靠谱客的博主活力棉花糖，最近开发中收集的这篇文章主要介绍触发器系列一之DDL触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

近期出于同步数据的需求，需要创建一个具有在目标数据库具有alter table权限的帐号，但是由于该工具在alter table同步表结构时会同时频繁发起create index和drop index。现阶段工具无法优化，只能从权限入手直接收回alter index权限。

sqlserver的alter权限包含alter,create,drop，无法单独赋予create或drop权限。想让帐号只能进行alter table操作，考虑创建Transact-SQL DDL触发器，当用户进行除alter table 外的其他操作，将回滚该操作并返回错误信息。

下面附上详细脚本与DDL触发器使用说明，后期会整理其他几种类型的触发器以作记录与学习。

创建测试帐号

--创建测试帐号
use master
go
create login t_test with pasword='test##123456';
use test 
go
create user t_test for login t_test
--赋予读写权限
EXEC sp_addrolemember  N'db_datareader',N'u_test';
EXEC sp_addrolemember  N'tb_datawriter',N'u_test';
--为该权限单独创建数据库角色
use test
go
CREATE ROLE tb_alter
--授予该数据库角色alter权限
GRANT alter TO tb_alter;
--赋予u_test帐号tb_alter角色身份
EXEC sp_addrolemember  N'tb_alter',N'u_test';

创建DDL触发器

alter TRIGGER TR_Limit_Role_tb_alter_DDL_Events
ON DATABASE
with execute as sa
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
  declare
    @eventData          XML,
    @DATABASENAME      SYSNAME,
    @EVENTDATE         DATETIME,
    @USERNAME          SYSNAME,
    @SYSTEMUSER        VARCHAR(128),
    @CURRENTUSER       VARCHAR(128),
    @ORIGINALUSER      VARCHAR(128),
    @HOSTNAME          VARCHAR(128),
    @APPLICATIONNAME   VARCHAR(128),
    @SCHEMANAME        SYSNAME,
    @OBJECTNAME        SYSNAME,
    @OBJECTTYPE        SYSNAME,
    @EVENTTYPE         VARCHAR(128),
    @COMMANDTEXT       VARCHAR(max),
    @NAMEFORDEFINITION VARCHAR(261),
    @CMD       VARCHAR(500)
  --Load Variables from the xml
  SET @eventData = eventdata()
  --my standard variables for a DDL trigger are above,
  SELECT
    @DATABASENAME    = db_name(),
    @EVENTDATE       = GETDATE(),
    @USERNAME        = @eventData.value('data(/EVENT_INSTANCE/UserName)[1]', 'SYSNAME'),
    @SYSTEMUSER      = SUSER_SNAME(),
    @CURRENTUSER     = CURRENT_USER,
    @ORIGINALUSER    = ORIGINAL_LOGIN(),
    @HOSTNAME        = HOST_NAME(),
    @APPLICATIONNAME = APP_NAME(),
    @SCHEMANAME      = @eventData.value('data(/EVENT_INSTANCE/SchemaName)[1]', 'SYSNAME'),
    @OBJECTNAME      = @eventData.value('data(/EVENT_INSTANCE/ObjectName)[1]', 'SYSNAME'),
    @OBJECTTYPE      = @eventData.value('data(/EVENT_INSTANCE/ObjectType)[1]', 'SYSNAME'),
    @COMMANDTEXT     = @eventData.value('data(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(MAX)'),
    @EVENTTYPE       = @eventData.value('data(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(128)')
    --只审计u_test帐号的相关操作
    IF IS_MEMBER ('u_test') = 1
    BEGIN
      --只审计下列事件
    IF @EVENTTYPE NOT IN('CREATE_DEFAULT', 'DROP_DEFAULT', 'ALTER_TABLE', 'ALTER_VIEW')
    BEGIN
    RAISERROR ('触发器"TR_Limit_ViewMaker_DDL_Events" 不允许相关alter操作', 16, 1)
    ROLLBACK
    END
    END
END --trigger
GO
--启用触发器
ENABLE TRIGGER TR_Limit_Role_tb_alter_DDL_Events ON DATABASE
GO

DDL触发器主要是响应数据定义语言（DDL）语句而运行的。语句类型包括CREATE，ALTER，DROP，GRANT，DENY，REVOKE和UPDATE STATISTICS。某些执行类似DDL的操作的系统存储过程也可以触发DDL触发器。

DDL触发器仅在运行触发它们的DDL语句后才触发。DDL触发器不能用作INSTEAD OF触发器。

运行CREATE TRIGGER之后，事件组所涵盖的事件将添加到sys.trigger_events目录视图中。

DDL触发器分为：

Transact-SQL DDL触发器：一种特殊类型的Transact-SQL存储过程，用于响应服务器范围或数据库范围的事件而执行一个或多个Transact-SQL语句，极大多数触发器都是此类
CLR DDL触发器：执行一个或多个用托管代码编写的方法，这些方法是在.NET Framework中创建并在SQL Server中上载的程序集的成员

--标准语法  
CREATE [ OR ALTER ] TRIGGER trigger_name   
ON { ALL SERVER | DATABASE }   --触发器应用范围为当前数据库|服务器
[ WITH <ddl_trigger_option> [ ,...n ] ]  
{ FOR | AFTER } --指定仅在触发SQL语句中指定的所有操作成功启动后才触发触发器
{ event_type | event_group } [ ,...n ]  
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  --触发条件和操作，在尝试操作时，在Transact-SQL语句中指定的触发操作将生效。
  
<ddl_trigger_option> ::=  
    [ ENCRYPTION ]  --遮盖CREATE TRIGGER语句的文本，防止触发器作为SQL Server复制的一部分发布
    [ EXECUTE AS Clause ] --指定执行触发器的安全上下文，该选项能够控制SQL Server实例来验证触发器所引用的任何数据库对象的权限的用户帐户。
{event_type|event_group}  --启动后导致DDL触发器触发的Transact-SQL语言事件的名称|Transact-SQL语言事件的预定义分组的名称

完整的DDL事件组和DDL事件可以使用下列语句查询

WITH DirectReports(name, parent_type, type, level, sort) AS   
(  
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)  
    FROM sys.trigger_event_types   
    WHERE parent_type IS NULL  
    UNION ALL  
    SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),  
        e.parent_type, e.type, level + 1,  
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)  
    FROM sys.trigger_event_types AS e  
        INNER JOIN DirectReports AS d  
        ON e.parent_type = d.type   
)  
SELECT parent_type, type, name  
FROM DirectReports  
ORDER BY sort;