动态SQL的存储过程在SQL Server中的权限问题

Question

我有一个带有应用程序角色的数据库。所有角色成员都属于Active Directory中的一个组。我没有给角色在表上选择的权限，而是给予了它调用所需存储过程的执行权限。

这种方法效果很好，除了其中一个存储过程正在构建一些动态SQL并调用sp_executesql。

动态SQL看起来差不多是这样的：

SET @SQL = N'
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

这个角色的用户未能调用存储过程。它会给出以下错误，我认为这是可以预料的：

“拒绝对对象'uvView1'，数据库'Foobar'，架构'dbo'的SELECT权限。”

我是否能让我的用户成功执行此过程，而不必给予该角色对动态SQL中所有视图的权限？

- Dismissile

---

用户正在访问视图，对吧？我认为安全性并不关心他们如何访问它。 - JNK

这是唯一一个失败的存储过程，而且它在存储过程上没有执行权限...它明确告诉我可以从视图中选择...所以我猜这些可能有关系。 - Dismissile

关于为什么会发生这种情况，我在 Dynamic SQL and Ownership Chaining in SQL Server @ mssqltips.com 中找到了答案：“当sp_executesql或EXECUTE语句执行一个字符串时，该字符串将作为自己独立的批次执行。…要求对在EXECUTE字符串中引用的安全对象具有权限。” - Nickolay

Answer 1

可以。

在存储过程中添加一个“EXECUTE AS CALLER”子句，然后对存储过程进行签名并授予 签名 所需的权限。这是100%安全、可靠和防弹的。请参见使用证书签署过程。

- Remus Rusanu

---

使用数据库触发器来监控用户的SQL，如果SQL以“select”开头，则拒绝它，这是一个怎样的解决方案？ - ca9163d9

正如链接文章所述，“WITH EXECUTE AS CALLER”是默认设置。尽管签名过程有些麻烦，但并不太复杂，只需要几个SQL命令（CREATE CERTIFICATE+CREATE USER cert$user FROM CERTIFICATE ..，然后是 ADD SIGNATURE TO @spname BY CERTIFICATE .. 和 GRANT ... TO cert$user）。 - Nickolay

Answer 2

您可以使用模拟用户身份来切换至具备所需权限的另一个ID吗？

SET @SQL = N'
EXECUTE AS USER = ''TrustedUser'';
SELECT * 
FROM dbo.uvView1 
INNER JOIN uvView2 ON uvView1.Id = uvView2.Id'

EXEC sp_executesql @SQL

- Joe Stefanelli

---

好的建议，我会看看能否实现。 - Dismissile

-1：需要在“TrustedUser”上具有IMPERSONATE权限，这实际上意味着用户可以随意模拟受信任的用户，因此具有所有权限。 - Remus Rusanu

Answer 3

不行。你有没有办法改成不使用动态SQL的方式？

- Vidar Nordnes

---

不是的...就我所知没有。该查询实际上在对一些数据进行透视，而它透视的列是动态的。我的示例并不能真正展示查询的复杂性。 - Dismissile

你可以考虑将所有这些视图放在一个架构中，并给予它们对该架构的数据阅读器访问权限。 - K Richard