[AccessD] Cascade-delete (was: Estimating Help)

Rocky,

Think of cascade delete off in the same way as setting up validation rules
in the table in the back end.  A validation rule may say, "Age cannot be
less than zero".  It doesn't matter how the user tries, they simply can't
enter an age less than zero.  Enforced by Jet, for all users, all of the
time!

Cascade delete off is a validation rule.  "Do not allow deletes if the table
has children".  I don't care who you are, I don't care how you try, I am not
going to allow you to delete the parent if it has children.  Enforced by
Jet, for all users, all of the time, from any method of access!

Your method works fine, as long as they go in through your interface.  If
they go around your interface, then they again gain the right to delete,
since "anyone can delete".

All you are doing by getting rid of cascade delete and doing it with queries
is raising the bar on the ability to delete.  If I can get at your database,
and cascade delete is on, I can delete potentially thousands of records
simply by clicking on a single record and hitting delete.  I can do that by
opening the BE directly, by linking in the tables to a new db, by creating a
vb interface and going at them using vb (or 'C', or anything else).  I can
do it by getting at the database window in the FE.  I can do it if I am
allowed to build queries in the FE.  Etc. Etc.

Turn off cascade delete and ALL of those avenues are cut off.  Just like
that, with the stroke of a pen.

Now I have to know enough to delete children, then delete the parent.  I
have to know enough to build a query that pulls exactly the children for the
parent I want to delete.  I have to know all of the child tables that belong
to that parent.  Etc. Etc.

Look at what cascade delete does and you see why turning it off is so
important.  In a real database of mine, an insurance policy is related to N
claims.  With cascade delete turned on, if anyone manages to tell Jet to
delete a policy, all of the related claims get deleted.  Claims have contact
information, payments, expenses, attending physicians, and about 5 other
child tables.  Deleting "one record" (the policy) could also delete dozens
of claims, and literally thousands of contacts, payments, expenses etc.

That is disastrous!!!  It doesn't matter if they weren't supposed to, it
doesn't matter if they were supposed to know better, it doesn't matter if
they were supposed to lock up their computer and throw away the key when
they went home.  What matters is that if I turn on cascade delete, I AM
RESPONSIBLE for that data being deleted.  I knew the damage that could be
done, and I turned it on anyway.  The poor user who actually does the delete
and clicks yes without even reading the warning from Jet about deleting
related records is NOT responsible for the damage.  I am!

ONLY security will absolutely prevent some individual from doing the damage
if they are determined to do it, but cascade delete just hands the world the
key to deletes.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Rocky Smolin -
Beach Access Software
Sent: Sunday, February 09, 2003 11:07 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Cascade-delete (was: Estimating Help)


John:

I agree about the need for restricting the right to delete.  My standard
approach to this, which I copy from one app to another, is to have the user
sign in with a password.  My standard access rights are 1) read only, 2)
data entry, 3) admin.  These levels can be easily modified or expanded to
fit a particular user's requirements.

I even have one user who has the access controlled by field and function
within form, so that I had to design a (beautifully color coded) grid of
access rights.  Which of course, requires admin level to access.

So I can easily restrict the delete key to a user with only admin rights.
Once restricted however, I don't see the advantage of the multiple delete
query approach vs. the cascade delete.  If a user has the right to delete,
does the method of deletion make a difference?

Rocky