[SOLVED] How Grant Create Foreign Key on another schema?

How Grant Create Foreign Key on another schema?

I have two oracle users : User1 and User2.

User1 has a table Users_
User2 has a table TestTable

I'm trying to create a foreign key constraint between the two tables as follows :

ALTER TABLE "User2"."TESTTABLE" ADD CONSTRAINT "TESTTABLE_CREATEDBY"
FOREIGN KEY (CREATEDBY) REFERENCES "User1"."USERS_" (ID) ENABLE

User2 has the privilege to select on User1 table Users_ (Grant select to user2 on Users_)

When running the alter table statement I'm having an error : of insufficient privileges.

Does anyone know how to solve that please ?

Cheers,

Solution

To create a foreign key against a table in another schema we need to have the REFERENCES privilege on that table. This is a separate privilege because it imposes a burden on the table's owner: they can't delete records from their table if you're referencing them. Find out more