主页 / dba / 问题 / 171562
Accepted
Peter Vandivier
Asked: 2017-04-21 12:58:13 +0800 CST

服务帐户的跨数据库模拟失败

  • 772

我想启用一个将大量数据转储到暂存区的应用程序，以便能够启动将数据转换并加载到生产区的存储过程。如果可能，我不想授予应用程序对生产区域的任何访问权限。不幸的是，EXECUTE AS似乎让我失望了。作为参考，有问题的数据库正在运行2008 R2，但我正在我的2016本地主机上重新创建行为。同样，有问题的服务帐户是一个域凭据，但我已经使用 SQL Auth 凭据重新创建了行为以实现可移植性。

设置

use [master]
create database Prod;
create database Stage;
create login ServiceAccount with password='Password1234';
go
use Prod
create table tbl1 ( i int );
insert tbl1 select 1;
go
use Stage
create user ServiceAccount for login ServiceAccount; 
go
create proc spLoadStageToProd as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go
grant execute on spLoadStageToProd to ServiceAccount;
go

测试

从Stage数据库，exec spLoadStageToProd;成功。不出所料，exec as login = 'ServiceAccount'; exec spLoadStageToProd;失败并出现错误...

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 21]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

好的，所以我会尝试execute as owner让 proc 拥有完成工作所需的权限，但（拥有密码的开发人员）ServiceAccount不会在我的生产区域乱搞。

alter proc spLoadStageToProd
with execute as owner
as
begin
    insert Prod.dbo.tbl1(i)
    select checksum(newid())%100; -- random number
end;
go

射击！那么现在 Staging of 的一个简单测试exec spLoadStageToProd;返回错误：

Msg 916, Level 14, State 1, Procedure spLoadStageToProd, Line 5 [Batch Start Line 35]
The server principal "DOMAIN\peter" is not able to access the database "Prod" under the current security context.

"DOMAIN\peter"当然是我：与sysadmin服务器角色的愚蠢...

嗯，这很奇怪……我试过alter authorization on object::spLoadStageToProd to dbo;了，但弹出了同样的错误（消息 916）。更何况……exec as login = 'ServiceAccount'; exec spLoadStageToProd;现在又回来了……

Msg 229, Level 14, State 5, Procedure spLoadStageToProd, Line 1 [Batch Start Line 37]
The EXECUTE permission was denied on the object 'spLoadStageToProd', database 'Stage', schema 'dbo'.

算了！alter authorization on object::spLoadStageToProd to sa;...

Msg 15151, Level 16, State 1, Line 41
Cannot find the user 'sa', because it does not exist or you do not have permission.

...

...

……伤心。

所以......显然我误解了一些基本的东西。我尝试启用跨数据库所有权链接，以防万一这是粘性检票口但没有运气。在毫无结果地谷歌搜索并快速浏览了这 两个dba.se 问题之后，我陷入了困境。我哪里错了？

我如何让这个 SProc 在提升的跨数据库权限下运行，而不管是谁调用它？

引发这个问题的项目是一个迭代弃用，所以我认为随着时间的推移，证书签名最终可能会成为一个相当大的管理开销。

如果可以使用此存储过程作为包装器来执行与调用它的人无关的受限操作，这就是最终目标。

我相信 SProc 不会被不当修改，因此从其中执行的任何内容都可能获得高度许可 - 希望我不需要ServiceAccount逐一管理权限。

礼貌清理脚本

revert;
use [master]
go
drop database Prod,Stage;
drop login ServiceAccount;
go
sql-server permissions

1 个回答

  1. Best Answer

    正确：默认情况下，模拟 /EXECUTE AS被隔离到初始数据库。为了解决这个问题，您需要将初始数据库设置为TRUSTWORTHY ON（不是一个很好的选择，因此应该被视为最后的手段），或者创建一个可以同时存在于初始数据库和目标数据库中的证书用于链接所需的权限（然后您根本不需要模拟）。

    我在 DBA.StackExchange 上的各种答案中有几个这样做的例子。先试试这些：

    现在，考虑到问题中添加的新信息：

    引发这个问题的项目是一个迭代弃用，所以我认为随着时间的推移，证书签名最终可能会成为一个相当大的管理开销。

    和：

    如果可以使用此存储过程作为包装器来执行与调用它的人无关的受限操作，这就是最终目标。...我相信 SProc 不会被不当修改，因此从其中执行的任何内容都可能获得高度许可 - 希望我不需要ServiceAccount逐个管理权限。

    可以缩小选择的方向。通常目标是尽可能细化权限，因此有时需要一些额外的步骤才能达到最低权限设置。但这并不意味着您不能在情况需要时采取更少的步骤来降低粒度。

    模块签名的（几个）精彩方面之一是您有效地授予代码权限，而不是人。因此，您可以通过对正在签名的任何代码（即存储过程、函数、触发器等）正确设置权限来控制谁可以使用这些权限。

    假设您需要服务器/实例级和数据库级权限，即使您最终没有使用大部分允许的权限，您也可以执行以下操作：

    1. 在[master].
    2. 从该证书创建登录。
    3. 将基于证书的登录添加到sysadmin固定服务器角色。
    4. 切换到Stage数据库
    5. 在此数据库中重新创建相同的证书（通过将其备份到磁盘上的文件或通过将证书和私钥提取到变量或本地临时表，如我的其他答案所示）。
    6. 使用此证书通过 对存储过程进行签名ADD SIGNATURE。

    而已！存储过程，不管是谁执行的，都可以做任何它被编码要做的事情。如果该代码发生变化，您将需要ADD SIGNATURE再次执行此操作。但是基于证书的登录不能被模拟，所以没有人可以用它来获得“sa”权限。而且，如果您在创建证书时使用“按密码加密”并且不将该密码提供给任何人，那么任何人都无法使用它签署其他模块。

    在此设置中，唯一正在进行的管理是重新签署存储过程（如果您不断更改它）。

    此外，关于您的最终尝试（即alter authorization on object::spLoadStageToProd to sa;）：这不起作用，因为“sa”是登录名（实例/服务器级），而不是用户（数据库级）。因此，您只能将数据库级项目的权限授予在sys.database_principals.

    • 1

相关问题