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 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

"dana" <dana_at_work (AT) yahoo (DOT) com> wrote

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.

On Jul 14, 7:53*am, "Carl Kayser" <kayse... (AT) bls (DOT) gov> wrote:
When the object owner, userA, grants userB, REFERENCES, userA has in
effect given userB the right to stop userA from updating their own
table unless userA's action meets the requirement imposed by userB's
FK.

In a large application where multiple owners are definded for areas/
departments you would expect that the need for FK to reference other
owners tables will exist.

HTH -- Mark D Powell --

Thanks Carl and Mark.

Mark, you wrote:
Thanks Mark. Wouldn't it be "more correct" or more complete to add to
the above (although it could be deduced):

.... if and only if userB then proceeds to create a foreign key
referencing userA's table? UserA granting REFERENCES to userB doesn't
auto-generate a FK in userB's schema from userB's table to userA's
table. It's just a pre-condition for userA electing to do generate
that key, and volunteering, as it were, to be so constrained. Unless
I've misunderstood you--quite possible. As the OP, I'm the confused
one here by definition. :-)

Just wanted to make things as clear as possible for myself and for
posterity.

Makes sense. But are there any "gotchas" you know of in doing so? I
guess the biggest "anti-gotcha" would be getting in trouble by
duplicating Parent tables from other tables to avoid having to request
a REFERENCES grant from the schema owner. The whole point, after all,
with databases (at least a significant one) is to reduce or eliminate
data duplication (except for controlled redundancy for a good, well-
understood, specific purpose).

Carl, you wrote:

This makes sense to me too. Unless there's a subtlety I've missed--
again, quite possible.

Thanks again guys. I appreciate the replies.

Dana

Any additional thoughts on this?

Thanks.

Going once, going twice. :-) Trying to get complete closure on this
one for myself--so I don't have to think about it any further.