Re: Where to do Data Validation?

On Tue, 20 Nov 2007 13:33:52 -0800 (PST), "fitzjarrell_at_cox.net" <fitzjarrell_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.

>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.

>
>> 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.

>
>> 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.

>
>> 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.

> 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.

>>
>> >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.
>
>> 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.

B.

>
>> B.
>>
>>
>>
>>
>>
>> >Reconsider.- Hide quoted text -
>>
>> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>
>David Fitzjarrell
Received on Tue Nov 20 2007 - 15:59:10 CST