您面临的问题是模拟用户(即通过
EXECUTE AS
切换安全上下文)的限制。对于数据库范围对象,例如存储过程,您在
EXECUTE AS
子句中指定了一个数据库级别的
user
,而不是服务器级别的
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
步骤二:创建登录并将其添加到sysadmin
角色中:
CREATE LOGIN [BackupRestoreOps] FROM CERTIFICATE [BackupRestoreCert];
ALTER SERVER ROLE [sysadmin] ADD MEMBER [BackupRestoreOps];
第三步:使用该证书签署存储过程,创建到新登录的链接:
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之上有任何步骤,如果传递了除数据库名称以外的任何内容,它们将出错,那么很好,但这仍然需要针对那些可能试图复制并粘贴而不理解完整上下文的其他人进行说明。
应该做两件事情:
- 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]
.
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;
我尝试寻找另外一种服务器角色,以允许不使用如此特权的角色实现这一点。我已经尝试了
dbcreator
、
securityadmin
、
serveradmin
和
setupadmin
,但不幸的是,它们都没有起作用 :(。
EXECUTE AS
)及其限制有关。我正在努力回答。顺便问一下,他们正在审查这个过程吗?你不能通过SQL注入授予自己“sysadmin”服务器角色吗?;-) - Solomon Rutzky