主页 / dba / 问题 / 290923
Accepted
Aleksey Vitsko
Asked: 2021-05-04 08:06:22 +0800 CST

SQL Server 登录触发器含义 - 在我的场景中是否有任何副作用？

  • 772

我团队中的一些开发人员知道具有扩展权限的 SQL 帐户的密码
我们希望在任何开发人员使用任何这些 SQL 帐户进行连接时进行跟踪并收到警报

我们即将logon trigger为每次登录尝试实施该功能，评估登录的属性，并在它们符合特定条件时发送电子邮件报告。逻辑如下：

如果 original_login() in (...SQL accounts list here...) 并且：
a) 客户端 IP 地址是 192.168.xx VPN 子网（没有生产应用程序从该子网连接，只有开发人员可以）或
b) 客户端主机名（... Dev 的主机名称列表...）或
c）（SSMS、az-Data 等）中的客户端应用程序名称
exec sp_send_dbmail（通过电子邮件向 DBA 发送报告）

触发器将具有“执行为”子句并代表 SQL 登录名运行，该登录名的唯一权限是发送 db 邮件电子邮件

在 Production 上启用这种登录触发器会产生什么不良副作用？
它会减慢登录过程或导致任何其他问题吗？

ps 我知道DAC以及如何使用它。
测试使用连接DAC并依靠它来帮助我在出现任何问题时禁用触发器

sql-server sql-server-2017

3 个回答

  1. Best Answer

    数据库邮件使用 Service Broker 队列和异步后台进程来实际发送电子邮件，因此性能应该不是什么大问题。但是登录触发器很容易导致停机，因此在编写和测试时需要非常小心。如果开发人员运行负载测试或类似的东西，您最终可能会收到数千封电子邮件。

    因此，为此使用登录触发器可能有点过头了。而是使用审计甚至只是一个 XEvent 会话。将数据写入事件文件并使用计划作业对其进行处理。

    以下是创建和查询审计的方法：

    --ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = OFF);
--DROP SERVER AUDIT [sa_successful_logon]
go

declare @logFolder nvarchar(255) = ( select [path]  from sys.dm_os_server_diagnostics_log_configurations )
exec('
CREATE SERVER AUDIT [sa_successful_logon]
TO  FILE ( FILEPATH = '''+@logFolder+''' )
WHERE ([server_principal_name]=''sa'');
ALTER SERVER AUDIT [sa_successful_logon] WITH (STATE = ON);
')
GO

CREATE SERVER AUDIT SPECIFICATION [sa_successful_logon_logon_spec]
FOR SERVER AUDIT [sa_successful_logon]
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)

go

declare @log nvarchar(255) = ( select [path] + 'sa_successful_logon*.sqlaudit' from sys.dm_os_server_diagnostics_log_configurations ) 
SELECT event_time, client_ip, application_name
FROM sys.fn_get_audit_file (@log,default,default);
    • 13

  2. 一个方面可能不是您所要求的暗示类型，但 IMO 同样重要的是：

    应用程序名称和主机名由客户端应用程序设置。因此，您的开发人员可以使用 SSMS 轻松连接到您的 SQL Server，并在连接字符串中指定如下内容：

    应用程序名称=MyAppName；工作站 ID=MickeyMouse

    （您可以在 SSMS“连接”对话框中指定连接字符串属性。）

    我不能确定 IP 地址是否“安全”使用。

    • 1

  3. 你可以试试这个触发器：

    CREATE TRIGGER [logon_trigger_not_from_SSMS] 
ON ALL SERVER FOR LOGON
AS
  BEGIN
    IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%' --app name check
      BEGIN
      --the only persons allowed to use SSMS
        IF suser_name() not IN ('sa','DBA_LOGINS') 
        and suser_name() not like 'dev[_]%' 
                        
             BEGIN
                ROLLBACK --Logins other than above mention logins are not allowed to connect through SSMS
             END 
      END 
                  
       
  END
    • 0

相关问题