如果一个表的外键引用的是另一个用户的表，需要特别的权限吗？答案就是refrences权 限。虽然一个schema（用户）下表的外键引用的是其他schema（用户）的表，是一种不太好的设计。但现实中仍然会有这种情况。下面来看看 reference的作用：
测试环境：
Oracle 10.2.0.1
Redhat Linux AS4
数据库里用于测试的两个用户test1和test2，只有connect角色权限和表空间使 用权限。
SQL> connect / as sysdba
Connected.
SQL> create table test1.t1 as select * from dba_objects where rownum< =1000;
Table created.
SQL> create table test2.t2 as select * from dba_objects where rownum< =1000;
Table created.
SQL> alter table test1.t1 add constraint pk_t1 primary key(object_id);
Table altered.
现在，我们用用户test2连接到数据库，在表test2.t2的object_id字段上 增加一个外键，外键引用test1.t1表的object_id字段：
SQL> connect test2/test
Connected.
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-00942: table or view does not exist
我们将test1.t1表的查询权限赋给test2：
SQL> grant select on test1.t1 to test2;
Grant succeeded.
再次增加外键：
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-01031: insufficient privileges
可以看到报权限不足。我们再看看如果将DBA权限给test2会怎么样：
SQL> connect / as sysdba
Connected.
SQL> grant dba to test2;
Grant succeeded.
SQL> connect test2/test
Connected.
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-01031: insufficient privileges
还是报权限不足。
SQL> connect / as sysdba
Connected.
SQL> revoke dba from test2;
Revoke succeeded.
SQL> grant references on test1.t1 to test2;
Grant succeeded.
SQL> connect test2/test
Connected.
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
Table altered.
SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name,status,validated,invalid from user_constraints;
OWNER CONSTRAINT_NAME C TABLE_NAME R_OWNER R_CONSTRAINT_NAME STATUS VALIDATED INVALID
---------- -------------------- - ---------- ---------- -------------------- ---------- ---------- ----------
TEST2 FK_T2 R T2 TEST1 PK_T1 ENABLED VALIDATED
我们看到将test1.t1表的references权限给test2后，外键增加成功。
如果这个时候取消这个权限，会怎么样？
SQL> revoke references on test1.t1 from test2;
revoke references on test1.t1 from test2
*
ERROR at line 1:
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke
可以看到由于已经有了引用其他表的外键，不能直接取掉这个权限。
SQL> revoke references on test1.t1 from test2 cascade constraints;
Revoke succeeded.
再看看test2用户的外键：
SQL> select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name,status,validated,invalid from user_constraints;
no rows selected
加了cascade constraints之后的取消权限语句，直接导致了test2用户上的外键被删除。
SQL> grant references on test1.t1 to dba;
Grant succeeded.
SQL> grant dba to test2;
Grant succeeded.
SQL> connect test2/test
Connected.
SQL> alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id);
alter table t2 add constraint fk_t2 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-01031: insufficient privileges
如上面所示，references权限只能直接赋予给用户，通过角色赋予用户，没有作用。另 外，没有系统级的references权限，如references any table等。
SQL> grant references on test1.t1 to test2;
Grant succeeded.
SQL> grant references on test1.t1 to dba;
Grant succeeded.
SQL> select * from role_tab_privs where privilege like ‘%REFERENCES%’;
no rows selected
SQL> select grantee,owner,table_name,grantor,privilege from dba_tab_privs where privilege like ‘%REFERENCES%’;
GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE
------------ ---------- -------------------- ---------- ---------------
SYSTEM SYS INCEXP SYS REFERENCES
SYSTEM SYS INCVID SYS REFERENCES
SYSTEM SYS INCFIL SYS REFERENCES
TEST2 TEST1 T1 TEST1 REFERENCES
我们看到将references赋给role并没有起作用，虽然grant语句没有报错，但 在role_tab_privs中并没有记录。
虽然DBA角色没有references权限，但是可以将任意一个表的references 权限赋给他人（这是因为DBA角色具有GRANT ANY OBJECT PRIVILEGE权限）。SYS也需要显式的某个表上赋予references权限才能引用那个表。
SQL> create table t3 as select * from dba_objects where object_id< =1000;
Table created.
SQL> alter table t3 add constraint fk_t3 foreign key (object_id) references test1.t1(object_id);
alter table t3 add constraint fk_t3 foreign key (object_id) references test1.t1(object_id)
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> grant dba to test2;
Grant succeeded.
SQL> connect test2/test
Connected.
SQL> grant references on test1.t1 to sys;
Grant succeeded.
SQL> connect / as sysdba
Connected.
SQL> alter table t3 add constraint fk_t3 foreign key (object_id) references test1.t1(object_id);
Table altered
http://hi.baidu.com/suofang/blog/item/40380655a4a242c9b745aeb6.html
