问Server:不授予sysadmin权限的存储过程EXECUTE AS子句
EN

Stack Overflow用户

提问于 2014-10-27 17:05:09

回答 1查看 4.6K关注 0票数 4

我开发了一个存储过程，以便从备份文件中恢复数据库并向其添加应用程序用户。此存储过程属于主数据库。

问题是，我的IT部门不允许我使用管理用户，只允许我对sysadmin用户使用EXECUTE AS语句。

我可以恢复数据库，但无法找到在进程结束时添加用户的方法。

我使用的代码：

CREATE PROCEDURE [dbo].[myProc] 
@database VARCHAR(50)
WITH EXECUTE AS 'aSysAdminUser' 
AS
BEGIN
    --Restore the database (working)
    --Add the application user    (not working)                  
    SET @sqlScript = 'USE '+@database +';
    CREATE USER [myApplicationUser] FROM LOGIN [myApplicationUser];
    EXEC sp_addrolemember ''db_owner'', ''myApplicationUser'''
    EXEC(@sqlScript)
END

当我运行它时，我会收到以下错误消息：

服务器主体"aSysAdminUser“无法在当前安全上下文下访问数据库”数据库“。

知道如何使用EXECUTE AS语句从主db上的存储过程中创建动态参数db名称中的用户吗？

谢谢

sql-server

permissions

sql-server-2008-r2

impersonation

database-permissions

回答 1

Stack Overflow用户

回答已采纳

发布于 2014-10-27 18:53:54

您面临的问题是模拟的限制(即通过EXECUTE AS切换安全上下文)。对于数据库作用域对象，例如存储过程，您要在user子句中指定数据库级login，而不是服务器级login。因此，这个过程实际上并不是一个sysadmin。但是，有一种方法可以安全地授予这个存储过程真正的sysadmin权限，允许它在动态SQL中执行步骤，即：

连接到数据库
创建用户
改变角色

这样做的方法是通过使用证书对存储过程进行签名。然后，证书还将用于创建服务器级登录，该登录将添加到sysadmin服务器角色中。然后，当任何用户/登录(对此存储过程具有EXECUTE权限)执行此过程时，它将仅通过由同一证书签名来获取基于证书的登录的权限。

步骤1:在[master]数据库中设置证书：

USE [master];
GO

CREATE CERTIFICATE [BackupRestoreCert]
    ENCRYPTION BY PASSWORD = N'MyPassword'
    WITH SUBJECT = N'Certificate for Managing Backup/Restore Operation Permissions';
GO

步骤2:创建登录并添加到sysadmin角色：

CREATE LOGIN [BackupRestoreOps] FROM CERTIFICATE [BackupRestoreCert];

ALTER SERVER ROLE [sysadmin] ADD MEMBER [BackupRestoreOps];

步骤3:使用该证书对存储过程签名，创建指向新登录名的链接：

ADD SIGNATURE
    TO [dbo].[myProc]
    BY CERTIFICATE [BackupRestoreCert]
    WITH PASSWORD = 'MyPassword';

以上所有这些实际上都是在[master]数据库的上下文中完成的，尽管对其进行分解似乎更容易理解。但是，在实践中，it人员只运行一次这个脚本。当然，如果ALTER存储过程，它们将不得不再次运行ADD SIGNATURE命令(即步骤3)，因为对过程定义的任何更改都会丢失该命令。

就是这样。我已经用问题中显示的存储过程测试了这一点，一旦我添加了签名和sysadmin角色，就会创建用户并将其添加到db_owner角色中。

步骤4(可能)：您可能可以从存储过程中删除EXECUTE AS子句。如果它传递的任何权限允许初始恢复操作工作，那么现在应该通过基于证书的登录来假设这些权限，因为它被标记为sysadmin。

免责声明

我只想/需要清楚地表明，考虑到问题中指定的存储过程(正如所写的那样)，一旦授予该权限，就会允许SQL注入。当然，这已经是IT人员决定使用EXECUTE AS子句向此存储过程授予sysadmin权限的初衷的结果。现在我只是指出，上述步骤实际上将使他们的预期行为成为现实。如果在动态SQL上有步骤，如果传入了数据库名称以外的任何内容，都会出错，那么很好，但是对于其他可能想要复制它并在不了解完整上下文的情况下复制/粘贴的人来说，仍然需要说明这一点。

应该做两件事：

虽然这是次要的一点，但是输入参数的数据类型应该是sysname (NVARCHAR(128)的别名)，因为这是在[sys].[databases]中定义[name]的方式。
要做的主要工作是验证传入的值是一个现有的数据库名称，如下所示：如果(DB_NAME(@ Database )为NULL)开始RAISERROR(N‘无效数据库名称，16，1)；返回；

编辑：

为了不使用这样一个特权角色，我尝试找到了另一个允许这样做的服务器角色，它不是sysadmin。我试过dbcreator、securityadmin、serveradmin和setupadmin，但不幸的是，它们都没有起作用。