Home » RDBMS Server » Security » Problem with security and cross-schema foreign keys
Problem with security and cross-schema foreign keys [message #243684] Fri, 08 June 2007 07:01 Go to next message
rmkrueger
Messages: 2
Registered: June 2007
Junior Member
Hi,

I'm stuck with the following problem. Suppose I wanted to execute an SQL-Script that creates two tables in different schemas with foreign keys referencing each other like

create table TEST1.TABLE1 (
PK INTEGER PRIMARY KEY,
FK INTEGER
);
create table TEST2.TABLE2 (
PK INTEGER PRIMARY KEY,
FK INTEGER
);
alter table TEST1.TABLE1 add constraint FK1 foreign key(fk) references TEST2.TABLE2;
alter table TEST2.TABLE2 add constraint FK2 foreign key(fk) references TEST1.TABLE1;

Now, is there any combination of privileges that would allow a user to execute that sequence of statements successfully? Right now my understanding is that there is no such thing as a REFERENCE ANY TABLE privilege and therefore this is not possible. Is this correct or am I missing something?

The only way I can see is to add the respective object privileges (REFERENCES) after the tables have been created, which would alter the script, which is what I want to avoid.

Thanks in advance,

Robert

Re: Problem with security and cross-schema foreign keys [message #243692 is a reply to message #243684] Fri, 08 June 2007 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not your DBA user (the one that launches the script) that need a "references" privilege on the table, this is each user. You have to grant them explicitly:
SQL> create user test1 identified by test1 quota unlimited on ts_d01;

User created.

SQL> create user test2 identified by test2 quota unlimited on ts_d01;

User created.

SQL> create table TEST1.TABLE1 (
  2  PK INTEGER PRIMARY KEY,
  3  FK INTEGER
  4  );

Table created.

SQL> create table TEST2.TABLE2 (
  2  PK INTEGER PRIMARY KEY,
  3  FK INTEGER
  4  );

Table created.

SQL> grant references on TEST2.TABLE2 to TEST1;

Grant succeeded.

SQL> grant references on TEST1.TABLE1 to TEST2;

Grant succeeded.

SQL> alter table TEST1.TABLE1 add constraint FK1 foreign key(fk) references TEST2.TABLE2;

Table altered.

SQL> alter table TEST2.TABLE2 add constraint FK2 foreign key(fk) references TEST1.TABLE1;

Table altered.

Regards
Michel
Re: Problem with security and cross-schema foreign keys [message #243701 is a reply to message #243692] Fri, 08 June 2007 08:56 Go to previous message
rmkrueger
Messages: 2
Registered: June 2007
Junior Member
I was afraid so. So it is definitely not possible to execute this script unchanged. I was hoping there was some way to give one user the privileges to make all these metadata manipulations some kind of "all" privilege that would do the job.

Thanks a lot for the info.

Robert
Previous Topic: DB password limitations/constraints
Next Topic: looking for sql statements ran and date/time
Goto Forum:
  


Current Time: Thu Mar 28 11:16:38 CDT 2024