Oracle table referencing a table from another schema

Question

I'm having some trouble understanding what's possible and what's not possible in Oracle when it comes to multi-schema setups. Let's say I have two schemata A and B:

-- with user SYS connect as SYSDBA
-- note: ALL PRIVILEGES are granted for simplicity in the scope of this question.
--       real life databases would have more fine-grained grants...
create user A identified by A;
grant all privileges to A;

create user B identified by B;
grant all privileges to B;

-- with user A
create table A.REFERENCED_TABLE (
  ID number(7) not null,
  constraint REFERENCED_TABLE_PK primary key (ID)
);

-- with user A or B
create table B.REFERENCING_TABLE (
  A_ID number(7) not null,
  constraint REFERENCING_TABLE_FK 
    foreign key (A_ID) 
    references A.REFERENCED_TABLE(ID)
    on delete cascade
);

But the above statement causes

ORA-01031: insufficient privileges

How can I make a table from one schema reference a table from another schema? Is there some GRANT still missing? Is this even possible?

Solution 1

There are 2 different kinds of privileges: System privs & object privs.

GRANT ALL PRIVILEGES TO user;

will grant all system privs to the user and should be used very very carefully!

GRANT ALL ON table TO user;

will grant SELECT, INSERT etc on a table (ie an object) to the user.

So you'll need to do a...

GRANT ALL ON a.referenced_table TO b;

...after the CREATE TABLE A.REFERENCED_TABLE statement for the above to work.

Solution 2

Grant all is too much for most enterprise environments. Use Grant references instead.

Grant references on schema.tablename to target_schema or user;