时间戳

首页 > 解决方案 > 如何在 oracle sql 12.2.0.1.0 中授予引用？

oracle - 如何在 oracle sql 12.2.0.1.0 中授予引用？

问题描述

标签： oracleforeign-keysgrant

解决方案

There is no system privilege "REFERENCE ANY TABLE" in Oracle. https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/GRANT.html#GUID-20B4E2C0-A7F8-4BC8-A5E8-BE61BDC41AC3

What you can do is grant "create any table" to first create the table and then "alter any table" to add the foreign key constraint to another user's table.

Note in the sample below that user1 still needs explicit references privilege on user2's table to be able to have a foreign key to it.

FSITJA@db01 2019-06-25 11:43:14> create user user1 identified by 123 quota unlimited on users;

User created.

FSITJA@db01 2019-06-25 11:43:14> grant create session to user1;

Grant succeeded.

FSITJA@db01 2019-06-25 11:43:14> grant create any table, create any index, alter any table to user1;

Grant succeeded.

FSITJA@db01 2019-06-25 11:43:14> create user user2 identified by 123 quota unlimited on users;

User created.

FSITJA@db01 2019-06-25 11:43:14> conn user1/123@db01
Connected.

Session altered.

USER1@db01 2019-06-25 11:43:15> create table user2.t1(col1 number primary key);

Table created.

USER1@db01 2019-06-25 11:43:15> create table user2.t2(col1 number);

Table created.

USER1@db01 2019-06-25 11:43:15> alter table user2.t2 add constraint fk_t2_t1 foreign key (col1) references user2.t1(col1)
;

Table altered.

USER1@db01 2019-06-25 11:43:15> create table user1.t3(col1 number);

Table created.

USER1@db01 2019-06-25 11:43:15> alter table user1.t3 add constraint fk_t3_51 foreign key (col1) references user2.t1(col1)
;
alter table user1.t3 add constraint fk_t3_51 foreign key (col1) references user2.t1(col1)
                                                                                 *
ERROR at line 1:
ORA-01031: insufficient privileges


USER1@db01 2019-06-25 11:43:15> conn user2/123@db01
Connected.

Session altered.

USER2@db01 2019-06-25 11:43:16> grant references on user2.t1 to user1;

Grant succeeded.

USER2@db01 2019-06-25 11:43:16> conn user1/123@db01
Connected.

Session altered.

USER1@db01 2019-06-25 11:43:16> alter table user1.t3 add constraint fk_t3_51 foreign key (col1) references user2.t1(col1)
;

Table altered.

USER1@db01 2019-06-25 11:43:20>

推荐阅读