none
使用Event Notification 替代Logon Trigger (SQL Server 2005及以上版本) RRS feed

  • 问题

  • 在SQL Server实际应用中,会使用Logon Trigger 在去捕获一些登录信息,如:登录名,登录时间,计算机名等,把这些信息插入到自定义的用户表中。在Logon Trigger中,可以执行一些操作,如判断当前登录用户是否合法,若不合法则ROLLBACK,取消其登录资格。此时,无法捕获当前非法的登录信息,因为这个“捕获”的动作也一起被ROLLBACK了。

    SQL Server 2005开始,增加了Service Broker这个新特性。它是数据库引擎的一部分,提供一个基于消息的通信平台。同时,Event notifications 这个新特性,可以对各种 Transact-SQL 数据定义语言 (DDL) 语句和 SQL 跟踪事件做出响应,并将这些事件的相关信息发送到 Service Broker 服务。与触发器不同的是,Event notifications 在事物范围以外异步运行。话句话说,如果在Event notifications里面定义了事件,它不会随着事物的回滚而回滚。因此,在SQL Server 2005及以上版本,可以使用Event notifications代替Logon Trigger。具体实例如下。
    2011年7月12日 0:40
    版主

答案

  • --新建demo数据库
    CREATE DATABASE Logging_demo
    GO
    
    --启用 service broker
    ALTER DATABASE Logging_demo SET ENABLE_BROKER
    GO
    
    --新建存放登录日志的用户表
    USE Logging_demo
    
    CREATE TABLE Logging (
    LoggingID int IDENTITY(1,1),
    EventTime DATETIME,
    EventType VARCHAR(100),
    LoginName VARCHAR(100),
    HostName VARCHAR(100),
    NTUserName VARCHAR(100),
    NTDomainName VARCHAR(100),
    Success INT,
    FullLog XML
    )
    GO
    
    
    /*
    使用Event notifications (以及 Service Broker) 需要用到3个重要的对象: Queues, Services and Routes。
    具体信息请参考SQL Server联机文档中的介绍
    */
    
    --创建 queue
    CREATE QUEUE LoggingQueue
    GO
    
    --创建 service
    CREATE SERVICE LoggingService
    ON QUEUE LoggingQueue
    ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification])
    GO
    
    --创建 route
    CREATE ROUTE LoggingRoute
    WITH SERVICE_NAME = 'LoggingService',
    ADDRESS = 'LOCAL'
    GO
    
    --为当前数据库在AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED事件上创建EVENT NOTIFICATION
    CREATE EVENT NOTIFICATION Logging_Event_Notification
    ON SERVER
    FOR AUDIT_LOGIN, AUDIT_LOGOUT, AUDIT_LOGIN_FAILED
    TO SERVICE 'LoggingService', 'current database'
    GO
    
    /*
    现在,event事件中捕获的所有信息都会被保存在一个XML类型的变量中。使用下面的存储过程,从这个XML变量中读取信息
    并插入到前面定义的用户日志表中。
    */
    
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    CREATE PROCEDURE LoggingProc
    AS
    SET NOCOUNT ON;
    
    DECLARE @message_body XML,
      @message_type_name NVARCHAR(256),
      @dialog UNIQUEIDENTIFIER ;
    
    --Endless loop
    WHILE (1 = 1)
    BEGIN
     BEGIN TRANSACTION ;
    
     -- Receive the next available message
    
     WAITFOR (
     RECEIVE TOP(1)
      @message_type_name=message_type_name,
      @message_body=message_body,
      @dialog = conversation_handle
      FROM LoggingQueue
     ), TIMEOUT 2000
    
    	--Rollback and exit if no messages were found
    	IF (@@ROWCOUNT = 0)
     BEGIN
      ROLLBACK TRANSACTION ;
      BREAK ;
     END ;
    
     --End conversation of end dialog message
    
     IF (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
     BEGIN
     PRINT 'End Dialog received for dialog # ' + cast(@dialog as nvarchar(40)) ;
     END CONVERSATION @dialog ;
     END ;
     ELSE
     BEGIN
    
    
    	INSERT INTO Logging (
    	EventTime,
    	EventType,
    	LoginName,
    	HostName,
    	NTUserName,
    	NTDomainName,
    	Success,
    	FullLog	)
    	VALUES
    	(
    	CAST(CAST(@message_body.query('/EVENT_INSTANCE/PostTime/text()') AS VARCHAR(64)) AS DATETIME),
    	CAST(@message_body.query('/EVENT_INSTANCE/EventType/text()') AS VARCHAR(100)),
    	CAST(@message_body.query('/EVENT_INSTANCE/LoginName/text()') AS VARCHAR(100)),
    	CAST(@message_body.query('/EVENT_INSTANCE/HostName/text()') AS VARCHAR(100)),
    	CAST(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') AS VARCHAR(100)),
    	CAST(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') AS VARCHAR(100)),
    	CAST(CAST(@message_body.query('/EVENT_INSTANCE/Success/text()') AS VARCHAR(64)) AS INTEGER),
    	@message_body)
    END
    
    COMMIT TRANSACTION
    END
    GO
    
    --查询 QUEUE 中的日志记录
    select * from LoggingQueue
    
    --最后,将 QUEUE与存储过程关联起来,使得SQL Sever能自动将捕获到的信息添加的指定的用户日志表中
    ALTER QUEUE LoggingQueue
    WITH ACTIVATION (
    STATUS = ON,
    PROCEDURE_NAME = LoggingProc ,
    MAX_QUEUE_READERS = 2, EXECUTE AS SELF )
    
    --再次查询 QUEUE,此时已经无记录 
    SELECT * FROM LoggingQueue
    
    --查询自定义用户日志表查看结果 SELECT * FROM Logging



    2011年7月12日 0:41
    版主