浅析SQL Server 2008中的代码安全之二:DDL触发器与登录触发器

【说明】 本文转载自:http://database.51cto.com/art/201103/246987.htm

浅析SQL Server 2008中的代码安全之二:DDL触发器与登录触发器

笔者之前介绍了代码安全之一:存储过程加密与安全上下文,这次为我们介绍了涉及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触发器审核数据库级事件

  1. /***************  

  2. 创建一个审核表,其中EventData是一个XML数据列3w@live.cn*******************/  

  3. USE master  

  4. GO  

  5. CREATE TABLE dbo.ChangeAttempt  

  6. (EventData xml NOT NULL,  

  7. AttemptDate datetime NOT NULL DEFAULT GETDATE(),  

  8. DBUser char(50) NOT NULL)  

  9. GO  

  10. /***************  

  11. 在目标数据库上创建一个触发器,以记录该数据库的索引变化动作,包括Create|alter|Drop 

  12. 3w@live.cn*******************/  

  13. CREATE TRIGGER db_trg_RestrictINDEXChanges  

  14. ON DATABASE 

  15. FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX  

  16. AS 

  17. SET NOCOUNT ON 

  18. INSERT dbo.ChangeAttempt  

  19. (EventData, DBUser)  

  20. VALUES (EVENTDATA(), USER)  

  21. GO  

  22. /***************  

  23. 创建一个索引,以测试触发器3w@live.cn*******************/  

  24. CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON 

  25. dbo.ChangeAttempt(DBUser)  

  26. GO  

  27. /***************  

  28. 查看审核记录  

  29. 3w@live.cn*******************/  

  30. SELECT EventData  

  31. FROM dbo.ChangeAttempt <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——  

  32. /***************-  

  33. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<—–删除测试触发器和记录表——-  

  34. -3w@live.cn<span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——*******************/  

  35. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<–drop TRIGGER [db_trg_RestrictINDEXChanges]  

  36. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<–ON DATABASE  

  37. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<–go  

  38. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<–drop table dbo.ChangeAttempt  

  39. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<–go   

执行结果:

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

  1. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/  

  2. 在目标数据库服务器上创建一个触发器,以防止添加登录账号,  

  3. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  4. USE master  

  5. GO  

  6. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–< Disallow new Logins on the SQL instance  

  7. CREATE TRIGGER srv_trg_RestrictNewLogins  

  8. ON ALL SERVER  

  9. FOR CREATE_LOGIN  

  10. AS 

  11. PRINT 'No login creations without DBA involvement.' 

  12. ROLLBACK 

  13. GO  

  14. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  15. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——试图创建一个登录账号  

  16. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  17. CREATE LOGIN johny WITH PASSWORD = '123456' 

  18. GO  

  19. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  20. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——删除演示触发器  

  21. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  22. drop TRIGGER srv_trg_RestrictNewLogins  

  23. ON ALL SERVER  

  24. go   

效果:

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

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

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

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

  1. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  2. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——创建登录账号  

  3. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  4. CREATE LOGIN nightworker WITH PASSWORD = '123b3b4' 

  5. GO  

  6. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  7. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——演示数据库和审核表  

  8. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  9. CREATE DATABASE ExampleAuditDB  

  10. GO  

  11. USE ExampleAuditDB  

  12. GO  

  13. CREATE TABLE dbo.RestrictedLogonAttempt  

  14. (LoginNM sysname NOT NULL,  

  15. AttemptDT datetime NOT NULL)  

  16. GO  

  17. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  18. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——创建登录触发器,如果不是在7:00-17:00登录,  

  19. 则记录审核日志,并提示失败  

  20. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  21. USE master  

  22. GO  

  23. Create TRIGGER trg_logon_attempt  

  24. ON ALL SERVER  

  25. WITH EXECUTE AS 'sa' 

  26. FOR LOGON  

  27. AS 

  28. BEGIN 

  29. IF ORIGINAL_LOGIN()='nightworker' AND 

  30. DATEPART(hh,GETDATE()) BETWEEN 7 AND 17  

  31. BEGIN 

  32. ROLLBACK 

  33. INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt  

  34. (LoginNM, AttemptDT)  

  35. VALUES (ORIGINAL_LOGIN(), GETDATE())  

  36. END 

  37. END 

  38. GO  

  39. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  40. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——查看审核记录  

  41. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  42. USE ExampleAuditDB  

  43. GO  

  44. select * from dbo.RestrictedLogonAttempt  

  45. go  

  46. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——/***************  

  47. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——删除演示数据库及演示触发器  

  48. <span style="margin: 0px; padding: 0px; border: none; background-color: inherit; color: rgb(0, 130, 0); font-family: 宋体; font-size: 14px; font-style: normal; font-variant-caps: normal; font-variant-ligatures: normal; font-weight: normal; line-height: 18px;"–<——3w@live.cn——–*******************/  

  49. use master  

  50. go  

  51. drop TRIGGER trg_logon_attempt  

  52. ON ALL SERVER  

  53. go  

  54. drop database ExampleAuditDB  

  55. go  

结果:

 

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

小结:作为对数据DDL操作和登录事件的审核和监控,SQL Server提供了比较完善的事件处理机制。这也是SQL server安全机制的一部分。

原文出处:http://www.cnblogs.com/downmoon/archive/2011/03/01/1966670.html

此条目发表在未分类分类目录,贴了标签。将固定链接加入收藏夹。