Oracle 数据库中表触发器的多模式权限

Multi-Schema Privileges for a Table Trigger in an Oracle Database

提问by waltwood

I'm trying to write a table trigger which queries another table that is outside the schema where the trigger will reside. Is this possible? It seems like I have no problem querying tables in my schema but I get:

我正在尝试编写一个表触发器，它查询触发器所在架构之外的另一个表。这可能吗？似乎我在我的架构中查询表没有问题，但我得到：

Error: ORA-00942: table or view does not exist

when trying trying to query tables outside my schema.

尝试在我的架构之外查询表时。

EDIT

编辑

My apologies for not providing as much information as possible the first time around. I was under the impression this question was more simple.

我很抱歉第一次没有提供尽可能多的信息。我的印象是这个问题更简单。

I'm trying create a trigger on a table that changes some fields on a newly inserted row based on the existence of some data that may or may not be in a table that is in another schema.

我正在尝试在表上创建一个触发器，该触发器根据某些数据的存在来更改新插入行上的某些字段，这些数据可能存在于另一个模式中的表中，也可能不存在。

The user account that I'm using to create the trigger does have the permissions to run the queries independently. In fact, I've had my trigger print the query I'm trying to run and was able to run it on it's own successfully.

我用来创建触发器的用户帐户确实有权独立运行查询。事实上，我已经让我的触发器打印了我正在尝试运行的查询，并且能够成功地自行运行它。

I should also note that I'm building the query dynamically by using the EXECUTE IMMEDIATE statement. Here's an example:

我还应该注意，我正在使用 EXECUTE IMMEDIATE 语句动态构建查询。下面是一个例子：

CREATE OR REPLACE TRIGGER MAIN_SCHEMA.EVENTS
BEFORE INSERT
ON MAIN_SCHEMA.EVENTS REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE 
    rtn_count NUMBER := 0;
    table_name VARCHAR2(17) := :NEW.SOME_FIELD;
    key_field VARCHAR2(20) := :NEW.ANOTHER_FIELD;
BEGIN
    CASE
        WHEN (key_field = 'condition_a') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_A.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
        WHEN (key_field = 'condition_b') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_B.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
        WHEN (key_field = 'condition_c') THEN
            EXECUTE IMMEDIATE 'select count(*) from OTHER_SCHEMA_C.'||table_name||' where KEY_FIELD='''||key_field||'''' INTO rtn_count;
    END CASE;

    IF (rtn_count > 0) THEN
        -- change some fields that are to be inserted
    END IF; 
END;

The trigger seams to fail on the EXECUTE IMMEDIATE with the previously mentioned error.

触发器接缝在 EXECUTE IMMEDIATE 上失败，并出现前面提到的错误。

EDIT

编辑

I have done some more research and I can offer more clarification.

我做了更多的研究，我可以提供更多的说明。

The user account I'm using to create this trigger is not MAIN_SCHEMA or any one of the OTHER_SCHEMA_Xs. The account I'm using (ME) is given privileges to the involved tables via the schema users themselves. For example (USER_TAB_PRIVS):

我用来创建此触发器的用户帐户不是 MAIN_SCHEMA 或 OTHER_SCHEMA_X 中的任何一个。我正在使用的帐户 (ME) 通过模式用户本身获得了对相关表的权限。例如（USER_TAB_PRIVS）：

GRANTOR        GRANTEE TABLE_SCHEMA    TABLE_NAME PRIVILEGE GRANTABLE HIERARCHY
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     DELETE    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     INSERT    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     SELECT    NO        NO
MAIN_SCHEMA    ME       MAIN_SCHEMA    EVENTS     UPDATE    NO        NO
OTHER_SCHEMA_X ME       OTHER_SCHEMA_X TARGET_TBL SELECT    NO          NO

And I have the following system privileges (USER_SYS_PRIVS):

我有以下系统权限（USER_SYS_PRIVS）：

USERNAME   PRIVILEGE            ADMIN_OPTION
ME         ALTER ANY TRIGGER    NO
ME         CREATE ANY TRIGGER   NO
ME         UNLIMITED TABLESPACE NO

And this is what I found in the Oracle documentation:

这就是我在 Oracle 文档中找到的内容：

To create a trigger in another user's schema, or to reference a table in another schema from a trigger in your schema, you must have the CREATE ANY TRIGGER system privilege. With this privilege, the trigger can be created in any schema and can be associated with any user's table. In addition, the user creating the trigger must also have EXECUTE privilege on the referenced procedures, functions, or packages.

要在另一个用户的架构中创建触发器，或从架构中的触发器引用另一个架构中的表，您必须具有 CREATE ANY TRIGGER 系统权限。使用此权限，触发器可以在任何模式中创建，并且可以与任何用户的表相关联。此外，创建触发器的用户还必须对引用的过程、函数或包具有 EXECUTE 权限。

Here: Oracle Doc

此处：Oracle 文档

So it looks to me like this should work, but I'm not sure about the "EXECUTE privilege" it's referring to in the doc.

所以在我看来这应该可行，但我不确定它在文档中所指的“执行特权”。

采纳答案by Florin Ghita

You should execute this for every table and schema involved:

您应该对涉及的每个表和架构执行此操作：

grant select on OTHER_SCHEMA_%.table_name to MAIN_SCHEMA;

回答by APC

What you are experiencing is a feature of Oracle's security model. The entire point of using schemas is to control access to the data. The tables in my schema are mine, you cannot even see them until I grant you privileges on them.

您遇到的是 Oracle 安全模型的一个特性。使用模式的全部意义在于控制对数据的访问。我的架构中的表是我的，在我授予您对它们的权限之前，您甚至无法看到它们。

The syntax is quite simple: the owner schema issues

语法很简单：所有者架构问题

grant select, insert on my_table to you
/

Alternatively an account with the GRANT ANY privilege (such as a DBA) can pass privileges on any user's objects.

或者，具有 GRANT ANY 权限的帐户（例如 DBA）可以传递对任何用户对象的权限。

grant select, insert on apc.my_table to you
/

The grantee can be either a user or a role. However, note that we can only build program units - stored procedures, views, triggers - using privileges which have been granted directly through to our user.

被授权者可以是用户或角色。但是，请注意，我们只能使用直接授予我们用户的权限来构建程序单元——存储过程、视图、触发器。

So, if you get the other schema owner to grant you the necessary privileges you will be able to build your trigger.

因此，如果您让其他架构所有者授予您必要的权限，您将能够构建触发器。

edit

编辑

When referencing an object in another schema we need to qualify the object with the schema name ....

当引用另一个模式中的对象时，我们需要使用模式名称来限定对象....

insert into apc.whatever_table  values ...

or else we need to create a synonym for it

否则我们需要为它创建一个同义词

create synonym whatever for apc.whatever_table;

回答by dpbradley

I feel someone should add the obvious - the other schema's table must be qualified with the schema name or a private/public synonym is needed. I wonder if the original problem was merely a name resolution issue. If not, APC's answer is a good explanation of the Oracle security model.

我觉得有人应该添加明显的 - 另一个模式的表必须用模式名称限定，或者需要一个私有/公共同义词。我想知道最初的问题是否仅仅是名称解析问题。如果不是，APC 的回答是对 Oracle 安全模型的一个很好的解释。