2

I'm trying to grant user1 permission to create (and alter) tables in other schemas. So far i've been able to create "simple" tables. But when it come to create tables with foreign keys got error

I have oracle database 12c with 2 users/schemas. User1 granted permissions to create tables in user2 schema:

grant create any table to user1

(ok, probably not the best solution but doesn't matter right now)

now when i'm trying to create table with foreign key i'm getting standard message

Error: ORA-01031: insufficient privileges [Failed SQL: create table…

i've been trying

grant references to user1

solution from this question

got

ORA-01924: role 'REFERENCES' not granted or does not exist

(ok, it's true, there is no role 'REFERENCES' but i don't need new role or i don't understand how it will help in this situation)

also

grant references on testTable to user1

works but i need to be able to grant permission to any table even for those that will be created in future

Alexander
  • 177
  • 2
  • 9

1 Answers1

3

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>
Francisco Sitja
  • 963
  • 4
  • 7