SQL Server 2008中的代码安全(二) DDL触发器与登录触发器_mssql2008

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

结果:

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

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

时间: 2024-11-08 19:26:58

SQL Server 2008中的代码安全(二) DDL触发器与登录触发器_mssql2008的相关文章

SQL Server 2008中的代码安全(三) 通过PassPhrase加密_mssql2008

前言:       在SQL Server 2005和SQL Server 2008之前.如果希望加密敏感数据,如财务信息.工资或身份证号,必须借助外部应用程序或算法.SQL Server 2005引入内建数据加密的能力,使用证书.密钥和系统函数的组合来完成.     与数字证书类似.SQL Server 证书包括公钥和私钥这一对密钥,它们都用来加密和解密数据.SQL Server也拥有创建非对称密钥和对称密钥对象的能力.非对称密钥(asymmetric key)与证书相似,公钥用来加密数据库,

SQL Server 2008中的代码安全(八)透明加密(TDE)_mssql2008

当一个用户数据库可用且已启用TDE时,在写入到磁盘时在页级实现加密.在数据页读入内存时解密.如果数据库文件或数据库备份被盗,没有用来加密的原始证书将无法访问.这几乎是SQL Server2008安全选项中最激动人心的功能了,有了它,我们至少可以将一些初级的恶意窥视拒之见外. 下面的两个例子将展示如何启用和维护透明数据加密. 示例一.启用透明加密(TDE) /********************TDE**************** 3w@live.cn ****************/ U

SQL Server 2008中的代码安全(六) 对称密钥加密_mssql2008

证书和非对称密钥使用数据库级的内部公钥加密数据,并且使用数据库级内部私钥解密数据.而对称密钥相对简单,它们包含一个同时用来加密和解密的密钥.困此,使用对称密钥加密数据更快,并且用在大数据时更加合适.尽管复杂度是考虑使用它的因素,但它仍然是一个很好的加密数据的选择.  我们看一组例子: 示例一.创建对称密钥 对称密钥的特性是:在数据库会话中使用它对数据进行加密和解密前必须首先打开. 创建对称密钥使用如下命令:CREATE SYMMETRIC KEY  创建对称密钥.(http://msdn.mic

SQL Server 2008中有关XML的新功能

1 导言 Microsoft 在Microsoft SQL Server 2000中推出了与XML相关的功能以及Transact-SQL 关键字FOR XML和OPENXML ,这使得开发人员可以编写Transact-SQL代码来获取XML流形式的查询结果,并将一个XML 文档分割成一个rowset.SQL Server 2005显著的扩展了这些XML功能,推出了一个支持XSD schema验证. 基于XQuery的操作和XML索引的本地的xml 数据类型.SQL Server 2008建立在之

SQL点滴15—在SQL Server 2008中调用C#程序

原文:SQL点滴15-在SQL Server 2008中调用C#程序 T-SQL的在执行普通的查询的时候是很高效的,但是在执行循环,判断这样的语句的时候效率就不那么的高了.这时可以借助CLR了,我们可以在SQL Server 2008中扩展C#程序来完成循环等过程式的查询,或者其他SQL不便实现的功能.这个随笔中将介绍在SQL Server中扩展C#程序实现正则表达式的替换功能. 新建一个类库程序命名为Regex,打开Visual Studio 2008,点击File,点击New,点击Proje

使用SQL Server 2008中分区表快速执行存档任务

在此练习中,您将实现一个可调窗口应用场景,通过将分区切换入表中以及从表中将分区切换出去实现此应用场景.在大多数系统中,最常使用的数据都是最新的数据.在非常大的表中,定期对时间最早的数据进行存档很有用,这样可以改善性能,对给新数据创建空间也是很有必要的.使用 SQL Server 2008 中分区表的 SPLIT.SWITCH 和 MERGE 功能,您可以极快地执行存档任务. 注意: 您可以复制此练习中所用的脚本,这些脚本位于 C:\SQLHOLS\Partitioning\Solution\Pa

SQL Server 2008性能故障排查(二)——CPU

原文:SQL Server 2008性能故障排查(二)--CPU 承接上一篇:SQL Server 2008性能故障排查(一)--概论 说明一下,CSDN的博客编辑非常不人性化,我在word里面都排好了版,贴上来就乱得不成样了.建议CSDN改进这部分.也请大家关注内容不要关注排版.同时在翻译的过程中本人也整理了一次思路,所以还似乎非常愿意翻译,虽然有点自娱自乐,但是分享给大家也是件好事 CPU 瓶颈:CPU瓶颈可能因为某个负载所需的硬件资源不足而引起.但是过多的CPU使用通常可以通过查询优化(特

利用SQL Server 2008中MERGE刷新库存快照

SQL Server 2008是一个重大的产品版本,它推出了许多新的特性和关键的改进,使得它成为至今为止的最强大和最全面的SQL Server版本.这篇文章详细介绍了http://www.aliyun.com/zixun/aggregation/11208.html">Microsoft SQL Server 2008中的新的特性.优点和功能--  在现今数据的世界里,公司要获得成功和不断发展,他们需要定位主要的数据趋势的愿景.微软的这个数据平台愿景帮助公司满足这些数据爆炸和下一代数据驱动

在SQL Server 2008中调用.net,dll

原文:在SQL Server 2008中调用.net,dll T-SQL的在执行普通的查询的时候是很高效的,但是在执行循环,判断这样的语句的时候效率就不那么的高了.这时可以借助CLR了,我们可以在SQL Server 2008中扩展C#程序来完成循环等过程式的查询,或者其他SQL不便实现的功能.这个随笔中将介绍在SQL Server中扩展C#程序实现正则表达式的替换功能. 新建一个类库程序命名为Regex,打开Visual Studio 2008,点击File,点击New,点击Project,在