Oracle 表引用另一个架构中的表
当涉及到多模式设置时，我无法理解 Oracle 中什么是可能的，什么是不可能的。假设我有两个模式 A 和 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
);
但是上述语句会导致
ORA-01031: insufficient privileges
如何使一个模式中的表引用另一个模式中的表？是否还缺少一些GRANT？这可能吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
有两种不同类型的权限：系统权限和系统权限。对象权限
将向用户授予所有系统权限，应该非常非常小心地使用！
将向用户授予对表（即对象）的 SELECT、INSERT 等操作。
因此，您需要
在 CREATE TABLE A.REFERENCED_TABLE 语句之后执行... ...以上操作才能工作。
There are 2 different kinds of privileges: System privs & object privs.
will grant all system privs to the user and should be used very very carefully!
will grant SELECT, INSERT etc on a table (ie an object) to the user.
So you'll need to do a...
...after the CREATE TABLE A.REFERENCED_TABLE statement for the above to work.
对于大多数企业环境来说，授予一切都太过分了。请改用格兰特参考文献。
将 schema.tablename 上的引用授予 target_schema 或 user；
Grant all is too much for most enterprise environments. Use Grant references instead.
Grant references on schema.tablename to target_schema or user;