oracle ORA-01031: 选择视图时权限不足
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/140643/
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
ORA-01031: insufficient privileges when selecting view
提问by Igor Zelaya
When I try to execute a view that includes tables from different schemas an ORA-001031 Insufficient privileges is thrown. These tables have execute permission for the schema where the view was created. If I execute the view's SQL Statement it works. What am I missing?
当我尝试执行包含来自不同模式的表的视图时，抛出 ORA-001031 Insufficient privileges。这些表对创建视图的架构具有执行权限。如果我执行视图的 SQL 语句，它就可以工作。我错过了什么？
采纳答案by Steve K
As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.
作为表所有者，您需要向正在运行 SELECT 语句的用户授予对基础表的 SELECT 访问权限。
grant SELECT on TABLE_NAME to READ_USERNAME;
回答by Igor Zelaya
Finally I got it to work. Steve's answer is right but not for all cases. It fails when that view is being executed from a third schema. For that to work you have to add the grant option:
最后我让它工作。史蒂夫的回答是正确的，但并非适用于所有情况。当从第三个模式执行该视图时，它会失败。为此，您必须添加授权选项：
GRANT SELECT ON [TABLE_NAME] TO [READ_USERNAME] WITH GRANT OPTION;
That way, [READ_USERNAME]can also grant select privilege over the view to another schema
这样，[READ_USERNAME]还可以将视图的选择权限授予另一个模式
回答by Steve K
Q. When is the "with grant option" required ?
Q. 什么时候需要“with grant option”？
A. when you have a view executed from a third schema.
A. 当您从第三个模式执行视图时。
Example: schema DSDSW has a view called view_name
示例：模式 DSDSW 有一个名为 view_name 的视图
a) that view selects from a table in another schema (FDR.balance)
b) a third shema X_WORK tries to select from that view
Typical grants: grant select on dsdw.view_name to dsdw_select_role; grant dsdw_select_role to fdr;
典型授权：将 dsdw.view_name 上的 select 授权给 dsdw_select_role；将 dsdw_select_role 授予 fdr；
But: fdr gets select count(*) from dsdw.view_name; ERROR at line 1: ORA-01031: insufficient privileges
但是：fdr 从 dsdw.view_name 中获取 select count(*); 第 1 行的错误：ORA-01031：权限不足
issue the grant:
发放补助金：
grant select on fdr.balance to dsdw with grant option;
now fdr: select count(*) from dsdw.view_name; 5 rows
现在 fdr：从 dsdw.view_name 中选择 count(*)；5 行
回答by Roberto Monterrey
Let me make a recap.
让我回顾一下。
When you build a view containing object of different owners, those other owners have to grant "with grant option" to the owner of the view. So, the view owner can grant to other users or schemas....
当您构建包含不同所有者的对象的视图时，这些其他所有者必须向视图的所有者授予“授权选项”。因此，视图所有者可以授予其他用户或模式....
Example: User_a is the owner of a table called mine_a User_b is the owner of a table called yours_b
示例：User_a 是名为 mine_a 的表的所有者 User_b 是名为 yours_b 的表的所有者
Let's say user_b wants to create a view with a join of mine_a and yours_b
假设 user_b 想要创建一个包含 mine_a 和 yours_b 的视图
For the view to work fine, user_a has to give "grant select on mine_a to user_b with grant option"
为了使视图正常工作，user_a 必须使用“授予选项将 mine_a 上的选择授予 user_b”
Then user_b can grant select on that view to everybody.
然后 user_b 可以向所有人授予对该视图的选择权。
回答by akshay
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
如果视图是通过存储过程访问的，执行授权不足以访问视图。您必须明确授予 select 权限。
simply type this
只需输入这个
grant all on to public;
全部公开；
回答by dacracot
If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.
如果视图是通过存储过程访问的，执行授权不足以访问视图。您必须明确授予 select 权限。
回答by Van Gogh
To use a view, the user must have the appropriate privileges but only for the view itself, not its underlying objects. However, if access privileges for the underlying objects of the view are removed, then the user no longer has access. This behavior occurs because the security domain that is used when a user queries the view is that of the definer of the view. If the privileges on the underlying objects are revoked from the view's definer, then the view becomes invalid, and no one can use the view. Therefore, even if a user has been granted access to the view, the user may not be able to use the view if the definer's rights have been revoked from the view's underlying objects.
要使用视图，用户必须具有适当的权限，但仅限于视图本身，而不是其底层对象。但是，如果删除了视图底层对象的访问权限，则用户将不再具有访问权限。出现此问题的原因是用户查询视图时使用的安全域是视图定义者的安全域。如果底层对象的权限从视图的定义者中被撤销，那么视图就无效了，没有人可以使用该视图。因此，即使已授予用户访问视图的权限，如果定义者的权限已从视图的底层对象中撤消，用户也可能无法使用该视图。
Oracle Documentation http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017
Oracle 文档 http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017
