SQL Server 2008授予权限给information_schema.columns

Question

我有一系列的存储过程，用于从数据库中选择数据。我有一个角色（cctc_reader），其被授予对这些存储过程的执行权限。其中一个存储过程调用另一个名为recControl_system_option的存储过程，后者查询Information_schema.columns。

问题在于，在这个存储过程中，查询语句 select column_name from information_schema.columns where table_name = 'recControl_manager' 没有返回任何记录。cctc_reader已被授予以下权限：

- 每个选择存储过程 - recControl_system_option 理论上应该可以工作。在dbo下运行时，我没有任何问题。

如果我向cctc_reader授予db_datareader，则查询很好，但我不想向所有表授予读取权限（这就是为什么我使用存储过程的原因）。我尝试了在Master db中授予Information_schema的Select权限，但仍然无法使其正常工作。

有什么建议吗？

- pilsdumps

Answer 1

对象元数据的可见性取决于查看定义权限：

GRANT VIEW DEFINITION ON ... TO cctc_reader;

VIEW DEFINITION权限允许用户查看授予权限的可保护对象的元数据。但是，VIEW DEFINITION权限并不授予对可保护对象本身的访问权限。例如，仅被授予表的VIEW DEFINITION权限的用户可以在sys.objects目录视图中看到与该表相关的元数据。然而，在没有像SELECT或CONTROL这样的其他权限的情况下，用户无法从表中读取数据。

授予权限的可保护对象取决于您的场景。它可以是dbo或其他模式，可以是数据库本身，也可以是单个表。如果我处在您的位置，我将对recControl_system_option过程进行代码签名，并在服务器级别上授予VIEW ANY DEFINITION权限，这比使用角色和在角色上授予权限更好、更安全。请参见签署已激活的过程，了解如何签署过程并在签名上授予服务器级别权限的示例。

- Remus Rusanu

---

太好了！感谢您的输入。我已经想出如何修复这个问题，您给了我很多启示....！ - pilsdumps

Answer 2

如Remus所述，元数据可见性会影响查询系统表和视图时返回的数据。如果您对一个可保护对象（对象、登录等）没有权限，则它将不可见。

根据您的情况，您可以允许内部调用具有EXECUTE AS OWNER，或者在UDF中包装Information_schema.columns并使用EXECUTE AS OWNER。

我们在查询元数据时使用这种技术。

- gbn

Answer 3

在其他地方找到了这个，创建一个调用数据库中 System 存储过程 sp_columns 的 sproc。您的 sproc 可以使用与其他 sprocs 相同的权限执行。缺点是返回的结果集可能包含许多您不感兴趣的列。我敢说您可以完善这个 sproc。我选择在代码中进行字段选择。

CREATE PROCEDURE [dbo].[proc_tblMyTableSchemaGet] 

开始

SET NOCOUNT ON;


exec sp_columns @table_name = 'myTable', @table_owner = 'dbo';

结束

- Bob Clegg