oracle 授予选择视图
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24307857/
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 on view
提问by Gerben
somewhere along the way something is going wrong, and I can't seem to find out why. If this is already asked apologies.
一路上的某个地方出了问题，我似乎无法找出原因。如果这已经被问到道歉。
I have 3 schema's in my database: COLLDESK LOCAL_IT GERBEN
我的数据库中有 3 个模式： COLLDESK LOCAL_IT GERBEN
COLLDESK is our main schema, LOCAL_IT is where we do our local development, and GERBEN is the end user (should only have select options).
COLLDESK 是我们的主要模式，LOCAL_IT 是我们进行本地开发的地方，而 GERBEN 是最终用户（应该只有选择选项）。
Now I have a table in my COLLDESK schema called GESTIONES. In LOCAL_IT I have a view called ACTIONS. This is holding 4 columns from the table GESTIONES in the COLLDESK schema. So far so good!
现在我的 COLLDESK 模式中有一个名为 GESTIONES 的表。在 LOCAL_IT 中，我有一个名为 ACTIONS 的视图。这包含 COLLDESK 模式中 GESTIONES 表的 4 列。到现在为止还挺好！
Now I want to give the GERBEN schema select privileges in the view ACTIONS. But when I say
现在我想在视图 ACTIONS 中授予 GERBEN 模式选择权限。但是当我说
grant select on LOCAL_IT.ACTIONS to GERBEN
I get the error ORA-01720: grant option does not exist for COLLDESK.GESTIONES
我收到错误 ORA-01720：COLLDESK.GESTIONES 不存在授予选项
Now I tried giving GERBEN select privileges to GESTIONES, but I am still getting the error message
现在我尝试为 GESTIONES 授予 GERBEN select 权限，但我仍然收到错误消息
Any help would be greatly appreciated!
任何帮助将不胜感激！
Kind regards
亲切的问候
Gerben
格本
回答by Nicolas de Fontenay
The error message should be written like this:
错误信息应该这样写：
ORA-01720: "grant option" does not exist for COLLDESK.GESTIONES.
ORA-01720: COLLDESK.GESTIONES 不存在“授予选项”。
Here's how it works:
这是它的工作原理：
You have 3 schemas:
您有 3 个架构：
- Schema1 - Holder of a table named "table1"
- Schema2 - Holder of a view "view1" selecting from schema1.table1
- Schema3 - User, selecting from schema2.view1 - has no select granted on schema1.table1.
- Schema1 - 名为“table1”的表的持有者
- Schema2 - 从 schema1.table1 中选择的视图“view1”的持有者
- Schema3 - 用户，从 schema2.view1 中选择 - 没有在 schema1.table1 上授予选择权。
You probably have done the following:
您可能已经执行了以下操作：
grant select on schema1.table1 (COLLDESK.GESTIONES) to schema2;
Note that since you're creating a view, the select must granted directly to schema2, your view won't work if the select is granted through a role.
请注意，由于您正在创建视图，因此选择必须直接授予 schema2，如果选择是通过角色授予的，您的视图将无法工作。
Now you are trying to allow a 3rd schema (schema3) to use the view read data from schema1. For security purposes, a schema2 will never be allowed to access data from schema1 to a 3rd schema even if it can view the data. This would be a huge security hole otherwise.
现在您正在尝试允许第三个模式 (schema3) 使用从 schema1 读取数据的视图。出于安全目的，即使schema2 可以查看数据，也永远不会允许schema2 访问从schema1 到第三个schema 的数据。否则这将是一个巨大的安全漏洞。
So the solution is to make it explicit that schema2 will be able to grant that select privilege, indirectly, when a 3rd party is granted the select privilege on the view.
因此，解决方案是明确表示，当第 3 方被授予对视图的选择权限时，schema2 将能够间接授予该选择权限。
Solution:
解决方案：
grant select on schema1.table1 to schema2 with grant option;
Now schema2, is allowed to grant select on its view to 3rd parties:
现在，schema2 可以将其视图的选择授予 3rd 方：
grant select on schema2.view1 to schema3;
For you:
为你：
grant select on COLLDESK.GESTIONED to LOCAL_IT with grant option;
grant select on LOCAL_IT.ACTIONS to GERBEN;
Also: Avoid using public views and public db links unless absolutely necessary. Deplete every other options first.
另外：除非绝对必要，否则避免使用公共视图和公共数据库链接。首先耗尽所有其他选项。
回答by SriniV
create a public synonym for the view and try again with your grants
为视图创建一个公共同义词，然后使用您的授权重试
create public synonym ACTIONS for LOCAL_IT.ACTIONS;
grant select on ACTIONS to GERBEN;
