I have a 'normal' schema S, and a schema that stores history H (it contains all tables of S, added with some audit columns, but stripped of any keys).
Whenever a DML happens on S, it has to be stored within H (If S contains a table T, and we insert a record, H will contain a table T' with all the new inserted data, with a timestamp, user and a predicate 'INSERT').
Due to the nature of the application, and for security reasons, we have following restrictions:
- S and H cannot create triggers
- No synonyms nor grants are created between S and H.
Now we need triggers to replicate the DML on S towards H, so the solution was to create the triggers within schema D, which has the role of DBA:
CREATE TRIGGER TR_T
AFTER INSERT OR UPDATE ON S.T
FOR EACH ROW
BEGIN
INSERT INTO H.T' VALUES('INSERT',user,sysdate,:new, ...);
END;
The problem is that although
SELECT * FROM S.T
and
SELECT * FROM H.T'
both give results when queried from D
the trigger itself doesn't compile (stays invalid) for the reason
ORA-00942: table or view does not exist
pointing to the H.T'
Replacing it with T' (so without the schema-prefix) doesn't work either
If I add a synonym for H.T', it works, but as I said, this isn't an option for us.

grant dba to dbau identified by dbau;
grant unlimited tablespace to su identified by su;
grant unlimited tablespace to hu identified by hu;

create table su.t (
  x int 
);

create table hu.th (
  x int 
);

conn dbau/dbau

create or replace trigger trg 
before insert on su.t
for each row
begin
  insert into hu.th values (:new.x);
end;
/
sho err

LINE/COL ERROR
-------- -----------------------------------------------
2/3      PL/SQL: SQL Statement ignored
2/18     PL/SQL: ORA-00942: table or view does not exist

conn chris/chris

grant select, insert on su.t to dbau;
grant select, insert on hu.th to dbau;

conn dbau/dbau

create or replace trigger trg 
before insert on su.t
for each row
begin
  insert into hu.th values (:new.x);
end;
/
sho err

No errors.