Обсуждение: AW: New SQL Datatype RECURRINGCHAR
> My feeling is that the restrictions are stringent enough to eliminate > most of the interesting uses of views, and hence an automatic rule > creation feature is not nearly as useful/important as it appears at > first glance. The most prominent of the "interesting uses" probably beeing when the views are part of the authorization system, since views are the only standardized mechanism to restrict access at the row level. Imho not to be neglected. (user xxx is only allowed to manipulate rows that belong to his department, so he is only granted access to a view, not the main table) Andreas
Zeugswetter Andreas SB wrote: > > > > My feeling is that the restrictions are stringent enough to eliminate > > most of the interesting uses of views, and hence an automatic rule > > creation feature is not nearly as useful/important as it appears at > > first glance. > > The most prominent of the "interesting uses" probably beeing when the views > are part of the authorization system, since views are the only standardized > mechanism to restrict access at the row level. True, and often the views can be restricted to insert only data that will be visible using this view. > Imho not to be neglected. > (user xxx is only allowed to manipulate rows that belong to his department, > so he is only granted access to a view, not the main table) This seems to be a little more complicated that Tom described (I.e. it has probably more than one relation involved or uses a function to get CURRENT_USER's department id) IIRC MS Access has much broader repertoire of updatable views than described by Tom. Can be it's an extension to standard SQL though. ----------------- Hannu
Hannu Krosing <hannu@tm.ee> writes:
> Zeugswetter Andreas SB wrote:
>> The most prominent of the "interesting uses" probably beeing when the views
>> are part of the authorization system, since views are the only standardized
>> mechanism to restrict access at the row level.
> True, and often the views can be restricted to insert only data that
> will be visible using this view.
Right. The interesting question is whether an automatic rule creator
could be expected to derive the correct restrictions on
insert/update/delete given the WHERE clause of the view. Insert/delete
might not be too bad (at first thought, requiring the inserted/deleted
rows to pass the WHERE condition would do), but I'm not so sure about
update. Is it sufficient to require both the old and new states of the
row to pass the WHERE condition?
SQL92 gives this restriction on WHERE clauses for updatable views:
d) If the <table expression> immediately contained in QS imme- diately contains a <where clause>
WC,then no leaf generally underlying table of QS shall be a generally underlying table of any
<queryexpression> contained in WC.
which conveys nothing to my mind :-(, except that they're restricting
sub-SELECTs in WHERE somehow. Can anyone translate that into English?
regards, tom lane
Tom Lane wrote: > Hannu Krosing <hannu@tm.ee> writes: > > Zeugswetter Andreas SB wrote: > >> The most prominent of the "interesting uses" probably beeing when the views > >> are part of the authorization system, since views are the only standardized > >> mechanism to restrict access at the row level. > > > True, and often the views can be restricted to insert only data that > > will be visible using this view. > > Right. The interesting question is whether an automatic rule creator > could be expected to derive the correct restrictions on > insert/update/delete given the WHERE clause of the view. Insert/delete > might not be too bad (at first thought, requiring the inserted/deleted > rows to pass the WHERE condition would do), but I'm not so sure about > update. Is it sufficient to require both the old and new states of the > row to pass the WHERE condition? Yes, no other chance. Remember that the rule on SELECT is allways applied to the scan that looks for the rows to update, so you'd never have a chance to hit other rows through the view. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com
Tom Lane writes: > SQL92 gives this restriction on WHERE clauses for updatable views: > > d) If the <table expression> immediately contained in QS imme- > diately contains a <where clause> WC, then no leaf generally > underlying table of QS shall be a generally underlying table > of any <query expression> contained in WC. > > which conveys nothing to my mind :-(, except that they're restricting > sub-SELECTs in WHERE somehow. Can anyone translate that into English? No table mentioned in the FROM-clause (in PG even implicitly) of the query expression (or view definition) is allowed to be mentioned in a subquery in the WHERE clause of the query expression (or view definition). The phrasing "leaf" and "generally" underlying is only to make this statement theoretically pure because you can create generally underlying tables that look different but do the same thing (different join syntax), whereas a leaf generally underlying table is guaranteed to be a real base table. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter