Re: Why is a REFERENCES privilege required? And is it "better" to create FK relationship only between tables in the same schema?
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Tue, 14 Jul 2009 06:16:37 -0700 (PDT)
Message-ID: <841be106-6fbb-4379-8465-2dad406ff818_at_r25g2000vbn.googlegroups.com>
On Jul 14, 7:53 am, "Carl Kayser" <kayse..._at_bls.gov> wrote:
> "dana" <dana_at_w..._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- Hide quoted text -
>
> - Show quoted text -
Date: Tue, 14 Jul 2009 06:16:37 -0700 (PDT)
Message-ID: <841be106-6fbb-4379-8465-2dad406ff818_at_r25g2000vbn.googlegroups.com>
On Jul 14, 7:53 am, "Carl Kayser" <kayse..._at_bls.gov> wrote:
> "dana" <dana_at_w..._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- Hide quoted text -
>
> - Show quoted text -
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 -- Received on Tue Jul 14 2009 - 08:16:37 CDT