Rocky Smolin - Beach Access Software
bchacc at san.rr.com
Sun Feb 9 12:11:00 CST 2003
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 ----- Original Message ----- From: "John W. Colby" <jcolby at colbyconsulting.com> To: <accessd at databaseadvisors.com> Sent: Saturday, February 08, 2003 1:06 PM Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) > Rocky, > > What I was saying the other day is simply that cascade delete has no ability > to assess the "rights" of the person deleting something. The alternative in > your situation is to build a set of queries that join: > > Rental Agreement to CheckInOut > RentalAgreement to ItemsSold > RentalAgreement to Labor > and finally RentalAgreement with SubRental (subsystem) > > by setting a where clause under the RentalAgreement PK or other defining > field and turning each of these into a delete query, you delete the child > items to RentalAgreement. One by one, you delete the child records > (Checkout, ItemsSold, Labor, and Subrental). Once the child records are > gone, you delete the RentalAgreement. > > Of course cascade delete is free. And of course building these queries is > work. But... now that you have these queries you can use various strategies > to determine who gets to delete the RentalAgreement and it's children, and > equally important, who doesn't. > > I pretty much agree with Arthur, deleting records is mostly inappropriate in > a database. In any event, the ability (right) to do so should rest in the > hands of VERY senior people, not just the person behind the cash register. > > Cascade delete, by it's nature can't differentiate. If it's enabled, my cat > walking across the keyboard could (theoretically) delete a record (or worse > a thousand records in a dozen different child directories). > > When I was working down in Mexico I had an experience that forever changed > my view of the user interface. In Mexico companies often hire what we would > almost call "manservants" - uneducated "lower class" workers to do simple > things like run around town doing things, and of course cleaning. I came > back from lunch one day to find the cleaning lady cleaning the keyboard of > the computer... that happened to be in a record of real data (well, used to > be real anyway). Fields Across the form were filled with > !!!!!!!!@@@@@@@@########$$$$$$%%%%% and > QQQQWWWWWWWWWEEEEEEEEEEEEERRRRRRTTTTTTTTTYYYYYY - whatever keys she had been > diligently rubbing the dirt off of. > > That opened my eyes to the damage that can be done quite unexpectedly in a > database. She had no authorization to edit records, nor delete data. But > she was doing her job, and there she was editing my real user's data. And > she could just as easily have deleted your rental records while she cleaned > the keyboard. > > From that point on, I made a conscious effort to at least evaluate what > power I was giving my users, and make a conscious effort to prevent them > from doing things they were not authorized to do. > > Cascade delete is a tool that has little or no justifiable use in the hands > of the end user. There are simply too many scenarios where people that have > no business or understanding of the consequences, end up with the ability to > delete stuff. Like your example, most of us use(d) it because we didn't > think about it. > > There are ways around enabling it. Queries can be built that will delete > the data. Objects can be set up that allow qualified users (supervisors) to > delete things when necessary. And of course that is a lot of work. > > Cascade delete is "free". It's only cost is disaster in the wrong hands. > > 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: Saturday, February 08, 2003 11:07 AM > To: accessd at databaseadvisors.com > Subject: Re: [AccessD] Cascade-delete (was: Estimating Help) > > > I come a bit late to this thread (but that's not new for me). I've tried to > follow it but it ain't always been easy. I am using cascade delete in an > app for a client who rents audio visual equipment and am wondering if this > is good design runs afoul of anybody's catechism on the subject: > > The Rental Agreement header has one to many relationship with several table > : > Equipment to be rented (with one-to-one with a CheckIn/CheckOut table) > Items sold at retail > Labor > Sub-Rental Header (with its own detail records > > might be more - I forget. So in order to delete a rental agreement (which > they want to do from time to time) either they have to go in and delete all > the detail records first, or I give them cascade delete. They opted for > cascade delete. > > I do give them a very clear warning message about what's going to be deleted > and have them confirm. > > What say you all to this? > > Rocky Smolin > Beach Access Software > > P.S. I though everybody was used to Colby's rhetorical style by this time. > He's been quite - well relatively - civil on this subject. Try him on the > phrases 'unbound form' and 'natural key'. From a distance, of course. :) > > ----- Original Message ----- > From: "John W. Colby" <jcolby at colbyconsulting.com> > To: <accessd at databaseadvisors.com> > Sent: Wednesday, February 05, 2003 10:39 AM > Subject: RE: [AccessD] Cascade-delete (was: Estimating Help) > > > > Aww grow up guys. > > > > I never said never. I stated very plainly that if everyone has the right > to > > delete the records then it doesn't matter. John then states (finally, in > > the last email) that this is the case. So it doesn't matter (in this > case). > > > > So where exactly is the beef? > > > > I don't give a rat's patuty if you turn on cascade delete for every table, > > every time, in every database. To search around struggling to find > exactly > > the instance where it is useful is a waste of everyone's time. If it > works > > for you, and you don't get fired when records disappear who really cares. > > In any event, you can always blame the user after all. "Hey, I warned > > them". > > > > In any case, I certainly don't care, it isn't my database, nor my job on > the > > line. And I am not getting my users fired for not doing my job correctly. > > > > Sorry if that was "derisive" but really, look at what I said. I was very > > very VERY clear in my statements. And I see no reason to modify any of > > them. > > > > John W. Colby > > Colby Consulting > > www.ColbyConsulting.com > > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > > > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com