Referential integrity across schemas with synonyms?

I've got a schema/user A that contains several core tables for an application.

Several different, disparate applications wish to interface with the data in schema A. In fact, they are essentially adding there own front end to the tables repesented in schema A.

My thought was to create a new schema, B, and then use synonyms to shadow the tables in schema A so that the development can proceed against schema B with none being the wiser that A is even involved.

But, alas, something seems to be missing. I was hoping someone could tell me what I'm missing.

Here's an example of what I want to do:

CREATE ROLE SERVER_USER NOT IDENTIFIED; CREATE USER "SERVER" IDENTIFIED BY "XYZ"     DEFAULT TABLESPACE "DATA01"
    TEMPORARY TABLESPACE "TEMP"
    PROFILE DEFAULT ACCOUNT UNLOCK; GRANT "CONNECT" TO "SERVER" WITH ADMIN OPTION; GRANT "RESOURCE" TO "SERVER" WITH ADMIN OPTION; GRANT UNLIMITED TABLESPACE TO "SERVER" WITH ADMIN OPTION; GRANT SERVER_USER TO SERVER WITH ADMIN OPTION; ALTER USER "SERVER" DEFAULT ROLE ALL; CREATE USER "APP01" IDENTIFIED BY "XYZ"     DEFAULT TABLESPACE "DATA01"
    TEMPORARY TABLESPACE "TEMP"
    PROFILE DEFAULT ACCOUNT UNLOCK; GRANT "CONNECT" TO "APP01" WITH ADMIN OPTION; GRANT "RESOURCE" TO "APP01" WITH ADMIN OPTION; GRANT UNLIMITED TABLESPACE TO "APP01" WITH ADMIN OPTION; GRANT SERVER_USER TO APP01 WITH ADMIN OPTION; ALTER USER "APP01" DEFAULT ROLE ALL; CONNECT SERVER/XYZ_at_SYSTEM; CREATE TABLE TEST_TABLE (
    ID NUMBER NOT NULL PRIMARY KEY
);

GRANT ALL ON TEST_TABLE TO SERVER_USER; CONNECT APP01/XYZ_at_SYSTEM; CREATE SYNONYM TEST_TABLE FOR SERVER.TEST_TABLE; CREATE TABLE APP_TABLE(
    ID NUMBER NOT NULL PRIMARY KEY,
    REF NUMBER NOT NULL REFERENCES TEST_TABLE(ID)
);

The CREATE TABLE APP_TABLE complains about "table or view does not exist".

Yet, as the APP01 user I can describe TEST_TABLE, and SELECT * FROM TEST_TABLE. Is ORACLE not going to let me do this? I don't understand why it's having such a hissy fit over this. If someone could fill me in, I'd appreciate it.

I would think that this would be a slam dunk.

Thanx!

Best Regards,

Will Hartung
(willh_at_msoft.com) Received on Mon Jun 07 1999 - 16:00:09 CDT