如果一个表的外键引用的是另一个用户的表,需要特别的权限吗?答案就是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

Trackback

only 1 comment untill now

  1. 不错,原先还真不了解有这个权限。

    [回复]

Add your comment now