blocks|key|780284|text|作为表所有者，您需要向运行SELECT语句的用户授予对底层表的SELECT访问权限。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|780285|grant+SELECT+on+TABLE_NAME+to+READ_USERNAME;|code-block|syntax|javascript|780286|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

As the table owner you need to grant SELECT access on the underlying tables to the user you are running the SELECT statement as.

<pre><code>grant SELECT on TABLE_NAME to READ_USERNAME;
</code></pre>

blocks|key|783090|text|最后我让它起作用了。Steve的答案是正确的，但并不适用于所有情况。当从第三个模式执行该视图时，它会失败。为此，您必须添加授权选项：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|783091|GRANT+SELECT+ON+[TABLE_NAME]+TO+[READ_USERNAME]+WITH+GRANT+OPTION;|code-block|syntax|javascript|783092|这样，[READ_USERNAME]还可以将视图的select权限授予另一个模式|offset|length|style|CODE|783093|entityMap^0|0|0|3|F|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

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:

<pre class="lang-sql prettyprint-override"><code>GRANT SELECT ON [TABLE_NAME] TO [READ_USERNAME] WITH GRANT OPTION;
</code></pre>

That way, <code>[READ_USERNAME]</code> can also grant select privilege over the view to another schema

blocks|key|3899718|text|问:什么时候需要“带授权选项”？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3899719|A.当您从第三个模式执行视图时。|3899720|示例:+schema+DSDSW有一个名为view_name的视图|3899721|a)+that+view+selects+from+a+table+in+another+schema++(FDR.balance)
b)+a+third+shema++X_WORK++tries+to+select++from+that+view|code-block|syntax|javascript|3899722|典型的授权:将dsdw.view_name上的select授权给dsdw_select_role；将dsdw_select_role授权给罗斯福；|3899723|但是:+fdr从dsdw.view_name获取select+count(*)；第1行错误:+ORA-01031:权限不足|3899724|发放奖助金：|3899725|grant+select+on+fdr.balance+to+dsdw+with+grant+option;|3899726|现在fdr:+select+count(*)+from+dsdw.view_name；5行|3899727|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|X|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|Y|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|Z|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|11|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|12|8|@]|9|@]|A|$]]|$1|Q|3|R|5|H|7|13|8|@]|9|@]|A|$I|J]]|$1|S|3|T|5|6|7|14|8|@]|9|@]|A|$]]|$1|U|3|-4|5|6|7|15|8|@]|9|@]|A|$]]]|V|$]]

Q. When is the "with grant option" required ?

A. when you have a view executed from a third schema.

Example:
 schema DSDSW has a view called view_name

<pre><code>a) that view selects from a table in another schema (FDR.balance)
b) a third shema X_WORK tries to select from that view
</code></pre>

Typical grants:
 grant select on dsdw.view_name to dsdw_select_role;
 grant dsdw_select_role to fdr;

But: fdr gets
 select count(*) from dsdw.view_name;
 ERROR at line 1:
 ORA-01031: insufficient privileges

issue the grant: 

<pre><code>grant select on fdr.balance to dsdw with grant option;
</code></pre>

now fdr:
 select count(*) from dsdw.view_name;
 5 rows

blocks|key|5310464|text|让我来回顾一下。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5310465|当您构建一个包含不同所有者的对象的视图时，其他所有者必须将"with+grant+option“授予视图的所有者。因此，视图所有者可以将权限授予其他用户或模式...|5310466|示例:+User_a是名为mine_a的表的所有者User_b是名为yours_b的表的所有者|5310467|假设user_b想要创建一个连接了mine_a和yours_b的视图|5310468|为了使视图正常工作，user_a必须将"grant+select+on+mine_a+to+user_b+with+grant+option“|5310469|然后，user_b可以向每个人授予对该视图的select权限。|5310470|entityMap^0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|P|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|Q|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|R|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|S|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|M|$]]

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

Let's say user_b wants to create a view with a join of mine_a and yours_b

For the view to work fine, user_a has to give "grant select on mine_a to user_b with grant option"

Then user_b can grant select on that view to everybody.

blocks|key|3054753|text|如果通过存储过程访问视图，则execute权限不足以访问视图。您必须显式授予select权限。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3054754|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.

blocks|key|5310512|text|如果通过存储过程访问视图，则execute权限不足以访问视图。您必须显式授予select权限。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5310513|只需键入以下内容|5310514|把所有的东西都给公众；|5310515|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

If the view is accessed via a stored procedure, the execute grant is insufficient to access the view. You must grant select explicitly.

simply type this

grant all on to public;

blocks|key|5310564|text|要使用视图，用户必须拥有适当的特权，但仅限于视图本身，而不是其底层对象。但是，如果视图的底层对象的访问权限被删除，则用户不再具有访问权限。之所以会发生这种情况，是因为用户查询视图时使用的安全域是视图的定义者的安全域。如果从视图的定义器中撤消了底层对象的特权，则视图将无效，并且任何人都无法使用该视图。因此，即使用户已被授予对视图的访问权限，如果定义者的权限已从视图的底层对象中撤销，则用户可能无法使用视图。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5310565|Oracle文档http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017|offset|length|5310566|entityMap|0|LINK|mutability|MUTABLE|url|http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017^0|0|8|2B|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|O|8|@]|9|@$D|P|E|Q|1|R]]|A|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|A|$]]]|G|$H|$5|I|J|K|A|$L|M]]]]

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
<a href="http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017" rel="nofollow">http://docs.oracle.com/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG98017</a>

blocks|key|3899934|text|您还可以使用架构名称创建视图，例如create或replace+view+schema_name.view_name+as+select..|type|unstyled|depth|inlineStyleRanges|offset|length|style|BOLD|entityRanges|data|3899935|entityMap^0|H|1H|0^^$0|@$1|2|3|4|5|6|7|H|8|@$9|I|A|J|B|C]]|D|@]|E|$]]|$1|F|3|-4|5|6|7|K|8|@]|D|@]|E|$]]]|G|$]]

you may also create view with schema name
for example create or replace view schema_name.view_name as select..

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-01031: insufficient privileges when selecting view

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

教程

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云智能顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云AI代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

功能1上新10个字符

功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符功能2描述100个字符。

功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符功能2上新100个字符。

功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符功能5描述100个字符

功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符功能5上新100个字符

功能4上新

文章&问答评论现已支持表情

全新交互，全新视觉，新增快捷键、悬浮工具栏、高亮块等功能并同时优化现有功能，全面提升创作效率和体验

社区富文本编辑器全新改版！诚邀体验～ 

精选全网热门MCP server，让你的AI更好用 🚀

💥开发者 MCP广场重磅上线！

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

聚焦“写作效率、视觉美观与运行性能”三方面进行全面升级，为您提供更高效、稳定的创作环境

社区富文本&Markdown编辑器全新改版上线，欢迎大家体验!

诚挚邀请您参与本次调研，分享您的真实使用感受与建议。您的反馈至关重要，感谢您的支持与参与！

社区新版编辑器体验调研

当我尝试执行包含来自不同模式的表的视图时，抛出ORA-001031权限不足。这些表对创建视图的架构具有execute权限。如果我执行视图的SQL语句，它就可以工作。我遗漏了什么？

问ORA-01031:选择视图时权限不足
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-01031:选择视图时权限不足EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问ORA-01031:选择视图时权限不足
EN