主页 / dba / 问题 / 331611
Accepted
JukEboX
Asked: 2023-09-27 02:51:31 +0800 CST

SQL 触发器调出域\组

  • 772

我正在 SQL Server 2019 中创建一个触发器，以确保用户可以拥有的 SQL Server 连接数量。

我在使用域组获取 SYSDBA 访问权限时遇到问题。我宁愿这样做，而不是识别触发器中的每个用户。

如何识别下面脚本中的组而不是 DOMAIN\sysdba？

我试图设置 DOMAIN\group 以限制它所建立的连接数量，并保持某些 DOMAIN\users 具有不同数量的连接。

添加了下面来自 SergeyA 的代码。但我仍然缺少这里的逻辑。优先CASE ORIGINAL_LOGIN。

CREATE TRIGGER Secure_SQL
 ON ALL SERVER WITH EXECUTE AS 'sa account'
 FOR LOGON
 AS
BEGIN
declare @session_cnt int, @is_sysasmin int
select @session_cnt=count(*) from sys.dm_exec_sessions WHERE
is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()
-- change context to actual user to check if there any sysadmin role
execute as login=original_login()
select @is_sysasmin=count(*) from sys.login_token where name='sysadmin'
REVERT
IF (Select COUNT(1) from sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN*() ) > 
(CASE ORIGINAL_LOGIN()
    WHEN 'sa account' THEN 40
    WHEN 'DOMAIN\sysdba' THEN 150
    WHEN 'DOMAIN\sysdba' THEN 150
    ELSE 1
 END)
    BEGIN
        PRINT 'The login [' + ORIGINAL_LOGIN() + '] has exceeeded its current session limit.'
        ROLLBACK;;
    END
END;
sql-server

2 个回答

  1. 如果您想通过某些组检查登录名是否具有 sysadmin 服务器角色的成员身份，您可以运行

    select * from sys.login_token where name='sysadmin'

    更新。

    问题是：要查看 sys.dm_exec_sessions，帐户需要具有查看服务器状态权限，但 sys.login_token 返回有关您自己的会话的信息。我认为解决方案可能是将上下文更改为 ORIGINAL_LOGIN()

    我应该在给定的脚本中添加这个？

    CREATE TRIGGER Secure_SQL
 ON ALL SERVER WITH EXECUTE AS 'sa account'
 FOR LOGON
 AS
BEGIN

declare @session_cnt int, @is_sysasmin int
select @session_cnt=count(*) from sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()

-- change context to actual user to check if there any sysadmin role
execute as login=original_login()

select @is_sysasmin=count(*) from sys.login_token where name='sysadmin'

revert 

if -- put your logic here
    BEGIN
        PRINT 'The login [' + ORIGINAL_LOGIN() + '] has exceeeded its current session limit.'
        ROLLBACK;;
    END
END;
    • 3
  2. Best Answer

    感谢@SergeyA 对代码和逻辑的支持。这就是我加入这个团队所需的逻辑sysadmin。使用它，我将它用作查询来为其创建一个变量，用户是 a sysadmin。如果是的话，他们应该拥有无限制的访问权限。然后我限制了其他帐户。

    CREATE TRIGGER Secure_SQL
ON ALL SERVER WITH EXECUTE AS 'sa account'
FOR LOGON
AS
BEGIN
    declare @session_cnt int, @is_sysasmin int
    select @session_cnt=count(*) from sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = ORIGINAL_LOGIN()
-- change context to actual user to check if there any sysadmin role
    execute as login=original_login()
    select @is_sysasmin=count(*) from sys.login_token where name='sysadmin'
    REVERT
    IF ($is_sysadmin <> 1) 
        BEGIN
             IF (Select COUNT(1) from sys.dm_exec_sessions WHERE is_user_process = 1    AND original_login_name = ORIGINAL_LOGIN*() ) > 
             (CASE ORIGINAL_LOGIN()
                WHEN 'sa account' THEN 40
                WHEN 'DOMAIN\sysdba' THEN 150
                WHEN 'DOMAIN\sysdba' THEN 150
                ELSE 1
        END)
        BEGIN
                PRINT 'The login [' + ORIGINAL_LOGIN() + '] has exceeeded its current session limit.'
                ROLLBACK;
        END
    END
END
    • 0

相关问题