OraFAQ Forum: Security » Problem with security and cross-schema foreign keys

Home » RDBMS Server » Security » Problem with security and cross-schema foreign keys

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Problem with security and cross-schema foreign keys [message #243684]

Fri, 08 June 2007 07:01

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

Report message to a moderator

Re: Problem with security and cross-schema foreign keys [message #243692 is a reply to message #243684]

Fri, 08 June 2007 08:07

Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Problem with security and cross-schema foreign keys [message #243701 is a reply to message #243692]

Fri, 08 June 2007 08:56

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 11 20:00:04 CST 2024