管理触发器安全性Manage Trigger Security

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

默认情况下,在调用触发器的用户的上下文中执行 DML 和 DDL 触发器。By default, both DML and DDL triggers execute under the context of the user that calls the trigger. 触发器的调用方是执行使触发器运行的语句的用户。The caller of a trigger is the user that executes the statement that causes the trigger to run. 例如,如果用户 Mary 执行可以使 DML 触发器 DML_trigMary 运行的 DELETE 语句,则 DML_trigMary 中的代码将在 Mary的用户特权上下文中执行。For example, if user Mary executes a DELETE statement that causes DML trigger DML_trigMary to run, the code inside DML_trigMary executes in the context of the user privileges for Mary. 希望向数据库或服务器实例中引入恶意代码的用户可以使用此默认行为。This default behavior can be exploited by users who want to introduce malicious code in the database or server instance. 例如,用户 JohnDoe创建以下 DDL 触发器:For example, the following DDL trigger is created by user JohnDoe:

CREATE TRIGGER DDL_trigJohnDoe

ON DATABASE

FOR ALTER_TABLE

AS

GRANT CONTROL SERVER TO JohnDoe ;

GO

此触发器的含义是:在有权执行 GRANT CONTROL SERVER 语句的用户(如 sysadmin 固定服务器角色的成员)执行 ALTER TABLE 语句时,为 JohnDoe 授予 CONTROL SERVER 权限。What this trigger means is that as soon as a user that has permission to execute a GRANT CONTROL SERVER statement, such as a member of the sysadmin fixed server role, executes an ALTER TABLE statement, JohnDoe is granted CONTROL SERVER permission. 换言之,虽然 JohnDoe 不能为自己授予 CONTROL SERVER 权限,但是他启用了授予他此权限的触发器代码以在升级特权下执行。In other words, although JohnDoe cannot grant CONTROL SERVER permission to himself, he enabled the trigger code that grants him this permission to execute under escalated privileges. 对于此类安全隐患,DML 和 DDL 触发器都处于打开状态。Both DML and DDL triggers are open to this kind of security threat.

保证触发器安全的最佳方法Trigger Security Best Practices

可以采取下列措施阻止触发器代码在升级特权下执行:You can take the following measures to prevent trigger code from executing under escalated privileges:

  • 注意数据库和服务器实例中存在的 DML 和 DDL 触发器,方法是查询 sys.triggerssys.server_triggers 目录视图。Be aware of the DML and DDL triggers that exist in the database and on the server instance by querying the sys.triggers and sys.server_triggers catalog views. 下面的查询将返回当前数据库中的所有 DML 触发器和数据库级别的 DDL 触发器,以及服务器实例中所有服务器级别的 DDL 触发器:The following query returns all DML and database-level DDL triggers in the current database, and all server-level DDL triggers on the server instance:

    SELECT type, name, parent_class_desc FROM sys.triggers  
    UNION  
    SELECT type, name, parent_class_desc FROM sys.server_triggers ;  
    

备注

仅 sys.triggers 适用于 Azure SQL 数据库,除非你使用的是托管实例。Only sys.triggers is available for Azure SQL Database unless you are using Managed Instance.

  • 请注意数据库中是否存在 DML 和 DDL 触发器,具体方法是查询 sys.triggers 目录视图。Be aware of the DML and DDL triggers that exist in the database by querying the sys.triggers catalog view. 下面的查询返回当前数据库中的所有 DML 和数据库级别 DDL 触发器:The following query returns all DML and database-level DDL triggers in the current database:

    SELECT type, name, parent_class_desc FROM sys.triggers ; 
    
  • 使用 DISABLE TRIGGER 禁用在升级特权下执行时可能会损害数据库或服务器完整性的触发器。Use DISABLE TRIGGER to disable triggers that can harm the integrity of the database or server if the triggers execute under escalated privileges. 下面的语句可以禁用当前数据库中所有数据库级别的 DDL 触发器:The following statement disables all database-level DDL triggers in the current database:

    DISABLE TRIGGER ALL ON DATABASE  
    

    下面的语句可以禁用服务器实例中所有服务器级别的 DDL 触发器:This statement disables all server-level DDL triggers on the server instance:

    DISABLE TRIGGER ALL ON ALL SERVER  
    

    下面的语句可以禁用当前数据库中的所有 DML 触发器:This statement disables all DML triggers in the current database:

    DECLARE @schema_name sysname, @trigger_name sysname, @object_name sysname ;  
    DECLARE @sql nvarchar(max) ;  
    DECLARE trig_cur CURSOR FORWARD_ONLY READ_ONLY FOR  
        SELECT SCHEMA_NAME(schema_id) AS schema_name,  
            name AS trigger_name,  
            OBJECT_NAME(parent_object_id) as object_name  
        FROM sys.objects WHERE type in ('TR', 'TA') ;  
    
    OPEN trig_cur ;  
    FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;  
    
    WHILE @@FETCH_STATUS = 0  
    BEGIN  
        SELECT @sql = 'DISABLE TRIGGER ' + QUOTENAME(@schema_name) + '.'  
            + QUOTENAME(@trigger_name) +  
            ' ON ' + QUOTENAME(@schema_name) + '.'   
            + QUOTENAME(@object_name) + ' ; ' ;  
        EXEC (@sql) ;  
        FETCH NEXT FROM trig_cur INTO @schema_name, @trigger_name, @object_name ;  
    END  
    GO  
    
    -- Verify triggers are disabled. Should return an empty result set.  
    SELECT * FROM sys.triggers WHERE is_disabled = 0 ;  
    GO  
    
    CLOSE trig_cur ;  
    DEALLOCATE trig_cur;  
    

另请参阅See Also

CREATE TRIGGER (Transact-SQL) CREATE TRIGGER (Transact-SQL)
DML 触发器 DML Triggers
DDL 触发器DDL Triggers