SQL Server 2008 授予 information_schema.columns 权限
我有一系列从数据库中选择数据的存储过程。我有一个角色（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 下运行时没有任何问题。
如果我将 db_datareader 授予 cctc_reader 查询就可以，但我不想授予所有表的读者权限（因此我使用存储过程）。我已经尝试按照一些文章中的建议授予主数据库中 Information_schema 的 Select 权限，但仍然无法正常工作。
有什么建议吗？
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.
The problem is that in this proc the query
select column_name from information_schema.columns where table_name = 'recControl_manager'
does not return any records. cctc_reader has grant permissions on:
- each select proc
recControl_system_option
so in theory this should work. I have no problems when run under dbo.
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.
Any suggestions?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对象元数据可见性受 查看定义 权限的约束：
授予权限的正确安全取决于您的场景。它可以是 dbo 或其他一些模式，可以是数据库本身，也可以是单独的表。如果我处于你的位置，我会对recControl_system_option过程进行代码签名，并且我会在服务器级别的签名上授予“查看任何定义”的权限，这是使用角色和授予角色权限的更好、更安全的方式。有关如何签名的示例，请参阅签署激活的程序过程并授予对签名的服务器级别权限。
Objects metadata visibility is subject to the VIEW DEFINITION permission:
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.
正如 Remus 提到的，元数据可见性会影响查询系统表和视图时返回的数据。如果您对安全对象（对象、登录名等）没有权限，它将不可见。
根据您的情况，您可以允许内部调用 EXECUTE AS OWNER，或者将 Information_schema.columns 包装在 udf 中，作为
EXECUTE AS OWNER我们在查询元数据时使用此技术。
As Remus mentioned, metadata visbility affects data returned when querying system tables and views. If you have no rights on a securable (object, login, whatever) it won't be visible.
Depending on your situation, you would allow the internal call to have EXECUTE AS OWNER, or wrap Information_schema.columns in a udf that as
EXECUTE AS OWNERWe use this technique where we query metadata.
在其他地方找到这个，创建一个在数据库中调用系统存储过程 sp_columns 的存储过程。您的存储过程可以使用与其他存储过程相同的权限执行。缺点是返回的集合有许多您可能不感兴趣的列。我敢说您可以改进存储过程。我选择在代码中进行字段选择。
作为
开始
结束
Found this elsewhere, make a sproc that calls System sproc sp_columns in your database. Your sproc can execute with the same permissions as your other sprocs. Downside is that the returned set has many columns that you probably are not interested in. I daresay you could refine the sproc. I chose to do the field choice in code.
AS
BEGIN
END