oracle在reference使用,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