SQL Server 2008向information_schema.columns授予权限 [英] SQL Server 2008 grant permission to information_schema.columns

问题描述

我有一系列从数据库中选择数据的存储过程.我有一个角色(cctc_reader)，具有对该过程授予的执行权限.该过程之一调用另一个名为recControl_system_option的存储过程，该存储过程又查询Information_schema.columns.

I have a series of stored procedures that select data from a db. I have a role (cctc_reader) that has execute permissions granted on the procedures. One of the procedure calls another stored procedure called recControl_system_option which in turn queries Information_schema.columns.

问题是在此proc中查询

The problem is that in this proc the query

select column_name from information_schema.columns where table_name = 'recControl_manager'

不返回任何记录. cctc_reader在以下方面具有授予权限:

does not return any records. cctc_reader has grant permissions on:

• 每个选择过程
• recControl_system_option

因此从理论上讲这应该起作用.在dbo下运行时，我没有任何问题.

so in theory this should work. I have no problems when run under dbo.

如果我将db_datareader授予cctc_reader，则查询很好，但是我不想授予所有表的读取者权限(因此，为什么使用存储的procs).我曾尝试按照某些文章中的建议在Master db中授予对Information_schema的Select权限，但仍然无法使它正常工作.

If I grant db_datareader to cctc_reader the query is fine, but I don't want to grant reader permissions to all tables (hence why I used stored procs). I've tried granting Select permissions on Information_schema in the Master db as suggested in some articles, but still can't get this to work.

有什么建议吗?

推荐答案

对象元数据可见性受查看定义权限:

Objects metadata visibility is subject to the VIEW DEFINITION permission:

GRANT VIEW DEFINITION ON ... TO cctc_reader;

使用VIEW DEFINITION权限可以 用户看到安全对象的元数据 授予的权限. 但是，VIEW DEFINITION权限 不授予访问权限 本身是安全的.例如，一个用户 仅授予VIEW DEFINITION 对表的权限可以查看元数据 与表中的相关 sys.objects目录视图.然而， 没有其他权限，例如 选择或控制，用户不能 从表中读取数据.

The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

可安全授予权限的权利取决于您的方案.它可以是dbo或其他某种模式，可以是数据库本身，也可以是单个表.如果在您的位置，我将对recControl_system_option过程进行代码签名，并在服务器级别对签名授予VIEW ANY DEFINITION，这是使用角色和授予角色权限的一种更好而安全的方法.有关如何签名的示例，请参见签署已激活的程序.一个过程，并在签名上授予服务器级别的权限.

The right securable to grant permission to depends on your scenario. It could be the dbo or some other schema, it could be the database itself, it could be individual tables. If I was in your place, I'd code sign the recControl_system_option procedure and I'd grant VIEW ANY DEFINITION on the signature at server level, a much better and secure way that using roles and granting permission on roles. See Signing an activated procedure for an example of how to sign a procedure and grant a server level permission on the signature.

这篇关于SQL Server 2008向information_schema.columns授予权限的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！