授予在另一个模式 (Oracle) 上创建同义词的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30127491/
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 to create synonyms on another schema (Oracle)
提问by user1595858
I just wondering if there any option to grant permission to create synonyms on different schema without giving 'ANY' option. I just want to narrow down the grant to provide permission with what is required for security purpose.
我只是想知道是否有任何选项可以授予在不同模式上创建同义词的权限而不提供“ANY”选项。我只想缩小授权范围，以提供出于安全目的所需的许可。
We have created a schema name A which related to application product. But the application suppose to access the object through another (login) schema B. We have granted resource to schema A so schema A owner can creates its own objects. What grant syntax i need to use to grant Schema A to create synonyms on schema B, so it can create synonyms.
我们创建了一个与应用程序产品相关的模式名称 A。但是应用程序假设通过另一个（登录）模式 B 访问对象。我们已将资源授予模式 A，因此模式 A 所有者可以创建自己的对象。我需要使用什么授予语法来授予模式 A 在模式 B 上创建同义词，以便它可以创建同义词。
End result should be as below and can be created by schema owner A without interference of DBA
最终结果应如下所示，并且可以由架构所有者 A 创建，而不会受到 DBA 的干扰
B.b_synonym maps to A.b_object
回答by Erich Kitzmueller
You need the CREATED ANY SYNONYM privilege to do that as A, therefore
您需要 CREATED ANY SYNONYM 权限才能以 A 身份执行此操作，因此
GRANT CREATE ANY SYNONYM TO A;
EDIT: To avoid the ANY privilege, do this:
编辑：为了避免任何特权，请执行以下操作：
a) as A:
a) 作为 A：
GRANT SELECT ON mytable1 TO B;
GRANT SELECT, INSERT, UPDATE, DELETE ON mytable2 TO B;
b) as B:
b) 作为 B：
CREATE SYNONYM a_mytable1 FOR A.mytable1;
CREATE SYNONYM a_mytable2 FOR A.mytable2;
回答by Alex Poole
You can't grant privileges that only apply to one other schema. You would have to grant ANY - even if temporarily, e.g. during the creation/modification of the main A schema, to reduce the security impact - and create all the synonyms in the other B user's schema while you had the privileges. Otherwise user B would have to create the synonyms itself; or user A could create public synonyms.
您不能授予仅适用于另一种架构的权限。您必须授予 ANY - 即使是临时的，例如在主 A 模式的创建/修改期间，以减少安全影响 - 并在您拥有特权时在其他 B 用户的模式中创建所有同义词。否则用户 B 将不得不自己创建同义词；或者用户 A 可以创建公共同义词。
As an alternative to having any synonyms, you could have user B switch to schema A with:
作为拥有任何同义词的替代方法，您可以让用户 B 切换到模式 A，其中：
alter session set current_schema = A;
They could then refer to A's objects without having to prefix them with the schema name, though they then couldn't see any objects in their own schema without prefixing those instead - it doesn't sound like B will have objects but hard to tell.
然后，他们可以引用 A 的对象，而不必为它们加上模式名称的前缀，尽管他们在没有前缀的情况下无法看到自己模式中的任何对象 - 听起来 B 不会有对象，但很难说。
You can also automate that schema switch via a logon trigger:
您还可以通过登录触发器自动执行该架构切换：
create or replace trigger ramread_logon_trigger
after logon on database
begin
if user = 'B' then
execute immediate 'alter session set current_schema = A';
end if;
end;
/
If you actually have multiple users you can use a role instead, and switch schema for any user that has that role, by testing with dbms_session.is_role_enabled. The same role could be granted the necessary permissions to access A's objects, which you will need to grant somehow - a synonym doesn't itself give any access privileges.
如果您实际上有多个用户，则可以改用一个角色，并通过测试为具有该角色的任何用户切换架构dbms_session.is_role_enabled。可以授予同一角色访问 A 对象的必要权限，您需要以某种方式授予该权限 - 同义词本身不授予任何访问权限。
