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

154 阅读 0 评论 102 点赞

我是靠谱客的博主优美柚子，最近开发中收集的这篇文章主要介绍SQL Server 2008中的代码安全（二） DDL触发器与登录触发器，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

本文主要涉及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 
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 

执行结果：

邀月工作室

示例二：创建一个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 

效果：

邀月工作室

注意：要特别谨慎使用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 
--------*******************/ 

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 