浅析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   

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

  33.  

  34. -3w@live.cn  

  35.  

  36.  

  37.  

  38.  

  39.  

执行结果:

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

  1.  

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

  3.  

  4. USE master  

  5. GO  

  6.  

  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.  

  15.  

  16.  

  17. CREATE LOGIN johny WITH PASSWORD = '123456' 

  18. GO  

  19.  

  20.  

  21.  

  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.  

  2.  

  3.  

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

  5. GO  

  6.  

  7.  

  8.  

  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.  

  18.  

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

  20.  

  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.  

  40.  

  41.  

  42. USE ExampleAuditDB  

  43. GO  

  44. select * from dbo.RestrictedLogonAttempt  

  45. go  

  46.  

  47.  

  48.  

  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

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