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

数据库 SQL Server
笔者之前介绍了代码安全之一:存储过程加密与安全上下文,这次为我们介绍了涉及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. ----drop TRIGGER [db_trg_RestrictINDEXChanges]  
  36. ----ON DATABASE  
  37. ----go  
  38. ----drop table dbo.ChangeAttempt  
  39. ----go   

执行结果:

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

  1. --------/  
  2. 在目标数据库服务器上创建一个触发器,以防止添加登录账号,  
  3. --------3w@live.cn--------*******************/  
  4. USE master  
  5. GO  
  6. -- 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. --------/***************  
  15. --------试图创建一个登录账号  
  16. --------3w@live.cn--------*******************/  
  17. CREATE LOGIN johny WITH PASSWORD = '123456' 
  18. GO  
  19. --------/***************  
  20. --------删除演示触发器  
  21. --------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. --------/***************  
  2. --------创建登录账号  
  3. --------3w@live.cn--------*******************/  
  4. CREATE LOGIN nightworker WITH PASSWORD = '123b3b4' 
  5. GO  
  6. --------/***************  
  7. --------演示数据库和审核表  
  8. --------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. --------/***************  
  18. --------创建登录触发器,如果不是在7:00-17:00登录,  
  19. 则记录审核日志,并提示失败  
  20. --------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. --------/***************  
  40. --------查看审核记录  
  41. --------3w@live.cn--------*******************/  
  42. USE ExampleAuditDB  
  43. GO  
  44. select * from dbo.RestrictedLogonAttempt  
  45. go  
  46. --------/***************  
  47. --------删除演示数据库及演示触发器  
  48. --------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

【编辑推荐】

  1. 浅析SQL Server 2008中的代码安全之一:存储过程加密与安全上下文
  2. SQL Server与Oracle数据库在安全性上的异同
  3. Sql server安全设置九大措施
  4. SQL Server安全解析
  5. sql server安全的两层模型

 

责任编辑:艾婧 来源: 博客园
相关推荐

2010-04-19 10:43:27

SQL Server

2011-03-28 10:05:57

sql触发器代码

2010-06-30 09:36:25

SQL Server

2009-04-07 13:56:03

SQL Server触发器实例

2010-09-13 17:03:34

sql server触

2010-11-12 15:35:55

SQL Server约

2010-07-16 10:19:31

2010-10-20 14:34:48

SQL Server触

2011-05-20 14:06:25

Oracle触发器

2010-10-19 15:31:40

sql server触

2010-07-06 14:47:03

SQL Server数

2010-10-22 11:10:43

SQL Server触

2010-11-08 11:49:24

SQL Server管

2010-11-10 13:37:01

SQL Server触

2011-04-14 10:53:00

MySQLSQL触发器

2010-07-05 11:09:55

SQL Server触

2011-04-01 16:35:09

SQL Server数触发器

2010-05-18 15:36:44

MySQL触发器

2009-04-26 22:27:54

触发器密码修改数据库

2022-04-12 08:24:28

Zabbix事件通知触发器
点赞
收藏

51CTO技术栈公众号