时间戳

首页 > 解决方案 > 如何授予用户角色具有授予选项的权限？

sql-server - 如何授予用户角色具有授予选项的权限？

问题描述

我正在直接在 SQL Server 2017 上为我公司软件的新版本设置安全登录系统。我正在尝试撤销用户帐户对任何数据库的 CONNECT 权限，直到用户使用设置的用户名登录到初始身份验证数据库/password，并使用登录过程在特定数据库上启用他们的帐户，然后客户端将断开与初始数据库的连接并使用他们的个人帐户重新连接，连接到他们的目标数据库。

在初始数据库中创建了一个证书，用于对调用如下所示的过程进行签名。下面显示的过程没有任何证书签名。创建的证书被复制到目标数据库，并用于创建一个用户，然后将该用户添加到一个组中Certified，该用户有权执行下面显示的过程，并且CONNECT WITH GRANT OPTION对数据库具有权限。当下面的过程被执行时，grant/revoke 语句失败，说授予者没有必要的权限（或者找不到用户/角色，取决于我是否尝试添加 AS 子句）

连接权限的授予/撤销是通过目标数据库中存储过程中的动态 SQL 完成的（我们软件中的每个数据库都有一个相同的过程来完成这项工作）。如果我以 sysadmin 固定角色的成员身份运行代码，则它可以工作，但当我使用我设置的身份验证帐户运行它时，它就不行了。目标数据库中的ModifyUser过程（但是，每当我运行该过程时，该语句都会失败。CertifiedCONNECT WITH GRANT OPTION

我尝试了 3 个版本，更改了 AS 子句的内容：

  1. 作为证书用户，他是数据库角色的一部分，uCompCompID
  2. 作为数据库角色，证书用户是其中的一部分，Certified
    • SQL Server 文档似乎不清楚在尝试授予角色具有授予选项的权限时应该使用哪些
  3. 不带AS子句，以授予运行动态SQL的用户权限
    • 这似乎以身份验证用户身份运行查询，而不是作为具有授予选项的角色成员的证书用户CONNECT

ModifyUser过程很短，所以我将在下面包括整个过程。此过程存储在与我要授予/撤销CONNECT权限的用户相同的数据库中，但调用由与在同一数据库中创建的用户匹配的证书签名的不同数据库。

这是版本 3，其中没有 AS 子句。

PROCEDURE [Authorization].[ModifyUser]
    @user nvarchar(128),
    @status bit
AS
BEGIN
    SET NOCOUNT ON

    IF @user IS NULL
        THROW 50002, 'Invalid argument: @user', 0
    IF LTRIM(RTRIM(@user)) = ''
        THROW 50002, 'Invalid argument: @user', 1

    IF @status IS NULL
        THROW 50002, 'Invalid argument: @status', 0

    DECLARE @statement nvarchar(200)
    IF @status = 1
        SET @statement = 'GRANT CONNECT TO ' + @user
    ELSE
        SET @statement = 'REVOKE CONNECT TO ' + @user

    EXEC (@statement)
END

预期的结果是CONNECT目标用户的权限发生了变化，但接收到的结果始终是错误的。确切的错误取决于使用的版本：

  1. 消息 15151，级别 16，状态 1，行 2 找不到用户“uCompCompID”，因为它不存在或您没有权限。
  2. 消息 15151，级别 16，状态 1，行 2 找不到用户“已认证”，因为它不存在或您没有权限。
  3. Msg 4613，Level 16，State 1，Line 2 Grantor 没有 GRANT 权限。

如果我直接授予证书用户权限，这不是问题。但是，我想将权限保留在一个角色中，这样当我在修改该系统所涉及的程序之一后不可避免地需要重新创建证书时，我只需要担心将新证书用户添加到适当的组，而不是每次进行需要重新签署任何证书的更改时都需要向重新创建的用户授予权限。

标签： sql-serverstored-procedures

解决方案

首先，您应该更新您的问题，因为不清楚您创建了哪些数据库cerificate以及签署了哪些程序，哪些不是：

目标数据库中的过程 (ModifyUser) 是从初始数据库中的另一个过程调用的，该过程使用证书签名，目标数据库中的同一证书中存在一个用户，该用户是已获得认证的角色的成员。授予 CONNECT WITH GRANT OPTION

由此看来，只有初始数据库中的过程是用 a 签名的 certificate，但内部过程（在目标数据库中）不是，在这种情况下，只有外部过程具有授予从证书创建的用户的权限，这就是为什么你得到错误 N3

Grantor 没有 GRANT 权限

在其他情况下，您会因为使用execute as子句而出错（该子句只承认user，而不承认login！）。当你使用它时，你的 proc 被沙箱化在database它创建的过程中，即你不能在另一个数据库中做任何事情，即使你是sysadmin，服务器也不能使用对应的login（在其他数据库中搜索对应user的），除非数据库是trustworthy并且在另一个数据库中owner没有。authenticate

推荐阅读