SQL Server:存储过程的EXECUTE AS子句未授予sysadmin权限 [英] SQL Server: EXECUTE AS clause of stored procedure not granting sysadmin permissions

问题描述

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

I developped a stored procedure in order to restore a database from a backup file and add an application user to it. This stored procedure belongs to the master database.

问题是我的IT部门不允许我使用admin用户，而只能对sysadmin用户使用EXECUTE AS语句.

The issue is that my IT department does not allow me to use an admin user, only to use an EXECUTE AS statement with a sysadmin user.

我可以还原数据库，但是找不到在过程结束时添加用户的方法.

I can restore the database but I can't find a way to add the user at the end of the process.

我使用的代码:

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

运行它时，出现以下错误消息:

When I run it, I have the following error message:

服务器主体"aSysAdminUser"无法执行 在当前安全上下文下访问数据库数据库".

The server principal "aSysAdminUser" is not able to access the database "database" under the current security context.

有什么想法可以使用EXECUTE AS语句从主数据库上的存储过程中以动态参数数据库名称创建用户吗?

Any idea how I could create a user in a dynamic parameter db name from a stored procedure on master db using a EXECUTE AS statement?

谢谢

推荐答案

您面临的问题是模拟的局限性(即通过EXECUTE AS切换安全上下文).对于诸如存储过程之类的数据库范围的对象，您要在EXECUTE AS子句中指定数据库级别的user，而不是服务器级别的login.因此，此过程实际上不充当sysadmin.但是，有一种方法可以安全地授予此存储过程真正的sysadmin权限，该权限将允许它执行动态SQL中的步骤，即:

The issue you are facing is a limitation of Impersonation (i.e. switching the security context via EXECUTE AS). For database-scoped objects, such as stored procedures you are specifying a database-level user in the EXECUTE AS clause, not a server-level login. Hence, this procedure is not really acting as a sysadmin. But, there is a way to safely grant just this one stored procedure true sysadmin permissions that will allow it to do the steps in your dynamic SQL, namely:

1. 连接到数据库
2. 创建用户
3. 更改角色

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

The way to do this is through signing the stored procedure with a certificate. The certificate will then also be used to create a server-level login that will be added to the sysadmin server role. Then, when any user / login (that has EXECUTE permission on this stored procedure) executes this procedure, it will pick up the permissions of the certificate-based login merely by being signed by the same certificate.

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

Step 1: Set up the certificate in the [master] database:

USE [master];
GO

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

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

Step 2: Create the login and add to the sysadmin role:

CREATE LOGIN [BackupRestoreOps] FROM CERTIFICATE [BackupRestoreCert];

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

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

Step 3: Sign the stored procedure with that certificate, creating the link to the new login:

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

以上所有内容实际上都是在[master]数据库的上下文中完成的，尽管将其拆散的解释似乎更具可读性.但是，实际上，这是IT人员运行一次的单个脚本.当然，如果您曾经ALTER该存储过程，他们将不得不再次运行ADD SIGNATURE命令(即步骤3)，因为对过程定义的任何更改都会丢失该命令.

All of the above is actually done within the context of the [master] database, though it seemed more readable for explanation to have it broken up. But, in practice it would be a single script that the IT folks run once. Of course, if you ever ALTER that stored procedure, they will have to run the ADD SIGNATURE command again (i.e. Step 3) as it is lost upon any changes to the definition of the procedure.

就是这样.我已经用问题中显示的存储过程对其进行了测试，添加签名和sysadmin角色之后，它确实创建了用户并将其添加到db_owner角色.

And that is that. I have tested this with the stored procedure shown in the question and it did, once I added the signature and the sysadmin role, create the user and add it to the db_owner role.

第4步(也许)::您可能会从存储过程中删除EXECUTE AS子句.如果它确实传递了任何允许初始还原操作正常运行的权限，则现在应通过基于证书的登录名(被标记为sysadmin)来假定这些权限.

Step 4 (maybe): You could probably remove the EXECUTE AS clause from the stored procedure. If there were any permissions that it did pass on that allowed the initial restore operation(s) to work, those should be now assumed via the certificate-based login as it is marked as sysadmin.

免责声明
我只想/需要弄清楚，理解为，给定问题中指定的存储过程(编写时)，一旦授予此权限，将允许进行SQL注入.当然，这已经是IT员工决定使用EXECUTE AS子句对此存储过程授予sysadmin权限的初衷.我现在要指出的是，上述步骤实际上将使他们预期的行为成为现实.如果在动态SQL之上存在某些步骤，如果传入了数据库名称以外的任何内容，将会出错，那么很好，但是对于其他可能希望重复此操作而只是复制/粘贴而又不了解其完整内容的其他人，仍然需要说明这一点.上下文.

DISCLAIMER
I just want / need to make clear that it is understood that, given the stored procedure specified in the question, as it is written, will allow for SQL Injection once this permission is granted. This, of course, was already a consequence of the original intention of the IT staff's decision to use the EXECUTE AS clause to grant sysadmin permissions to this stored procedure. I am just pointing it out now that the above steps will actually make their intended behavior a reality. If there are steps above the dynamic SQL that would error if anything other than a database name were passed in, then great, but this still needs to be stated for others who might be looking to duplicate this and just copying / pasting without understanding the full context.

应该完成两件事:

1. 虽然是次要点，但输入参数的数据类型应为sysname(NVARCHAR(128)的别名)，因为这是在[sys].[databases]中定义[name]的方式.

2. 主要要做的是验证传入的值是现有的数据库名称，类似于:

1. While a minor point, the datatype of the input parameter should be sysname (alias for NVARCHAR(128)) as that is how [name] is defined in [sys].[databases].

2. The main thing to do is verify that the value passed in is an existing database name, something along the lines of:

IF (DB_NAME(@database) IS NULL)
BEGIN
   RAISERROR(N'Invalid Database Name', 16, 1);
   RETURN;
END;

我试图找到另一个服务器角色，该服务器角色不允许使用sysadmin，而不希望使用这种特权角色.我已经尝试过dbcreator，securityadmin，serveradmin和setupadmin，但是不幸的是，它们都不起作用:(.

I have tried finding another server role that would allow for this that is not sysadmin in the hopes of not using such a privileged role. I have tried dbcreator, securityadmin, serveradmin, and setupadmin, but unfortunately none of them worked :(.

这篇关于SQL Server:存储过程的EXECUTE AS子句未授予sysadmin权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！