ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 如何在不同schema下的table之间创建外键关系?

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

原创 Linux操作系统 作者:NinGoo 时间:2019-06-06 16:21:06 0 删除 编辑

在不同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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 小议Enqueue
请登录后发表评论 登录
全部评论

注册时间:2004-12-07

  • 博文量
    125
  • 访问量
    91837