Muti-Schema privileges for table trigger in Oracle database

Question

I am trying to write a table trigger that requests another table that is outside of the schema where the trigger will be. Is it possible? It seems that I have no problem querying tables in my schema, but I get:

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

when trying to query tables outside my schema.

EDIT

My apologies for the fact that I did not provide as much information as possible for the first time. I got the impression that this question was simpler.

I am trying to create a trigger in a table that changes some fields in a newly inserted row based on the existence of some data, which may or may not be in a table that is in a different schema.

The user account that I use to create the trigger has permissions to run queries myself. In fact, I had a trigger that issues a request that I am trying to run, and was able to successfully run it on it.

I should also note that I am building the query dynamically using the EXECUTE IMMEDIATE statement. Here is 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;

Trigger seams fail on EXECUTE IMMEDIATE with the previously mentioned error.

EDIT

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

The user account I use to create this trigger is not MAIN_SCHEMA or any of OTHER_SCHEMA_X. The account I use (ME) is granted privileges for the involved tables through 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):

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

Oracle:

, TRIGGER. , . , , , EXECUTE , .

: Oracle Doc

, , " EXECUTE", .

Answer 1

:

grant select on OTHER_SCHEMA_%.table_name to MAIN_SCHEMA;

Answer 2

, , - Oracle. - . , , .

:

grant select, insert on my_table to you
/

GRANT ANY (, DBA) .

grant select, insert on apc.my_table to you
/

, . , - , , - , .

, , .

....

insert into apc.whatever_table  values ...

create synonym whatever for apc.whatever_table;

Answer 3

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