 
> 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

Mon, 9 Jul 2001 12:31:23 +0200 
Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
AW: New SQL Datatype RECURRINGCHAR 
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

Mon, 09 Jul 2001 16:39:19 +0500
Hannu Krosing <hannu@tm.ee>
Re: AW: New SQL Datatype RECURRINGCHAR
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 <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?

			regards, tom lane

Mon, 09 Jul 2001 10:50:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: AW: New SQL Datatype RECURRINGCHAR 
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


Mon, 9 Jul 2001 13:09:03 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: AW: New SQL Datatype RECURRINGCHAR
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


Mon, 9 Jul 2001 23:44:27 +0200 (CEST)
Peter Eisentraut <peter_e@gmx.net>
Re: AW: New SQL Datatype RECURRINGCHAR 