oracle 为不同模式的用户授予权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4220094/
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
Granting permission to users on different schema
提问by mahen
I have tables in Schema A. I created views in Schema B using the tables in schema A.
我在模式 A 中有表。我使用模式 A 中的表在模式 B 中创建了视图。
I want to grant permissions to a user to select the data from view in Schema B.
我想授予用户从架构 B 中的视图中选择数据的权限。
For this to work i know we have to enable the grant option on tables in Schema A to user B. But I want to do it in a single script (This script has to be in schema B). Is there a way to do this using the user name/password of schema A.
为此，我知道我们必须为用户 B 在模式 A 中的表上启用授予选项。但我想在单个脚本中执行此操作（此脚本必须在模式 B 中）。有没有办法使用模式 A 的用户名/密码来做到这一点。
回答by APC
It's not unusual to want to have a single script to deploy a change. The thing is, such a script needs to be run by a power user, because it needs to have system privileges at the ANY level. This usually means a DBA account, preferably an application account but otherwise SYSTEM or SYS.
希望使用单个脚本来部署更改并不罕见。问题是，这样的脚本需要由高级用户运行，因为它需要具有任何级别的系统权限。这通常是指 DBA 帐户，最好是应用程序帐户，否则是 SYSTEM 或 SYS。
So the script you want would look like this:
所以你想要的脚本看起来像这样：
grant select on user_a.t23 to user_b
/
grant select on user_a.t42 to user_b
/
create view user_b.v_69 as
select t23.col1, t42.col2
from user_a.t42
join user_a.t23
on (t42.id = t23.id)
/
grant select on user_b.v_69 to user_c
/
A common scenario is that we have a suite of individual scripts which have been written to be run by different users but which we now need to bundle up into a single deployment. The original scripts don't contain the schema names, and there are many good reasons why we wouldn't want to hardcode them in the scripts.
一个常见的场景是我们有一套单独的脚本，这些脚本已经被编写为由不同的用户运行，但我们现在需要将它们捆绑到一个部署中。原始脚本不包含模式名称，我们不希望在脚本中对它们进行硬编码有很多很好的理由。
One way to build that master script is to use change the CURRENT_SCHEMA syntax:
构建该主脚本的一种方法是使用更改 CURRENT_SCHEMA 语法：
alter session set current_schema=USER_A
/
@run_grants_to_userb.sql
alter session set current_schema=USER_B
/
@create_view69.sql
@run_grants_to_userc.sql
We still need a DBA user to run the master script. One advantage of switching the current schema is that it allows us to deploy objects like database links, which through a quirk of syntax cannot have the schema name in their declaration. One gotcha is that the user doesn't change, so a script which employs the USER pseudo-column may produce unwanted results.
我们仍然需要一个 DBA 用户来运行主脚本。切换当前模式的一个优点是它允许我们部署像数据库链接这样的对象，由于语法的怪癖，它们的声明中不能包含模式名称。一个问题是用户不会改变，因此使用 USER 伪列的脚本可能会产生不需要的结果。
回答by Antriksh Devgan
Simply Run the query
只需运行查询
GRANT INSERT, SELECT, UPDATE, DELETE ON TABLE1 TO SCHEMA2;
将 TABLE1 上的插入、选择、更新、删除权限授予 SCHEMA2；
回答by Martin Schapendonk
Let user A grant select on his tables to B and include the 'grant option'.
让用户 A 在他的表上授予选择 B 并包括“授予选项”。
As user A:
作为用户 A：
GRANT select ON table TO user_b WITH GRANT OPTION;
Let user B grant select on his views to user A and include the 'grant option'.
让用户 B 将其视图的选择授予用户 A，并包含“授予选项”。
As user B:
作为用户 B：
GRANT select ON view TO user_a WITH GRANT OPTION;
As user A:
作为用户 A：
GRANT select on user_b.view TO user_c;
This allows user A to pass this grant on to other users.
这允许用户 A 将此授权传递给其他用户。
回答by Alex Poole
Only by connecting as user A at some point. You can still do it in one script if you really want to:
只能通过在某个时候作为用户 A 进行连接。如果您真的想这样做，您仍然可以在一个脚本中完成：
connect userA/passwordA
grant select on my_table to userB;
connect userB/passwordB
create view my_view as select * from userA.my_table;
Of course now you have a script lying around which exposes two sets of user credentials to anyone who can read it. So something to think hard about before doing in production, for example.
当然，现在您有一个脚本，它向任何可以阅读它的人公开两组用户凭据。例如，在生产之前需要认真考虑一些事情。
If you want other users to be able to select from the view, you don't need to grant explicit permissions on userA.my_tableto them; as long as the view owner can see the underlying table, other users just need to be able to see the view. Which is often kinda the point (or one of them) as you can restrict the view to only expose selected data from the underlying table to the rest of the world. I assume you have a reason for not creating the view in schema A.
如果您希望其他用户能够从视图中进行选择，则无需userA.my_table向他们授予明确的权限；只要视图所有者可以看到底层表，其他用户只需要能够看到该视图。这通常是重点（或其中之一），因为您可以将视图限制为仅将基础表中的选定数据公开给世界其他地方。我假设您有理由不在模式 A 中创建视图。
I'm not sure if you're really asking about granting select to user B with admin option so that user B can then grant select on user A's table to other people. If that's possible, it doesn't sound like a good idea, and isn't necessary for the view to work.
我不确定您是否真的在询问使用管理选项将选择授予用户 B，以便用户 B 可以将用户 A 的表上的选择授予其他人。如果可能的话，这听起来不是一个好主意，也不是视图工作所必需的。
