Oracle 中的references許可權

轉載自：

如果一個表的外來鍵引用的是另一個使用者的表，需要特別的許可權嗎？答案就是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