Oracle 表引用另一个模式中的表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9149095/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle table referencing a table from another schema
提问by Lukas Eder
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 Aand B:
当涉及到多模式设置时，我在理解 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
);
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 GRANTstill missing? Is this even possible?
如何使一个架构中的表引用另一个架构中的表？是否还有一些GRANT缺失？这甚至可能吗？
回答by cagcowboy
There are 2 different kinds of privileges: System privs & object privs.
有两种不同的权限：系统权限和对象权限。
GRANT ALL PRIVILEGES TO user;
will grant all system privs to the user and should be used very very carefully!
将授予用户所有系统权限，应非常小心地使用！
GRANT ALL ON table TO user;
will grant SELECT, INSERT etc on a table (ie an object) to the user.
将在表（即对象）上授予用户 SELECT、INSERT 等权限。
So you'll need to do a...
所以你需要做一个...
GRANT ALL ON a.referenced_table TO b;
...after the CREATE TABLE A.REFERENCED_TABLE statement for the above to work.
...在 CREATE TABLE A.REFERENCED_TABLE 语句之后，使上述工作生效。
回答by Henry Wycislo
Grant all is too much for most enterprise environments. Use Grant references instead.
对于大多数企业环境来说，全部授予太多了。请改用 Grant 引用。
Grant references on schema.tablename to target_schema or user;
将 schema.tablename 上的引用授予 target_schema 或用户；
