Re: Where to do Data Validation?

On Nov 20, 3:59 pm, Brian Tkatch <N/A> wrote:
> On Tue, 20 Nov 2007 13:33:52 -0800 (PST), "fitzjarr..._at_cox.net"
>
> <fitzjarr..._at_cox.net> wrote:
>
> <SNIP>
>
> >> >In no case can a validation in a front-end or tool protect data in a
> >> >database from corruption ... EVER ... unless the quality of the data
> >> >is guaranteed by the database itself.
>
> >> Although, i would promote that statement as encouragement, it is not
> >> correct. In a controlled environment, the UI can be made to be the
> >> only path of data entry, as such, it can do the data integrity
> >> checking.
>
> >Define 'controlled environment'.
>
> One implementation i have seen (in many groups in the same
> organization) is one username which the program uses to connect, but
> is not given to the users.

Yet, through sqlplus and a local server connection one doesn't need the 'application only' account to access and possibly disrupt application data. Sort of circumvents your 'control', doesn't it?

>
> >Now, implement that and still give users access. Then try to restrict what they use to access the
> >database. It's surprisingly easy to defeat such tactics as logon
> >triggers can easily be fooled by simply renaming the undesirable
> >application. sqlplus.exe can be renamed to anything one would want to
> >call it and, thus, can be used to directly access the data
> >circumventing exclusive use of your UI. And, as a result your data is
> >vulnerable to attack. So much for your 'controlled environment'.
>
> I'd like to see you do that.
>
>

Search google or this newsgroup for examples. They are widely available.

>
> >> In many cases, and sadly most cases i have seen, the DB is merely a
> >> convenient place to store data, and basically all checking was done in
> >> the UI code.
>
> >And that's just peachy until you have simultaneous inserts into a
> >table and attempt to enforce a pseudo primary key or pseudo foreign
> >key constraint through the UI. And end up with duplicate key values
> >or dependent inserts failing because the newly inserted parent record
> >hasn't yet been committed.
>
> >> And that was what the programmers wanted.
>
> >And programmers run the show where you are? I can now understand why
> >data validation is so low in priority on your list.
>
> They do in most cases. The data modelers rarely understand what the
> program is doing, and the DBAs usually don't care.
>

Which explains much in why your 'world' is so ... off-kilter.

>
>
> >> Mostly because
> >> it was easy to understand, and could be changed without a change
> >> request to the DBAs which is usually more pain than it is worth.
>
> >Ensuring data integrity is more of a pain than having a continuingly
> >running application? This sounds more like a paint and body shop than
> >an IT shop.
>
> Actually, it was a large IT operation. When there are tens of
> thousands of workers, groups separate, and people stop caring.
>

Prove that unequivocably. Where I work people care. And it isn't a local 7-Eleven, either.

>
>
> >> To
> >> that end, COLUMNs are added and reused, and rarely DROPped, just to
> >> not have to bother with the DBA group.
>
> >Interesting that in the quest for 'efficiency' you place data in
> >misnamed columns simply to circumvent proper change control and a
> >logical table design/implementation.
>
> I never did that. Indeed, i was called in for cleanup.
>

Were you really? Given the mess you've presented I'd have never guessed.

> > And, possibly, the DBA group is tired of you and your antics.
>
> Nope.
>
> > Is there no development or test
> >environment configured to facilitate such changes?
>
> Even test is a restricted environment. Someting i complained about on
> many an occasion.
>

'Restricted'? Meaning the DBAs actually care about what goes into and comes out of that database? Wow, what concepts -- data integrity and proper change control.

>
>
>
>
>
>
> >> >Putting validation into the application will never, EVER, keep someone
> >> >with SQL*Plus from destroying it.
>
> >> Unless it is a controlled environment.
>
> >Define that and implement it in a foolproof (and user-proof) manner.
> >Then see how much work you actually can get done.
>

I notice you failed to answer that challenge.

> >> While i understand your sentiment--indeed, they are my feelings as
> >> well--it just is not that way in the "real world" when the programmer
> >> considers the DB just a convenient place to store data.
>
> >If the inmates are running the asylum you have more to worry about
> >than data integrity.
>
> Be veeeery afraid. They run more than you are willing to admit.
>

And yet again you 'speak' without thought. Is cognitive action so foreign to you?

> B.
>
>
>
>
>
> >> B.
>
> >> >Reconsider.- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> >> - Show quoted text -
>
> >David Fitzjarrell- Hide quoted text -
>
> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

David Fitzjarrell Received on Tue Nov 20 2007 - 16:14:24 CST