SQL Server基础之登陆触发器

  虽然同表级(dml)触发器和库级(ddl)触发器共顶着一个帽子，但登陆触发器与二者有本质区别。无论表级还是库级，都是用来进行数据管理的，而登陆触发器是纯粹的安全工具。

  登陆触发器只响应logon事件，在登陆数据库成功后、用户会话未实际建立前触发。登陆数据库失败，如账号密码错误，不会激发登陆触发器。登陆触发器内部的错误消息及print等sql语句输出信息，统一写到sql server 错误日志。登陆触发器可以同时存在多个，但存在多个时,sql server不保证所有触发器的执行顺序，只能使用确定哪一个登陆触发器第一个或最后一个执行。登陆触发器内部执行rollback语句或者发生级别大于20的错误，将会阻止用户继续登陆。

  前面说到登陆触发器是纯粹的安全工具，作为安全工具，登陆触发器使用场景都是与安全有关的。下面列举了登陆触发器使用的4种场景：
1、记录登陆用户的登陆时间、登陆ip等。
2、限制某些用户，如test，在工作时间内不能登陆数据库。
3、限制某些用户，如sa，只能在指定ip或者ip段登陆。
4、限制只有来自指定ip或ip段的登陆可以成功登陆。

登陆触发器语法：

--创建或修改登陆触发器
create [ or alter ] trigger trigger_name   --触发器名字
on all server   
[ with <logon_trigger_option> [ ,...n ] ]  --在实际项目中，多使用管理员账号建立登陆触发器，因而这项可以省略
{ for| after } logon    
as 
  sql_statement   --用户自己编写内容

<ddl_trigger_option> ::=  
    [ encryption ]  
    [ execute as clause ]  

-- with encryption 表示加密该触发器的sql，使用该项后，将无法通过sp_helptext或数据库管理器查看触发器sql。
-- with execute as clause ，示例：with execute as 'sa'，指定触发器使用哪个用户的权限，省略该项，将使用登陆用户的权限。

--删除登陆触发器
drop trigger  trigger_name   on all server  

--查询登陆触发器
select * from sys.server_triggers

 备注：在登陆触发器内部使用函数eventdata()可以获取触发器上下文事件信息，使用函数original_login()可以获取当前登陆的用户名。

--登陆触发器示例
--限制用户test在工作时间（上午7时，晚上18时之间）不能登陆数据库
create trigger tr_xianzhidenglu
on all server
for logon
as 
    declare @xianzaishijian datetime
    set @xianzaishijian=getdate ()

    if original_login()='test' and (datepart(hh,getdate()) >7 and datepart(hh,getdate())<18)
        begin
          rollback 
        end

参考文献：

 

写作时间：2018-10-05

=====================================================================================
本文只代表本人的见解，可能存在错误，仅用于技术交流。如果你喜欢该文，可以扫下面的二维码打赏我（打赏敬请备注“博客园打赏”五字）。