Re: more on delete from join

From: Kevin Kirkpatrick <>
Date: Thu, 27 Aug 2009 11:52:22 -0700 (PDT)
Message-ID: <>

On Aug 26, 8:34 pm, paul c <> wrote:
> paul c wrote:
> > Kevin Kirkpatrick wrote:
> > ...
> >> In short, before delving into, "*how* should the DBMS handle view
> >> updates?", I'd like to see a discussion about the question, "*should*
> >> the DBMS handle view updates?".
> > Wow, that's far out.  To want a dbms never to update views might be
> > something an ostrich would want but not I.  Better to want a logic that
> > permits view updates.  Lots of pragmatic reasons, security, archiving,
> > optimization and data re-arrangement are just a few off the top of my
> > head.  ...
> Oh, integrity too, say to keep people out of last year's account after
> the books close!

First of all, I'm using the Google Groups web-based interface, and it appears that this thread has gotten a bit garbled in tracking who is replying to whom. So I had no idea until minutes ago that I'd had the above messages addressed to me in the first place... anyway, I apologize in advance if I'm slow to reply to messages addressed to me, or reply to them out of order.

Now, I'm going to start responding to your comments by pointing out that I take your first sentence as a compliment. It is incredibly rare that ideas are given such distinction on cdt without immediately (and rightfully!!) being relegated to crank-dom. Having said that, as this is comp.database.*theory*, I'd ask that we not dismiss ideas simply because they have immediate pragmatic ramifications to the way today's DBMS's operate. After all, there are lots of nifty and "useful" things that can be done with table triggers, and database designers who rely heavily on table triggers may scoff at the idea of building a DBMS which lacks trigger facilities, saying "maybe it's something an ostrich would want..." ;-)

I guess my point here is, if updating virtual relvars is not on sound theoretic footing (and until somebody can point to a sound theory that accommodates the assertion of conclusions in order to derive propositions, it remains there in my mind), then perhaps can we take the things it is traditionally used to address and work out a more theoretically sound approach (and then perhaps compare the approaches from a pragmatism perspective).

The first thing you mention that updatable views address: security. Let's jump right into it with a sample data model (given as base relvars and associated predicates):

EMP {ENO, ENAME, SAL} -- "<ENO> is named <ENAME> and has salary <SAL>"
BOSS {ENO, BOSS_ENO} -- "<ENO> has boss <BOSS_ENO>" CURRENT_USER -- "The current database connection has been authenticated as <ENO>" -- note, this is a view (not updatable, of course!)

And business rules:

1) Employees can view ENO and ENAME for everybody
2) Employees can update their own ENAME.
3) Employees can view their own SAL
4) Bosses can view and update ENAME and SAL of employees who report to

The challenge - can these business rules be enforced w/out using updatable views? I'd argue, "yes", but only with the introduction of some new features.

First, for reasons I'll make clear later, let's put this into 6th normal form:

EMP_NAME{ENO,  ENAME} "<ENO> is named <ENAME>"
EMP_SAL {ENO,  SAL} "<ENO> has salary <SAL>"
BOSS {ENO, BOSS_ENO} "<ENO> works for <BOSS_ENO>"
CURRENT_USER "The current database connection has been authenticated as <ENO>" -- note, this is a view (not updatable, of course!)

Now, some "standard" grants:

GRANT SELECT ON EMP_NAME TO PUBLIC GRANT SELECT ON BOSS TO PUBLIC And here, I introduce a new feature: "restriction grants". Basic syntax:

Essentially, the restriction is applied to all operand tuples of the operation at hand. The restriction will be applied at runtime, and it could either return a runtime error or simply filter the operand tuples before performing the operation. My *strong* preference is for the runtime error.

So, in this case, we could have restriction grants as follows: GRANT SELECT ON EMP_SAL TO PUBLIC WHERE ENO MATCHES (CURRENT_USER UNION {BOSS_ENO} JOIN (CURRENT_USER, BOSS)) GRANT INSERT, DELETE ON EMP_NAME TO PUBLIC WHERE ENO MATCHES (CURRENT_USER UNION {BOSS_ENO} JOIN (CURRENT_USER, BOSS)) GRANT INSERT, DELETE ON EMP_SAL TO PUBLIC WHERE ENO MATCHES ({BOSS_ENO} JOIN (CURRENT_USER, BOSS)) Note, I do not specify UPDATE access - from a privilege perspective, UPDATE should be treated as a INSERT/DELETE combo (hence, the move to 6th NF). This is mostly because I do *not* want or intend to present a solution which conflicts with any earlier stances I may have taken on the topic of "transition constraints".

Hopefully things aren't so muddled as to obscure what this approach accomplishes. Effectively, we've left the data modeling and enforcement of business rules in the domain of the data modeler, and views and application code in the realm of application designers. A quick swipe at "updatable views": ever stopped to wonder why the data modeler would or should pick view names, much less decide how updates to those views should be interpreted, instead of the application designer?

Anyway, I'll stop here - is this going in a direction that is even remotely interesting? Received on Thu Aug 27 2009 - 20:52:22 CEST

Original text of this message