Jul 23

oracle refrences权限

admin , 09:26 , 数据库 » 数据库(Oracle基础) , 评论(0) , 引用(0) , 阅读(2863) , Via 本站原创 | |
怎么手机十秒时时监控          拍拍购物综合频道 正品1折起 惊喜多多      拍拍女装特卖频道,汇集了最新的女装时尚新款 正品1折起,爱美的我还在等什么
     手机访问 就能天天 特价     电脑访问 爱淘宝go

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