Re: Why is a REFERENCES privilege required? And is it "better" to create FK relationship only between tables in the same schema?

"dana" <dana_at_work_at_yahoo.com> wrote in message news:70e3a29a-7575-41c0-b3ad-0e1dd70eeff8_at_r25g2000vbn.googlegroups.com...
> Not too long ago, I needed to create a FK relationship from a table in
> my schema to a table in another schema on the same database instance.
> I learned that I needed to be granted the REFERENCES privilege on the
> "remote" table.
>
> Why are things set-up this way? And is it ill-advised to be creating
> FK relationships to tables in "remote" schemas? If so, why? Should FK
> relationships only be established between tables in the same schema.
>

I'm not an Oracle guy but REFERENCES is ANSI defined. My take is that it allows you to put rows in your table that is validated against another table for which you may not be granted SELECT. So (it seems to me that) this is an unusual security/validation feature for RDBMSs. If your table is supposed to have a (key-wise) subset of another table and the key information is "sensitive" in the "parent table" then this feature supports the situation.

> I don't have an agenda here other than trying to learn some Best
> Practices in this regard. And I'm curious about why things are as they
> are regarding the pre-condition of a REFERENCES grant.
>
> Thanks.
>
> Dana
Received on Tue Jul 14 2009 - 06:53:49 CDT