SQL 授予对视图的 SELECT 权限，但不授予对基础对象的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4134740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Grant SELECT permission on a view, but not on underlying objects
提问by thomaspaulb
I often read that one purpose of a VIEW is security: to allow some users access to the underlying table, and other users to a derived view only. With that in mind I designed several views that supply restricted datasets to external users.
我经常读到 VIEW 的一个目的是安全性：允许一些用户访问基础表，而其他用户只允许访问派生视图。考虑到这一点，我设计了几个向外部用户提供受限数据集的视图。
All very fine, but in practice this doesn't work. After I grant SELECTpermission on a view, the users can't access it unless I grant SELECTon all underlying objects too. Same story for stored procedures. The net result is non-functional, for I end up still granting access to sensitive data to the wrong users, as well as annoying, for it is easy to forget one object and the users come back to complain that the view "doesn't work".
一切都很好，但在实践中这是行不通的。在我授予SELECT视图权限后，除非我也授予SELECT所有底层对象，否则用户将无法访问它。存储过程也是如此。最终结果是无效的，因为我最终仍然将敏感数据的访问权限授予错误的用户，而且很烦人，因为很容易忘记一个对象并且用户回来抱怨视图“没有工作”。
Is there a way to grant SELECTpermissions on a view or stored procedure without having to expose the underlying objects too?
有没有一种方法可以授予SELECT对视图或存储过程的权限，而不必公开底层对象？
采纳答案by Graeme Perrow
Does the same user who owns the view also own the underlying tables? If not, the owner of the tables needs to grant the view owner permission WITH GRANT OPTION. If the same user owns both the tables and the view, then granting permission on the view should be sufficient.
拥有视图的同一用户是否也拥有基础表？如果不是，则表的所有者需要使用 WITH GRANT OPTION 授予视图所有者权限。如果同一用户同时拥有表和视图，则授予对视图的权限就足够了。
回答by uTILLIty
If you have your views in a different schema than the table, you must either grant the user access to the base table, "AUTHORIZE" the owner of the tables to the view like this:
如果您的视图在与表不同的架构中，则必须授予用户对基表的访问权限，将表的所有者“授权”到视图，如下所示：
ALTER AUTHORIZATION ON reporting.MyViewName TO dbo
In the example above dbois the user owning the tables the reporting.MyViewNameis accessing
在上面的例子中dbo是拥有reporting.MyViewName正在访问的表的用户
回答by NotMe
You might find the information in this forumhelpful.
您可能会发现此论坛中的信息很有帮助。
The last post has the details of what was run to grant permissions to a view but not the underlying tables:
最后一篇文章详细介绍了为视图授予权限而不是基础表的运行情况：
CREATE USER [Reports] FOR LOGIN [Reports] WITH DEFAULT_SCHEMA = Reports
CREATE SCHEMA Reports AUTHORIZATION Reports --Auth as Reports was the key piece of information that I had missed.
GO
CREATE ROLE Reporting AUTHORIZATION db_securityadmin
GO
exec sp_addrolemember @rolename = 'Reporting', @membername = 'Reports'
GO
GRANT CREATE VIEW TO Reporting
GRANT CREATE TABLE TO Reporting
GRANT SELECT, VIEW DEFINITION ON [dbo].[zName] TO Reporting;
FYI - For stored procedures, you should be granting EXEC to the procedure.
仅供参考 - 对于存储过程，您应该将 EXEC 授予该过程。
