如何在不同schema下的table之间创建外键关系？

在不同schema下的table之间创建外键引用，一直报ORA-01031: insufficient privileges，即使给了dba权限还是如此？

NING@ning>create user test1 identified by test1;

User created.

NING@ning>create user test2 identified by test2;

User created.

NING@ning>grant connect,resource to test1;

Grant succeeded.

NING@ning>grant connect,resource to test2;

Grant succeeded.

NING@ning>create table test1.main(i int primary key,a varchar2(10));

Table created.

NING@ning>create table test2.child(id references test1.main(i),a varchar2(10));
create table test2.child(id references test1.main(i),a varchar2(10))
*
ERROR at line 1:
ORA-00942: table or view does not exist

ok，报这个错是由于test2用户没有test1.main的select权限

NING@ning>grant select on test1.main to test2;

Grant succeeded.

NING@ning>create table test2.child(id references test1.main(i),a varchar2(10));
create table test2.child(id references test1.main(i),a varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

已经能select了，还是报权限不足

NING@ning>grant dba to test2;

Grant succeeded.

NING@ning>create table test2.child(id references test1.main(i),a varchar2(10));
create table test2.child(id references test1.main(i),a varchar2(10))
*
ERROR at line 1:
ORA-01031: insufficient privileges

已经授予DBA角色了，还是权限不足

NING@ning>grant references on test1.main to test2;

Grant succeeded.

NING@ning>create table test2.child(id references test1.main(i),a varchar2(10));

Table created.

可见，外键引用其他schema的table，需要有select on table和references on table的权限。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50327/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50327/