授权访问INFORMATION_SCHEMA

Question

我们有一个实用程序，通过反射某些视图和存储过程的定义来执行一些SQL操作。为了使其正常工作，代码需要访问INFORMATION_SCHEMA.ROUTINES、INFORMATION_SCHEMA.VIEWS等表中的各个字段。

因此，我们编写了一个游标来应用这些表的信息。

GRANT VIEW DEFINITION on ' + @Name + ' TO tenant'

租户是代码执行角色，@Name 是 proc 名称的游标变量。这很有效。

但随后有人不可避免地会删除/重新创建一个存储过程，从而删除了对该存储过程定义的访问权限，导致魔术工具失败。

那么最好的方法是如何表达“在“租户”角色下执行的所有代码都可以完全读取所有 INFORMATION_SCHEMA 表格？”

编辑：

我尝试了 GRANT VIEW ANY DEFINITION TO tenant，并出现了以下错误：

Cannot find the login 'tenant', because it does not exist or you do not have permission.

这是因为租户是一个角色而不是用户，还是我真的没有权限？我以 sa 身份登录。

- LoveMeSomeCode

Answer 1

非常抱歉要回答自己的问题，但这个似乎有效：

GRANT VIEW DEFINITION TO tenant

这在数据库范围内有效。

GRANT VIEW ANY DEFINITION TO tenant

这段代码应该在服务器范围内运行，但我收到了以下错误信息：“找不到登录名'tenant'，因为它不存在或您没有权限。”这是因为“tenant”是一个角色而不是用户吗？

虽然数据库范围的方法可以满足我的需求，但除了将自己的答案标记为正确外，是否有人能解释一下为什么服务器范围版本无法正常工作？这会带来任何真正的安全威胁吗？

- LoveMeSomeCode

---

GRANT VIEW DEFINITION具有数据库范围，而GRANT VIEW ANY DEFINITION具有SQL Server实例范围（因此该语句必须使用[master]）。数据库角色具有数据库范围，而SQL Server实例范围没有。换言之，不能并且不应该假定数据库角色存在于每个由SQL Server实例拥有的数据库中。 - Bill

Answer 2

如果找不到更好的方法，可以采用一种方法，在用于创建过程的脚本末尾分配权限，例如：

If Exists(...)
 Drop Proc Foo

Create Proc Foo
As


GO

GRANT VIEW DEFINITION on Foo TO tenant
GO

- Conrad Frix

---

这是一个不错的解决方案，但我真的想让它变得傻瓜化。不可避免地，有人会忘记做这件事，导致 SQL 魔法失败。 - LoveMeSomeCode

这就是我们最终做的事情。我想大家每次修改过程时都要记得使用这个模板。谢谢！ - LoveMeSomeCode

Answer 3

在创建存储过程时，如果不是初始实际的创建，请开始使用ALTER PROCEDURE ....，这将保持权限完整。

只有在第一次创建存储过程时才使用CREATE命令，然后始终进行ALTER操作。因此，每次更改时无需删除它（并且失去所有权限）或再次创建它。

编辑
以下是一种可以更改您的存储过程（也适用于视图）编码的方法，以便您始终可以使用ALTER：

BEGIN TRY
    --create a dummy procedure if it doesn't already exist
    EXEC ('CREATE Procedure YourProcedure AS SELECT 1/0')
END TRY BEGIN CATCH END CATCH --ignore the error if it already exits
GO

ALTER Procedure YourProcedure --it will always exist, because of the above code
AS
SELECT 'Hello World!'
GO

使用这种方法，您只需使用ALTER而不是IF EXITS...DROP然后CREATE模式。

- KM.

---

这是个好主意，但这超出了我的控制范围。团队中的其他成员正在使用该方法来修改存储过程。我会询问为什么需要这样做，但我认为这与模式绑定有关。 - LoveMeSomeCode

当将该过程推广到新环境时，这种方法是否存在问题？ - Conrad Frix

@Conrad Frix，请查看我的编辑，了解如何编写过程以避免在初始部署时出现问题。 - KM.

@KM 我喜欢这个想法，但是有人告诉我我们不能使用 ALTER ，因为我们的模式绑定。我们使用的每个对象都是用模式绑定创建的，这样如果它有依赖项，就没有人可以更改该对象。 - LoveMeSomeCode