(2010/07/22 10:04), Stephen Frost wrote:
> * KaiGai Kohei (kaigai@ak.jp.nec.com) wrote:
>> We can find out a similar case in CreateTrigger().
>> If I was granted TRIGGER privilege on a certain table, I can create a new
>> trigger on the table without its ownership. More commonly, it allows us
>> to modify a certain property of the table without its ownership.
>
> TRIGGER is hardly the same as REFERENCES. If we invented a new priv, it
> would be more like 'FK_CREATE'.
>
>> Perhaps, if SQL permission would be more fine-grained, for example,
>> "RENAME" permission might control RENAME TO statement, rather than
>> its ownership.
>
> This wouldn't actually be any more fine-grained, it'd just be adding
> rights on to an existing priv, which I think is a wholly *bad* idea.
>
>> What is the reason why we check its ownership here, although we already
>> have REFERENCES permission to control ADD FOREIGN KEY?
>
> REFERENCES is needed on the REFERENCED table, ownership is needed on the
> REFERENCING table. They're not the same..
>
> We only allow owners of objects to change the structure of those
> objects. Adding a FK to another table doesn't really change the
> structure of the referenced table. Adding a FK does though, imv.
>
However, existing ATAddForeignKeyConstraint() checks REFERENCES
permission on both of the referencing and referenced table/columns.
Is it unexpected behavior???
It is an agreeable interpretation that we need ownership on the
referencing table because creating a new FK equals to change
a certain property of the referencing table.
If so, why REFERENCES permissions are necessary on the referencing
side, not only referenced side?
Thanks,
--
KaiGai Kohei <kaigai@ak.jp.nec.com>