Oracle数据库中表触发器的多模式特权 [英] Multi-Schema Privileges for a Table Trigger in an Oracle Database

问题描述

我正在尝试编写一个表触发器，该触发器查询该触发器将驻留的架构之外的另一个表.这可能吗?看来我在架构中查询表没有问题，但是我得到了:

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.

编辑

我很抱歉没有在第一时间提供尽可能多的信息.我觉得这个问题更简单.

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.

我还应该注意，我正在使用EXECUTE IMMEDIATE语句动态构建查询.这是一个示例:

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

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;

触发器在执行EXECUTE IMMEDIATE时失败，并带有前面提到的错误.

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

编辑

我做了更多的研究，我可以提供更多的澄清.

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

我用于创建此触发器的用户帐户不是MAIN_SCHEMA或OTHER_SCHEMA_Xs中的任何一个.我正在使用的(ME)帐户通过架构用户本身获得了对所涉及表的特权.例如(USER_TAB_PRIVS):

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):

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

我具有以下系统特权(USER_SYS_PRIVS):

And I have the following system privileges (USER_SYS_PRIVS):

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

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

And this is what I found in the Oracle documentation:

要在其他用户的帐户中创建触发器 模式，或引用其中的表 您的触发器中的另一个架构 模式，您必须具有CREATE ANY 触发系统特权.有了这个 特权，可以创建触发器 在任何模式下都可以关联 与任何用户的表.此外， 创建触发器的用户必须 也对 引用的过程，功能或 包.

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.

此处: Oracle Doc

所以在我看来这应该可以工作，但是我不确定它在文档中所指的执行权限".

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.

推荐答案

您应该为涉及的每个表和模式执行此操作:

You should execute this for every table and schema involved:

grant select on OTHER_SCHEMA_%.table_name to MAIN_SCHEMA;

这篇关于Oracle数据库中表触发器的多模式特权的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！