DMcAfee at haascnc.com
Wed Jun 30 20:05:17 CDT 2004
Amen, brother! I couldn't have said that better myself. :) David Arthur Fuller wrote: As usual, Shamil, you make excellenmt points. We are not arguing so much as complementing each other's position. IMO, (and that's all it is), everything the database can do the database should do. In my ideal SQL app, there would be almost no code in the FE except the code that decides which sproc to fire and what to pass as arguments. The more code that exists in the FE, the weaker I think the app is. Why? Because I don't want some "power user" to load Excel and f**k with the data! So when I build a SQL database, NO ONE (except me and my peers) can touch a table. Everyone else using the system hits a sproc or view or UDF. End of story. I, as godlike being, can touch tables. Mere mortals cannot. Period. No exceptions. In practice, this means that I examine the human roles, i.e. job specs, to see what the various humans in the organization are allowed to do. I build roles corresponding to job specs. I generate simple sprocs for each op (DISU = delete/insert/select/update) on each table. Could be that a single form hits a number of tables, but the hits always result in some combination of DISU -- there are no other ops. Having built the single-table DISU ops, it becomes trivial to write a sproc that hits say 5 tables, inserting into 2, updating 1, and deleting from 2. Nothing to it, brain-dead simple once you have the atomic pieces in place. Ideally, I don't want any code that touches the database in the FE. Why? Because if we dump FE#1 (let's call it Access) and replace it with FE#2 (let's call it vb.net), I don't want to rewrite any database-hitting code. I want to fire sprocs and/or call UDFS and be done with it. So if some "power user" fires up Excel and creates an ODBC hook to the database, he can't do anything but select (at most). Can't add, can't edit, can't insert, can't delete. Substitute POwerBuilder or Delphi or anything you want... If the only available access is via sprocs/views/udfs, then I'm confident that you can't trudge in and stomp all over my data. Every new UI inherits the smarts in the sprocs, and nothing needs to be duplicated except the calls to the sprocs/views/udfs. IMO, this way no code needs to be copied from one UI to another; you simply fire the sprocs, passing what they require. If there's a bug in the sproc you fix the sproc and all apps inherit the fix -- because the logic resides in precisely one place -- the db, where it belongs! Ok, I stated this a little bit fundamentalist. There are exceptions, and I accept them.
More information about the dba-SQLServer mailing list