笔记40 SQL Server中的代码安全:DDL触发器与登录触发器 数据库级事件 和服务器级事件

笔记40 SQL Server中的代码安全:DDL触发器与登录触发器 数据库级事件 和服务器级事件

  1 --SQL Server中的代码安全:DDL触发器与登录触发器 数据库级事件 和服务器级事件
  2 
  3 
  4 --创建一个DDL触发器审核数据库级事件
  5 
  6 /***************
  7 创建一个审核表,其中EventData是一个XML数据列
  8 w@live.cn
  9 *******************/
 10 --msdn:http://technet.microsoft.com/zh-cn/library/ms189799%28SQL.90%29.aspx
 11 
 12 
 13 --这个表应该在master库中创建,这里为了演示创建在[pratice]库
 14 
 15 
 16 USE [pratice]
 17 
 18 GO
 19 
 20 CREATE TABLE dbo.ChangeAttempt
 21 (EventData xml NOT NULL,
 22 AttemptDate datetime NOT NULL DEFAULT GETDATE(),
 23 DBUser char(50) NOT NULL)
 24 GO
 25 
 26 SELECT * FROM [dbo].[ChangeAttempt]
 27 /***************
 28 在目标数据库上创建一个触发器,以记录该数据库的索引变化动作,
 29 包括Create|alter|Drop
 30 w@live.cn
 31 *******************/
 32 USE [pratice]
 33 GO
 34 
 35 CREATE TRIGGER db_trg_RestrictINDEXChanges  ON DATABASE
 36 FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
 37 AS
 38 SET NOCOUNT ON
 39 INSERT dbo.ChangeAttempt
 40 (EventData, DBUser)
 41 VALUES (EVENTDATA(), USER)
 42 GO
 43 
 44 /***************
 45 创建一个索引,以测试触发器
 46 w@live.cn
 47 *******************/
 48 USE [pratice]
 49 GO
 50 
 51 CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
 52 GO
 53 --
 54 --/***************
 55 --查看审核记录
 56 --w@live.cn
 57 --*******************/
 58 --
 59 USE [pratice]
 60 GO
 61 
 62 SELECT * FROM [dbo].[ChangeAttempt]
 63 --
 64 --
 65 
 66 ------/***************
 67 ------删除测试触发器和记录表
 68 ------3w@live.cn
 69 ------*******************/
 70 USE [pratice]
 71 GO
 72 
 73 drop TRIGGER db_trg_RestrictINDEXChanges
 74 ON DATABASE
 75 go
 76 drop table dbo.[ChangeAttempt]
 77 GO
 78 
 79 <EVENT_INSTANCE>
 80   <EventType>CREATE_INDEX</EventType>
 81   <PostTime>2013-01-26T01:34:07.590</PostTime>
 82   <SPID>54</SPID>
 83   <ServerName>JOE</ServerName>
 84   <LoginName>JOE\Administrator</LoginName>
 85   <UserName>dbo</UserName>
 86   <DatabaseName>pratice</DatabaseName>
 87   <SchemaName>dbo</SchemaName>
 88   <ObjectName>ni_ChangeAttempt_DBUser</ObjectName>
 89   <ObjectType>INDEX</ObjectType>
 90   <TargetObjectName>test</TargetObjectName>
 91   <TargetObjectType>TABLE</TargetObjectType>
 92   <TSQLCommand>
 93     <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON" ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON" ENCRYPTED="FALSE" />
 94     <CommandText>CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
 95 </CommandText>
 96   </TSQLCommand>
 97 </EVENT_INSTANCE>
 98 
 99 -------------------------------------------------------------------------------------------------------
