创建同义词 ora-01031 权限不足 [英] create synonym ora-01031 insufficient privileges

问题描述

我需要帮助了解用户在指向另一个(不同的)架构对象时创建同义词需要什么授权/特权.

I need help understanding what grants/privileges a user needs to CREATE a SYNONYM when it points to another (different) schema object.

当我尝试下面的操作时，我得到的 ora-01031 权限不足，所以很明显我遗漏了并且无法应用其他所需的权限.我尽可能地进行了搜索，但找不到任何特定于跨架构同义词的内容.

When I try the below, I get ora-01031 insufficient privileges, so obviously I am missing and failing to apply other needed privileges. I did search as well as I could but couldn't find anything specific to cross-schema synonyms.

CREATE USER test IDENTIFIED BY pw DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;
ALTER USER test IDENTIFIED BY pw;
GRANT CONNECT, RESOURCE TO test;

-- ... create a bunch of stuff in test...

CREATE USER READWRITE IDENTIFIED BY pw DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE    TEMP;
ALTER USER READWRITE IDENTIFIED BY pw;
GRANT CONNECT, RESOURCE TO READWRITE;

GRANT SELECT ON GDACS.FIXALARMS TO PUBLIC;
GRANT UPDATE, INSERT ON GDACS.FIXALARMS TO READWRITE; 

CONNECT READWRITE/pw;

CREATE SYNONYM FIXALARMS for test.FIXALARMS;
ORA-01031 insufficient privileges

推荐答案

CREATE SYNONYM 命令 包括:

The documentation for the CREATE SYNONYM command includes:

先决条件

要在您自己的架构中创建私有同义词，您必须具有CREATE SYNONYM 系统权限.

To create a private synonym in your own schema, you must have the CREATE SYNONYM system privilege.

要在另一个用户的架构中创建私有同义词，您必须具有CREATE ANY SYNONYM 系统权限.

To create a private synonym in another user's schema, you must have the CREATE ANY SYNONYM system privilege.

要创建PUBLIC同义词，您必须具有CREATE PUBLIC SYNONYM系统权限.

To create a PUBLIC synonym, you must have the CREATE PUBLIC SYNONYM system privilege.

您正在尝试在 READWRITE 自己的架构中创建私有同义词，因此您必须这样做:

You're trying to create a private synonym in READWRITE's own schema, so you have to have to do:

GRANT CREATE SYNONYM TO READWRITE;

同义词指向的对象在不同的​​架构中，但这与此处无关.

The object the synonym is pointing to is in a different schema, but that isn't relevant here.

如果您的新帐户仅打算访问 GDACS 模式中的对象，并且特别是如果您要授予访问权限的对象很多，那么作为必须为所有可以访问的内容创建同义词的替代方法 在每个会话中更改新用户的 current_schema - 可能是 通过登录触发器.

If your new account is only going to access objects in the GDACS schema, and particularly if you have a lot of objects you want to grant access to, then as an alternative to having to create synonyms for everything you could alter the new user's current_schema in each session - possibly via a logon trigger.

这篇关于创建同义词 ora-01031 权限不足的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！