Re: How I can create a foreign key with 2 tables having different owner?

A copy of this was sent to "Patrice Leclerc" <patricel_at_mobilis.qc.ca> (if that email address didn't require changing) On Thu, 2 Sep 1999 11:48:47 -0400, you wrote:

>I try to execute this and I have the following error:
>ORA-00942 table or view does not exist.
>
>SQL> ALTER TABLE ADM_EL.EL_ENTREE_ELECTRI ADD (
> 2 CONSTRAINT EL_EE_MAP_FK1
> 3 FOREIGN KEY (MAPID)
> 4 REFERENCES ADM_MGE.MAPS (MSLINK)
> 5 )
> 6 ;
> REFERENCES ADM_MGE.MAPS (MSLINK)
> *
>ERROR at line 4:
>ORA-00942: table or view does not exist
>
>The table MAPS exist and I have also create a public synonym MAPS on
>ADM_MGE.MAPS
>
>Please help me.
>
>---------------------------------
>Patrice Leclerc
>patricel_at_mobilis.qc.ca
>Systemes Mobilis
>

adm_el needs to have been granted all or references on maps. for example:

tkyte_at_8.0> grant connect, resource to a identified by a; Grant succeeded.

tkyte_at_8.0> grant connect, resource to b identified by b; Grant succeeded.

tkyte_at_8.0> connect a/a
Connected.
tkyte_at_8.0> create table t ( x int primary key ); Table created.

tkyte_at_8.0> grant references on t to b;
Grant succeeded.

tkyte_at_8.0> connect b/b
Connected.
tkyte_at_8.0> create table t ( x int references a.t ); Table created.

synonyms do not imply any level of granting access. they are just a shortcut to an object -- you still need the underlying grants to be in place.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 12:56:49 CDT