Tom Lane wrote:
> Peter Eisentraut <peter_e@gmx.net> writes:
>> The SQL standard uses a recursive-by-default language. For example, the
>> rules for the DELETE command state:
>
> Actually, I'm not convinced. Take a look at the SELECT WITH HIERARCHY
> OPTION stuff in SQL99 and later, in particular this from SQL99
> 12.2 <grant privilege statement>:
Ah, the mysterious HIERARCHY OPTION comes into play. That appears to be
the ticket.
> 7) Let SWH be the set of privilege descriptors in CPD whose action
> is SELECT WITH HIERARCHY OPTION, and let ST be the set of
> subtables of O, then for every grantee G in SWH and for every
> table T in ST, the following <grant statement> is effectively
> executed without further Access Rule checking:
>
> GRANT SELECT ON T TO G GRANTED BY A
>
> It's difficult to read that any other way than that privileges are *not*
> auto-recursive, and they have chosen to spell "*" in GRANT as "WITH
> HIERARCHY OPTION" (gackk).
Er, well, I see this piece from SQL:2008 on <table reference>:
"""
1) Case:
[...]
B) [...], the current privileges shall include SELECT on at least one
column of T.
2) If TP simply contains <only spec> and TN identifies a typed table, then
Case:
[...]
B) [...], the current privileges shall include SELECT WITH HIERARCHY
OPTION on at least one supertable of T.
"""
(The omitted phrases deal with SECURITY INVOKER situations.)
I read that as that privileges are auto-recursive, and that you need the
hierarchy option to be permitted to use ONLY. (So the hierarchy option
is an additional privilege on top of SELECT that allows you to break the
encapsulation of the inheritance setup.)
> On the other hand, it's hard to square that reading with the lack of any
> UPDATE or DELETE WITH HIERARCHY OPTION syntax. What am I missing here?
You need SELECT with or without HIERARCHY, as the case may be, to locate
the row. Once you have located it, you can UPDATE or DELETE it
depending on privilege, but then it doesn't matter anymore how you got it.
