Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> comp.databases.theory -> Re: Requirements for update languages?

Re: Requirements for update languages?

From: Leandro Guimarães Faria Corsetti Dutra <>
Date: Mon, 11 Nov 2002 17:00:36 +0100
Message-ID: <aqok7l$bu4an$>

Jens Lechtenbörger wrote:

> Leandro Guimarães Faria Corsetti Dutra writes:

>> Jens Lechtenbörger wrote:
>>> 2. As a db user, I expect that I can undo (inadvertent) data 
>>> manipulations, e.g., undo an insertion via a deletion or vice 
>>> versa. Does anybody else believe that this is a reasonable 
>>> requirement?
>> That's one of the ideas behind transactions: you can do whatever
>> you want, until you terminate your transaction with a COMMIT.

> I really meant that I would like to undo committed changes. Via
> updates in another transaction...

        OK. What I was really trying to say is that once you commit, there's little point in wanting to be able to put things back short of a backup restoration or having the needed information somewhere else.

>> OTOH there is this new idea from Date that transactions are an
>> unnecessary complication because we should just have atomic 
>> statements that make all the transaction's updates in parallel. 
>> This would take COMMIT from us, but than perhaps we should create 
>> something like a branch of work particular to an interactive 
>> session, even perhaps using a transaction-like sintax:

> What do you mean by "branch of work"? A private workspace

        Yes. Obviously it would take lots of resources, not necessarily doable.

>> Now for the specific case of inserting back deleted data. I don't
>> think it is reasonable.  That because if I say "delete all this
>> user's from the key I used to specify the user.  I know it's gone.

> First, you know that it's gone, but you might not know what it was.
> How can you judge about it's value and be allowed to delete it?

        If I'm not knowledgeable enough, the DBA won't give me the privileges.   Simple as that.

        Remember, even when deleting from a base (non-derived) relation, I didn't necessarily saw data before deleting it. I can say just "DELETE relation WHERE key = <something>", and I don't know the non-key attributes' values.

> Now, I feel that users with the right to delete should also be
> allowed to select, just to make sure they know what they are doing.
> Of course, this is just my opinion.

        Obvious. But that they have the rights to read data doesn't mean they will, nor I see how it is relevant.

>> Now if I wanted to have it back, I'd like to have a nice way of
>> restoring deleted data, like using a backup in the way of HSMs: it
>> would be nice to tell a system "give me whatever was in such and
>> such a tuple at such and such a time".  A relational system could
>> be able to fetch that data from backup thru an HSM and give me that
>> data, but that is orthogonal to deleting it.

> I'm not sure what you mean by "whatever was in such and such a
> tuple".

        The tuple itself, meaning the value of the attributes in the tuple identified by the key, or rather by the selection expression (WHERE).

> If we are in the setting of a deletion from a projection
> view then the user does not have access to all attributes of the base
> relation tuples. Hence, the system must not show them to the user
> via backups.

        No, but still could restore. Or this could be done by the DBA.

> Another problem coming up here is that different users may have
> changed the tuple since "such and such a time". The updates of those
> users will be lost when a backup is installed, violating
> serializability. When using view updates instead of backups (which
> is possible in my setting), the effects of those updates will be
> based upon all intermediate changes.

        Good point, really my scheme seems to be bad enough. But that doesn't make yours better, unfortunately…

>>> 3. As a db admin, I expect that users know what they are doing 
>>> when they manipulate data. Does anybody else believe that this is
>>> a reasonable requirement?
>> Well, that's what we have access control and declared constraints
>> for.

> I don't see that. What constraint prevents a user from deleting in a
> projection that preserves the key? (I assume that users access the
> database via an external schema. In particular, users should neither
> know nor care whether they access a view or a base relation.)

        I'm not questioning that, just pointing that people shouldn't be given permissions if they lack cluefulness.

>> And why it should matter?  Suffices them to know to identify which
>> information they deleted; the system (and therefore the DBA) should
>> know where it was deleted from because he knows the constraints
>> derivation rules.  Then the information can be fetched from a
>> backup if necessary.

> First, how many times may I ask the DBA to undo my updates before my
> account disappears? Second, as suggested above, installing backups
> might violate serializability.

        Agreed, but even if this can't be made automatically, still it can be done to a separate database that will be queried to give the necessary information.

        Obviously, if it's more than a few base relations it's a pain.

> My point is that a
> large class of view updates (those without a constant complement
> translator; please see my paper for details) is dangerous and should
> not be allowed.

        I don't agree, sorry… I really think you are mixing access control and view updateability.

/ \ Leandro Guimarães Faria Corsetti Dutra        +41 (21) 216 15 93
\ /        fax +41 (21) 216 19 04
  X      Orange Communications CH
/ \ Campanha fita ASCII, contra correio HTML      +41 (21) 644 23 01
Received on Mon Nov 11 2002 - 10:00:36 CST

Original text of this message