100 --经理前几天要求记录数据库的最后一次访问时间,不管是我们自己的系统还是使用SSMS还是其他第三方工具
101 --以便对数据库进行审核,因为之前发现有人更改了sa的密码,导致我们的系统连接不上数据库
102 --我们的系统是winform,联网的C/S架构
103 
104 
105 --相信触发器大家并不陌生,做INSERT, UPDATE, or DELETE 这些操作的时候保证数据完整性或者数据同步
106 --都会创建DML触发器,使用inserted表或者deleted表
107 
108 --刚才我提到的是DML触发器,就是做数据操作的时候就触发的,网上资料很多,但是对于
109 
110 --DDL触发器 和登录触发器的资料就比较少了,正好经理提的这个需求可以使用登录触发器和DDL触发器来做
111 
112 
113 --我这里做的是当有人登录数据库或者服务器就做一个日志记录以便遇到登录不上或者其他情况的时候可以查日志,
114 --但是并没有阻止他登录或者继续做其他操作
115 
116 
117 
118 --DDL 触发器用于响应各种数据定义语言 (DDL) 事件。这些事件主要对应于 Transact-SQL CREATE、ALTER 和 DROP 语句,
119 --以及执行类似 DDL 操作的某些系统存储过程。
120 
121 --登录触发器在遇到 LOGON 事件时触发。LOGON 事件是在建立用户会话时引发的。
122 
123 Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
124 CREATE TRIGGER trigger_name
125 ON { ALL SERVER | DATABASE }
126 [ WITH <ddl_trigger_option> [ ,...n ] ]
127 { FOR | AFTER } { event_type | event_group } [ ,...n ]
128 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }
129 
130 <ddl_trigger_option> ::=
131     [ ENCRYPTION ]
132     [ EXECUTE AS Clause ]
133    
134    
135    
136    
137 -----------------------------------------------------------------------------------
138 Trigger on a LOGON event (Logon Trigger)
139 CREATE TRIGGER trigger_name
140 ON ALL SERVER
141 [ WITH <logon_trigger_option> [ ,...n ] ]
142 { FOR| AFTER } LOGON 
143 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }
144 
145 <logon_trigger_option> ::=
146     [ ENCRYPTION ]
147     [ EXECUTE AS Clause ]
148 
149 
150 ------------------------------------------------------------------------------------   
151 
152 DATABASE
153 --将 DDL 触发器的作用域应用于当前数据库。如果指定了此参数,则只要当前数据库中出现 event_type 或 event_group,就会激发该触发器。
154 
155 ALL SERVER
156 --将 DDL 或登录触发器的作用域应用于当前服务器。如果指定了此参数,则只要当前服务器中的任何位置上出现 event_type 或 event_group,就会激发该触发器。
157 
158 
159 event_type
160 --执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。DDL 事件中列出了 DDL 触发器的有效事件。
161 
162 
163 
164 
165 --DDL 和登录触发器通过使用 EVENTDATA (Transact-SQL) 函数来获取有关触发事件的信息
166 
167  
168 ---------------------------------------------------------------------------------------------------------
169 --触发器限制
170 
171 
172 --CREATE TRIGGER 必须是批处理中的第一条语句,并且只能应用于一个表。
173 
174 --触发器只能在当前的数据库中创建,但是可以引用当前数据库的外部对象。
175 --
176 --如果指定了触发器架构名称来限定触发器,则将以相同的方式限定表名称。
177 --
178 --在同一条 CREATE TRIGGER 语句中,可以为多种用户操作(如 INSERT 和 UPDATE)定义相同的触发器操作。
179 --
180 --如果一个表的外键包含对定义的 DELETE/UPDATE 操作的级联,则不能对为表上定义 INSTEAD OF DELETE/UPDATE 触发器。
181 --
182 --在触发器内可以指定任意的 SET 语句。选择的 SET 选项在触发器执行期间保持有效,然后恢复为原来的设置。
183 --
184 --如果触发了一个触发器,结果将返回给执行调用的应用程序,就像使用存储过程一样。若要避免由于触发器触发而向应用程序返回结果,请不要包含返回结果的 SELECT 语句,也不要包含在触发器中执行变量赋值的语句。包含向用户返回结果的 SELECT 语句或进行变量赋值的语句的触发器需要特殊处理;这些返回的结果必须写入允许修改触发器表的每个应用程序中。如果必须在触发器中进行变量赋值,则应该在触发器的开头使用 SET NOCOUNT 语句以避免返回任何结果集。
185 --
186 --虽然 TRUNCATE TABLE 语句实际上就是 DELETE 语句,但是它不会激活触发器,因为该操作不记录各个行删除。
187 --然而,仅那些具有执行 TRUNCATE TABLE 语句的权限的用户才需要考虑是否无意中因为此方式而导致没有使用 DELETE 触发器。
188 --
189 --无论有日志记录还是无日志记录,WRITETEXT 语句都不触发触发器。
190 
191 
192 ----------------------------------------------------------------------------------------------
193 
194 --DDL 触发器
195 
196 
197 --DDL 触发器像标准触发器一样,在响应事件时执行存储过程。但与标准触发器不同的是,它们并不在响应对表或视图的 UPDATE、INSERT 或 DELETE 语句时执行存储过程。它们主要在响应数据定义语言 (DDL) 语句执行存储过程。这些语句包括 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 和 UPDATE STATISTICS 等语句。执行 DDL 式操作的系统存储过程也可以激发 DDL 触发器。
198 
199 --对于影响局部或全局临时表和存储过程的事件,不会触发 DDL 触发器。
200 --
201 --与 DML 触发器不同,DDL 触发器的作用域不是架构。因此,不能将 OBJECT_ID、OBJECT_NAME、OBJECTPROPERTY 和 OBJECTPROPERTYEX 之类的函数用于查询有关 DDL 触发器的元数据。请改用目录视图。
202 
203 
204 --------------------------------------------------------------------------------------------
205 
206 --登录触发器
207 --
208 --
209 --登录触发器将为响应 LOGON 事件而执行存储过程。与 SQL Server 实例建立用户会话时将引发此事件。登录触发器将在登录的身份验证阶段完成之后且用户会话实际建立之前激发。因此,来自触发器内部且通常将到达用户的所有消息(例如错误消息和来自 PRINT 语句的消息)会传送到 SQL Server 错误日志。有关详细信息,请参阅登录触发器。
210 --
211 --如果身份验证失败,将不激发登录触发器。
212 --
213 --在登录触发器中不支持分布式事务。在激发包含分布式事务的登录触发器时,将返回错误 3969。
214 
215 
216 
217 --禁用登录触发器
218 --
219 --
220 --登录触发器可以有效地阻止所有用户(包括 sysadmin 固定服务器角色的成员)与数据库引擎的成功连接。在登录触发器正在阻止连接时,sysadmin 固定服务器角色的成员可通过使用专用管理员连接,或者通过以最小配置模式 (-f) 启动数据库引擎,来进行连接
221 
222 
223 --------------------------------示例--------------------------------------------
224 --下面的示例使用 DDL 触发器来防止从数据库中删除任何同义词。
225 USE [AdventureWorks];
226 GO
227 IF EXISTS (SELECT * FROM sys.triggers
228     WHERE parent_class = 0 AND name = 'safety')
229 DROP TRIGGER safety
230 ON DATABASE;
231 GO
232 CREATE TRIGGER safety
233 ON DATABASE
234 FOR DROP_SYNONYM
235 AS
236    RAISERROR ('You must disable Trigger "safety" to drop synonyms!',10, 1)
237    ROLLBACK
238 GO
239 DROP TRIGGER safety
240 ON DATABASE;
241 GO
242 
243 ------------------------------------------------------------------------------
244 --E.使用具有服务器范围的 DDL 触发器
245 --
246 --
247 --在以下示例中,如果当前服务器实例上出现任何 CREATE DATABASE 事件,则使用 DDL 触发器输出一条消息,
248 --并使用 EVENTDATA 函数检索对应 Transact-SQL 语句的文本
249 USE [AdventureWorks]
250 GO
251 
252 IF EXISTS (SELECT * FROM sys.server_triggers
253     WHERE name = 'ddl_trig_database')
254 DROP TRIGGER ddl_trig_database
255 ON ALL SERVER;
256 GO
257 CREATE TRIGGER ddl_trig_database
258 ON ALL SERVER
259 FOR CREATE_DATABASE   --CREATE_DATABASE是其中一个DDL事 具体看SQL提供了哪些DDL事件 --http://msdn.microsoft.com/zh-cn/library/bb522542.aspx
260 AS
261     PRINT 'Database Created.'
262     SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
263 GO
264 DROP TRIGGER ddl_trig_database
265 ON ALL SERVER;
266 GO
267 
268 --XML在SQL中的使用只有 XML FOR PATH
269 
270 -------------------------------------------------------------------------------
271 --使用登录触发器
272 --
273 --
274 --下面的登录触发器示例拒绝了作为 login_test 登录名的成员登录 SQL Server 的尝试(如果在此登录名下已运行三个用户会话)。
275 
276 USE master;
277 GO
278 CREATE LOGIN login_test WITH PASSWORD = '123456' --MUST_CHANGE,CHECK_EXPIRATION = ON;
279 GO
280 GRANT VIEW SERVER STATE TO login_test;
281 GO
282 CREATE TRIGGER connection_limit_trigger
283 ON ALL SERVER WITH EXECUTE AS 'sa'   ---这里都要为sa用户,如果是login_test 他自己不能判断自己
284 FOR LOGON
285 AS
286 BEGIN
287 IF ORIGINAL_LOGIN()= 'login_test' AND
288     (SELECT COUNT(*) FROM sys.dm_exec_sessions
289             WHERE is_user_process = 1 AND
290                 original_login_name = 'login_test') > 1
291     ROLLBACK;
292 END;
293 
294 
295 USE MASTER
296 go
297 
298 DROP TRIGGER connection_limit_trigger
299 ON ALL SERVER
300 go
301 
302 DROP USER login_test
303 GO
304 
305 -------------------------------------------------------------------------------
306 --查看导致触发器触发的事件
307 --
308 --
309 --以下示例将查询 sys.triggers 和 sys.trigger_events 目录视图,以确定是哪个 Transact-SQL 语言事件导致触发了 safety。safety 是在前一个示例中创建的
310 
311 
312 USE pratice
313 GO
314 
315 SELECT TE.*
316 FROM sys.trigger_events AS TE
317 JOIN sys.triggers AS T
318 ON T.object_id = TE.object_id
319 WHERE T.parent_class = 0
320 AND T.name = 'db_trg_RestrictINDEXChanges'
321 GO
322 
323 SELECT *  FROM sys.triggers
324 SELECT *  FROM sys.trigger_events
325 
326 
327 
328 ----------------------------邀月博客------------------------------
329 --------/***************
330 --------在目标数据库服务器上创建一个触发器,以防止添加登录账号,
331 --------3w@live.cn
332 --------*******************/
333 USE master
334 GO
335 -- Disallow new Logins on the SQL instance
336 CREATE TRIGGER srv_trg_RestrictNewLogins
337 ON ALL SERVER
338 FOR CREATE_LOGIN
339 AS
340 PRINT'No login creations without DBA involvement.'
341 ROLLBACK
342 GO
343 
344 --------/***************
345 --------试图创建一个登录账号
346 --------3w@live.cn
347 --------*******************/
348 CREATE LOGIN johny WITH PASSWORD ='123456'
349 GO
350 
351 --------/***************
352 --------删除演示触发器
353 --------3w@live.cn
354 --------*******************/
355 
356 DROP TRIGGER srv_trg_RestrictNewLogins
357 ON ALL SERVER
358 go
359 
360 
361 ------------------------------------------------------------------------------
362 
363 
364 --创建一个DDL触发器审核数据库级事件
365 /***************
366 创建一个审核表,其中EventData是一个XML数据列
367 w@live.cn
368 *******************/
369 --这个表应该在master库中创建,这里为了演示创建在[pratice]库
370 
371 USE [pratice]
372 GO
373 CREATE TABLE dbo.ChangeAttempt
374 (EventData xml NOT NULL,
375 AttemptDate datetime NOT NULL DEFAULT GETDATE(),
376 DBUser char(50) NOT NULL)
377 GO
378 
379 SELECT * FROM [dbo].[ChangeAttempt]
380 /***************
381 在目标数据库上创建一个触发器,以记录该数据库的索引变化动作,
382 包括Create|alter|Drop
383 w@live.cn
384 *******************/
385 USE [pratice]
386 GO
387 
388 CREATE TRIGGER db_trg_RestrictINDEXChanges  ON DATABASE
389 FOR CREATE_INDEX, ALTER_INDEX, DROP_INDEX
390 AS
391 SET NOCOUNT ON
392 INSERT dbo.ChangeAttempt
393 (EventData, DBUser)
394 VALUES (EVENTDATA(), USER)
395 GO
396 
397 /***************
398 创建一个索引,以测试触发器
399 w@live.cn
400 *******************/
401 USE [pratice]
402 GO
403 
404 CREATE NONCLUSTERED INDEX ni_ChangeAttempt_DBUser ON [pratice].[dbo].[test]([a])
405 GO
406 --
407 --/***************
408 --查看审核记录
409 --w@live.cn
410 --*******************/
411 --
412 USE [pratice]
413 GO
414 
415 SELECT * FROM [dbo].[ChangeAttempt]
416 --
417 --
418 
419 ------/***************
420 ------删除测试触发器和记录表
421 ------3w@live.cn
422 ------*******************/
423 USE [pratice]
424 GO
425 
426 drop TRIGGER db_trg_RestrictINDEXChanges
427 ON DATABASE
428 go
429 drop table dbo.[ChangeAttempt]
430 GO
431 -----------------------------------------------------------------------------------------
432 --创建一个登录触发器审核登录事件
433 --------------------------------------------------------
434 --------/***************
435 --------创建登录账号
436 --------3w@live.cn
437 --------*******************/
438 
439 CREATE LOGIN nightworker WITH PASSWORD ='123b3b4'
440 GO
441 
442 --------/***************
443 --------演示数据库和审核表
444 --------3w@live.cn
445 --------*******************/
446 
447 CREATE DATABASE ExampleAuditDB
448 GO
449 USE ExampleAuditDB
450 GO
451 
452 CREATE TABLE dbo.RestrictedLogonAttempt
453 (LoginNM sysname NOT NULL,
454 AttemptDT DATETIME NOT NULL)
455 GO
456 
457 --------/***************
458 --------创建登录触发器,如果不是在7:00-17:00登录,则记录审核日志,并提示失败
459 --------3w@live.cn
460 --------*******************/
461 
462 USE [ExampleAuditDB]
463 GO
464 CREATE TRIGGER trg_logon_attempt
465 ON ALL SERVER
466 WITH EXECUTE AS'sa'  --这里都要为sa用户,如果是nightworker 他自己不能判断自己
467 FOR LOGON,ALTER_LOGIN
468 AS
469 BEGIN
470 IF ORIGINAL_LOGIN()='nightworker'AND
471 DATEPART(hh,GETDATE()) BETWEEN 7 AND 17
472 BEGIN
473 ROLLBACK
474 INSERT ExampleAuditDB.dbo.RestrictedLogonAttempt
475 (LoginNM, AttemptDT)
476 VALUES (ORIGINAL_LOGIN(), GETDATE())
477 END
478 END
479 GO
480 
481 --------/***************
482 --------查看审核记录
483 --------3w@live.cn
484 --------*******************/
485 USE ExampleAuditDB
486 GO
487 SELECT * from dbo.RestrictedLogonAttempt
488 go
489 
490 --------/***************
491 --------删除演示数据库及演示触发器
492 --------3w@live.cn
493 --------*******************/
494 USE [ExampleAuditDB]
495 go
496 
497 DROP TRIGGER trg_logon_attempt
498 ON ALL SERVER
499 go
500 
501 DROP database ExampleAuditDB
502 go
503 
504 DROP USER nightworker
505 GO
506 
507 
508 
509 
510 
511 
512 登录过,改过密码记录日志
513 
514 
515 
516 
517 
518 SELECT  *
519 FROM    sysobjects
520 WHERE   xtype = 'TR'
521         AND parent_obj = OBJECT_ID('表名')
522 
523 SELECT  *
524 FROM    sysobjects
525 WHERE   xtype = 'TR'
526  
527  
528 SELECT  *
529 FROM    sysobjects
530 WHERE   id IN ( SELECT  parent_obj
531                 FROM    sysobjects
532                 WHERE   xtype = 'TR'
533                         AND name = 'db_trg_RestrictINDEXC')
534      
535 --触发 DDL 触发器的 DDL 事件                  
536 -- http://technet.microsoft.com/zh-cn/library/ms189871(v=sql.90).aspx
537 
538 
539 --设计 DDL 触发器
540 --http://technet.microsoft.com/zh-cn/library/ms186406(v=sql.90).aspx

 

posted @ 2013-08-02 22:13 桦仔 阅读(...) 评论(...) 编辑 收藏