SQL Server 2008中的代码安全（二）：DDL触发器与登录触发器

港控/mmm° 2022-05-15 05:07 245阅读 0赞

本文主要 涉及DDL触发器和登录触发器的应用实例。

MicrosoftSQL Server 提供两种主要机制来强制使用业务规则和数据完整性：约束和触发器。触发器为特殊类型的存储过程，可在执行语言事件时自动生效。SQL Server 包括三种常规类型的触发器：**DML 触发器** 、**DDL 触发器** 和**登录触发器** 。

1、当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

关于DML触发器应用最为广泛。这里不再赘述。MSDN官方说明:[http://msdn.microsoft.com/zh-cn/library/ms189799.aspx][http_msdn.microsoft.com_zh-cn_library_ms189799.aspx]

2、当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。

下面我们用举例说明DDL触发器（[http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx][http_technet.microsoft.com_zh-cn_library_ms189799_28SQL.90_29.aspx] ）的应用：

示例一：**创建一个DDL触发器审核数据库级事件**

/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 创建一个审核表,其中EventData是一个XML数据列 3w@live.cn \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ USE master GO CREATE TABLE dbo.ChangeAttempt (EventData xml NOT NULL, AttemptDate datetime NOT NULL DEFAULT GETDATE(), DBUser char(50) NOT NULL) GO /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 在目标数据库上创建一个触发器,以记录该数据库的索引变化动作， 包括Create|alter|Drop 3w@live.cn \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ CREATE TRIGGER db\_trg\_RestrictINDEXChanges ON DATABASE FOR CREATE\_INDEX, ALTER\_INDEX, DROP\_INDEX AS SET NOCOUNT ON INSERT dbo.ChangeAttempt (EventData, DBUser) VALUES (EVENTDATA(), USER) GO /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 创建一个索引，以测试触发器 3w@live.cn \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ CREATE NONCLUSTERED INDEX ni\_ChangeAttempt\_DBUser ON dbo.ChangeAttempt(DBUser) GO /\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* 查看审核记录 3w@live.cn \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ SELECT EventData FROM dbo.ChangeAttempt --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------删除测试触发器和记录表 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ ----drop TRIGGER \[db\_trg\_RestrictINDEXChanges\] ----ON DATABASE ----go ----drop table dbo.ChangeAttempt ----go

执行结果：

![邀月工作室][2011-3-1_2014-07-43.png_psid_1]

示例二：**创建一个DDL触发器审核服务器级事件**

\--------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------在目标数据库服务器上创建一个触发器,以防止添加登录账号， --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ USE master GO -- Disallow new Logins on the SQL instance CREATE TRIGGER srv\_trg\_RestrictNewLogins ON ALL SERVER FOR CREATE\_LOGIN AS PRINT 'No login creations without DBA involvement.' ROLLBACK GO --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------试图创建一个登录账号 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ CREATE LOGIN johny WITH PASSWORD = '123456' GO --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------删除演示触发器 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ drop TRIGGER srv\_trg\_RestrictNewLogins ON ALL SERVER go

效果：

![邀月工作室][2011-3-1_2014-21-08.png_psid_1]

注意：**要特别谨慎使用DDL触发器。如果设置不当，将会在数据库级甚至服务器级引发不可预知的后果。**

3、登录触发器（[http://msdn.microsoft.com/zh-cn/library/bb326598.aspx][http_msdn.microsoft.com_zh-cn_library_bb326598.aspx] ）将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。

如果你有这样的需求：在某个特定的时间只允许某个账号登录服务器（如单位和家里使用不同的账号远程登录服务器），那么登录触发器是一个不错的选择。

示例三：**创建一个登录触发器审核登录事件**

\--------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------创建登录账号 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ CREATE LOGIN nightworker WITH PASSWORD = '123b3b4' GO --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------演示数据库和审核表 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ CREATE DATABASE ExampleAuditDB GO USE ExampleAuditDB GO CREATE TABLE dbo.RestrictedLogonAttempt (LoginNM sysname NOT NULL, AttemptDT datetime NOT NULL) GO --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------创建登录触发器,如果不是在7:00-17:00登录，则记录审核日志，并提示失败 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ USE master GO Create TRIGGER trg\_logon\_attempt ON ALL SERVER WITH EXECUTE AS 'sa' FOR LOGON AS BEGIN IF ORIGINAL\_LOGIN()='nightworker' AND DATEPART(hh,GETDATE()) BETWEEN 7 AND 17 BEGIN ROLLBACK INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt (LoginNM, AttemptDT) VALUES (ORIGINAL\_LOGIN(), GETDATE()) END END GO --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------查看审核记录 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ USE ExampleAuditDB GO select \* from dbo.RestrictedLogonAttempt go --------/\*\*\*\*\*\*\*\*\*\*\*\*\*\*\* --------删除演示数据库及演示触发器 --------3w@live.cn --------\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*/ use master go drop TRIGGER trg\_logon\_attempt ON ALL SERVER go drop database ExampleAuditDB go

结果：

![邀月工作室][2011-3-1_2014-48-05.png_psid_1]

![邀月工作室][2011-3-1_2014-51-37.png_psid_1]

当然，你也可以使用应用程序或类似于log4net的日志机制记录类似的登录事件，但SQL server 2008已经为我们做到了，你所做的仅仅是有勇气来试一试。

小结：作为对数据DDL操作和登录事件的审核和监控，SQL Server提供了比较完善的机制。这也是SQL server安全机制的一部分。下文将涉及SQL server数据库级的透明加密,敬请关注。

邀月注：本文版权由邀月和CSDN共同所有，转载请注明出处。
    
    助人等于自助! 3w@live.cn

[http_msdn.microsoft.com_zh-cn_library_ms189799.aspx]: http://msdn.microsoft.com/zh-cn/library/ms189799.aspx
[http_technet.microsoft.com_zh-cn_library_ms189799_28SQL.90_29.aspx]: http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx
[2011-3-1_2014-07-43.png_psid_1]: /images/20220515/7058345075974bd09475d2a26ae1263f.png
[2011-3-1_2014-21-08.png_psid_1]: /images/20220515/4fdf2d020c58409d8c5afd47ad9cf3ed.png
[http_msdn.microsoft.com_zh-cn_library_bb326598.aspx]: http://msdn.microsoft.com/zh-cn/library/bb326598.aspx
[2011-3-1_2014-48-05.png_psid_1]: /images/20220515/74340fd1ef204de19e04c70728939758.png
[2011-3-1_2014-51-37.png_psid_1]: /images/20220515/ace616df05e443e2bc79e42877cc57ce.png