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
them.
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:
GRANT <OPERATION_LIST> ON <TUPLE_SOURCE> TO <ROLE> [<RESTRICTION>]
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?
