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

renheshi

　　SQL Server 2008中SQL应用系列--目录索引
　　
　　本文主要 涉及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
　　2、当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。DDL 触发器是一种特殊的触发器，它在响应数据定义语言 (DDL) 语句时触发。它们可以用于在数据库中执行管理任务，例如，审核以及规范数据库操作。
　　下面我们用举例说明DDL触发器（http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx）的应用：
　　示例一：创建一个DDL触发器审核数据库级事件


/***************
创建一个审核表,其中EventData是一个XML数据列
3w@live.cn
*******************/
USE master
GO
CREATETABLE dbo.ChangeAttempt
(EventData xml NOTNULL,
AttemptDate datetimeNOTNULLDEFAULTGETDATE(),
DBUser char(50) NOTNULL)
GO
/***************
在目标数据库上创建一个触发器,以记录该数据库的索引变化动作，
包括Create|alter|Drop
3w@live.cn
*******************/
CREATETRIGGER db_trg_RestrictINDEXChanges
ONDATABASE
FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
AS
SET NOCOUNT ON
INSERT dbo.ChangeAttempt
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO
/***************
创建一个索引，以测试触发器
3w@live.cn
*******************/
CREATENONCLUSTEREDINDEX 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 　　
　　执行结果：
http://p69muq.blu.livefilestore.com/y1pzBwiVjj2RKclK8-STon42hwIW5sOfKaAPRpnkwYslj7sGxTbKLzHKrmge2hlSEe3RI9y2lY5svdJo4amhU1yjXD7YtVtTaj_/2011-3-1%2014-07-43.png?psid=1
　　示例二：创建一个DDL触发器审核服务器级事件


--------/***************
--------在目标数据库服务器上创建一个触发器,以防止添加登录账号，
--------3w@live.cn
--------*******************/
USE master
GO
-- Disallow new Logins on the SQL instance
CREATETRIGGER srv_trg_RestrictNewLogins
ONALL 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
--------*******************/

dropTRIGGER srv_trg_RestrictNewLogins
ONALL SERVER
go　　
　　效果：
http://p69muq.blu.livefilestore.com/y1pvG1NvS52wvyV9JUnmkXARer_v2dV8Wef59hN7cOSEdq0D5EiM_pWQe8RGX9OfiKVAWAATV_I6WZNxl6f9a9POAHvQOcVwLMo/2011-3-1%2014-21-08.png?psid=1
　　注意：要特别谨慎使用DDL触发器。如果设置不当，将会在数据库级甚至服务器级引发不可预知的后果。
　　
　　3、登录触发器（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
--------*******************/

CREATEDATABASE ExampleAuditDB
GO
USE ExampleAuditDB
GO
CREATETABLE dbo.RestrictedLogonAttempt
(LoginNM sysname NOTNULL,
AttemptDT datetimeNOTNULL)
GO
--------/***************
--------创建登录触发器,如果不是在7:00-17:00登录，则记录审核日志，并提示失败
--------3w@live.cn
--------*******************/

USE master
GO
CreateTRIGGER trg_logon_attempt
ONALL SERVER
WITHEXECUTEAS'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()='nightworker'AND
DATEPART(hh,GETDATE()) BETWEEN7AND17
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
dropTRIGGER trg_logon_attempt
ONALL SERVER
go
dropdatabase ExampleAuditDB
go　　
　　结果：
http://p69muq.blu.livefilestore.com/y1pCAoe2drUWHRQV5p9DERhTDEjCOpGYfdELBUJJsC-C0IFiLgPe7qOswwXuC_PrtaNDK8vFs3vZJ5_O4CEYGaFVbMAseWRc8kg/2011-3-1%2014-48-05.png?psid=1
http://p69muq.blu.livefilestore.com/y1ppmx09b6kMHRCx3XCtVvwe_Q9nGVat5PGupYk3VotoeMG6SpAzmfrl9oEMKv-KKoGWcOXMn2kw1FW_1qJmnX0K0OrmjwsvR22/2011-3-1%2014-51-37.png?psid=1
　　当然，你也可以使用应用程序或类似于log4net的日志机制记录类似的登录事件，但SQL server 2008已经为我们做到了，你所做的仅仅是有勇气来试一试。
　　小结：作为对数据DDL操作和登录事件的审核和监控，SQL Server提供了比较完善的事件处理机制。这也是SQL server安全机制的一部分。下文将涉及SQL server数据库级的透明加密,敬请关注。